Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Location
    Rhodesia
    Posts
    28

    Unanswered: Massive ID/data generation

    :: 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

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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/
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    BTW, you will find the link for the cookbook in the 'Useful DB2 Stuff' thread

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •