EXPLAIN Facility and Predictive Analysis › Predicting Database Performance › Methods to Generate Data

Methods to Generate Data

To simulate program access, you need data in tables. You can simply type some data into INSERT statements, insert them into a table, and then use data from that table to generate more data. For example, you might have to test the various program processes against a PERSON_TABLE table and a PERSON_ORDER table. No actual data has been created yet, but you have to test the access patterns of incoming files. You can key some INSERT statements for the parent table, and then use the parent table to propagate data to the child table. For example, if the parent table, PERSON_TABLE, contains this data:

PERSON_ID    NAME
        1    JOHN SMITH
        2    BOB RADY

The following statement can be used to populate the child table, PERSON_ORDER, with some test data:

INSERT INTO PERSON_ORDER
(PERSON_ID, ORDER_NUM, PRDT_CODE, QTY, PRICE)
SELECT PERSON_ID, 1, 'B100', 10, 14.95
FROM    YLA.PERSON_TABLE
UNION ALL
SELECT PERSON_ID, 2, 'B120', 3, 1.95
FROM    YLA.PERSON_TABLE

The resulting PERSON_ORDER data would look like this:

PERSON_ID     ORDER_NUM    PRDT_CDE    QTY    PRICE
        1             1        B100     10    14.95
        1             2        B120      3     1.95
        2             1        B100     10    14.95
        2             2        B120      3     1.95

You can repeatedly use the statements to add more data, or additional statements can be executed against the PERSON_TABLE to generate more PERSON_TABLE data.

Recursive SQL (DB2 V8 and DB2 9) is a useful way to generate test data. The following is a simple recursive SQL statement:

WITH TEMP(N) AS
     (SELECT 1
       FROM SYSIBM.SYSDUMMY1
      UNION ALL
      SELECT N+1
      FROM TEMP
      WHERE N < 10)
 SELECT N
 FROM TEMP

This statement generates the numbers 1 through 10, one row each. You can use the power of recursive SQL to generate mass quantities of data that can be inserted into DB2 tables, ready for testing.

Example

The following piece of an SQL statement was used to insert 300,000 rows of data into a large test lookup table. The table was quickly populated with data, and a test was conducted to determine the performance. It was determined that the performance of this large lookup table would not be adequate, but that could not have been confirmed without testing:

WITH LASTPOS (KEYVAL) AS
   (VALUES (0)
    UNION ALL
    SELECT KEYVAL + 1
    FROM   LASTPOS
    WHERE  KEYVAL < 9)
,STALETBL (STALE_IND) AS
   (VALUES 'S', 'F')
SELECT STALE_IND, KEYVAL
 ,CASE STALE_IND WHEN 'S' THEN
   CASE KEYVAL WHEN 0 THEN 1
    WHEN 1 THEN 2 WHEN 2 THEN 3
    WHEN 3 THEN 4 WHEN 4 THEN 4
    WHEN 5 THEN 6 WHEN 6 THEN 7
    WHEN 7 THEN 8 WHEN 8 THEN 9
    WHEN 9 THEN 10 END
                  WHEN 'F' THEN 
   CASE KEYVAL WHEN 0 THEN 11
    WHEN 1 THEN 12 WHEN 2 THEN 13
    WHEN 3 THEN 14 WHEN 4 THEN 15
    WHEN 5 THEN 16 WHEN 6 THEN 17
    WHEN 7 THEN 18 WHEN 8 THEN 19
    WHEN 9 THEN 20 END
       END AS PART_NUM
FROM  LASTPOS INNER JOIN
      STALETBL ON 1=1;