:: DB2 UDB WAREHOUSE v8.1, AIX 5.2::

I am trying to generate test data into a relational database.

Here is the table structures of the 3 eg tables:

Table A, TBL_A_ID,TBL_A_DSC
Table B, TBL_B_ID ,TBL_B_CODE, TBL_A_ID
Table C, TBL_C_ID,TBL_C_VAL,TBL_B_ID

What Im looking to do is generate 10 records for Table a, 100 records in Table B (For each TBL_A_ID generate 10 tbl_b_id) and about 1000 rows in Table c etc

My pseudocode is somewhat like this:

Create procedure SP1(IN MAXIMUM INTEGER)

DECLARE ctr_a_id INTEGER 1;
DECLARE ctr_b_id INTEGER 1;
DECLARE ctr_c_id INTEGER 1;

WHILE ctr_a_id <> MAXIMUM DO
INSERT INTO TBLA (TBL_A_ID) VALUES (ctr_a_id);
INSERT INTO TBLB (TBL_B_ID,TBL_A_ID) VALUES (ctr_b_id,ctr_a_id);
INSERT INTO TBLC (TBL_C_ID,TBL_B_ID) VALUES (ctr_c_id,ctr_b_id);

SET ctr_a_id = ctr_a_id +1;
SET ctr_b_id = ctr_b_id +1;
SET ctr_c_id = ctr_c_id +1;
loop;

end


The problem with this code is it generates a 1:1 mapping for TBL_A_ID to TBL_A_ID in the Child Tbl B and same way to TBL C.
i.e for tbl_a_id =1 there is only 1 tbl_b_id etc.
I am looking for a nested while statement or something similar which will allow me to create 10:100:1000 for tbles A B & C resp , is this possible in DB2?
Any thoughts methods?

Thanks mac

Download Graeme Birchall's SQL Cookbook ... There are a number of examples for generating test data ...

Or

if you can spend $$$ , you may consider using a tool like test database generator ..

http://www-306.ibm.com/software/data...tools/db2tdbg/

BTW, you will find the link for the cookbook in the 'Useful DB2 Stuff' thread

Cheers
Sathyaram