Results 1 to 8 of 8

Thread: cursor loop

  1. #1
    Join Date
    Mar 2004
    Posts
    16

    Unanswered: cursor loop

    Hi,
    I'm writing a DB2 stored proc (DB2 UDB7) and I'd like to get some advice on the most efficient way to do this:

    Table A has several million contacts names and their phone numbers. Table B has one column with several thousand phone numbers. Table C needs to get populated with the same data as Table A. However, not all records from A will get inserted into C--only those records whose phone number exists in the phone number cross reference table B.

    I was planning to use a cursor and loop to look at records from A one at a time and see if their phone num. exists anywhere in B. If so then the proc should write this record into C. Can anyone give me more specifics on how to do this?

    Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: cursor loop

    Originally posted by tylerr
    Hi,
    I'm writing a DB2 stored proc (DB2 UDB7) and I'd like to get some advice on the most efficient way to do this:

    Table A has several million contacts names and their phone numbers. Table B has one column with several thousand phone numbers. Table C needs to get populated with the same data as Table A. However, not all records from A will get inserted into C--only those records whose phone number exists in the phone number cross reference table B.

    I was planning to use a cursor and loop to look at records from A one at a time and see if their phone num. exists anywhere in B. If so then the proc should write this record into C. Can anyone give me more specifics on how to do this?

    Thanks
    insert into table_c select * from table_a where phone_number in (select phone_number from table_b)

  3. #3
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Transaction log full!

    If you try the above SQL and get a log full, use EXPORT-IMPORT with a low commitcount.
    --
    Jonathan Petruk
    DB2 Database Consultant

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Originally posted by J Petruk
    Transaction log full!

    If you try the above SQL and get a log full, use EXPORT-IMPORT with a low commitcount.
    If you can re-create table_c then there's another way to avoid log overflow:

    CREATE TABLE TABLE_C ... NOT LOGGED INITIALLY;
    COMMIT;
    UPDATE COMMAND OPTIONS USING C OFF;
    ALTER TABLE TABLE_C ACTIVATE NOT LOGGED INITIALLY;
    INSERT ....;
    COMMIT;

  5. #5
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Originally posted by n_i
    If you can re-create table_c then there's another way to avoid log overflow:

    CREATE TABLE TABLE_C ... NOT LOGGED INITIALLY;
    COMMIT;
    UPDATE COMMAND OPTIONS USING C OFF;
    ALTER TABLE TABLE_C ACTIVATE NOT LOGGED INITIALLY;
    INSERT ....;
    COMMIT;
    True, although don't forget to perform a backup immediately after if you do it this way.

    I think we've bombarded this person with too many new concepts...
    --
    Jonathan Petruk
    DB2 Database Consultant

  6. #6
    Join Date
    Mar 2004
    Posts
    16

    def not use loop/cursor?

    Thanks for the help guys...I was told that it would be more efficient to use a loop/cursor than to just do WHERE....IN.... otherwise it would be too slow... Can you elaborate any more on this?
    Thanks a lot

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: def not use loop/cursor?

    Originally posted by tylerr
    Thanks for the help guys...I was told that it would be more efficient to use a loop/cursor than to just do WHERE....IN.... otherwise it would be too slow... Can you elaborate any more on this?
    Thanks a lot
    My guess would be that a single statement version should be a bit faster... if only for this reason: cursor/loop would have to cycle through all records in table_a and execute exactly one select against table_b for each of them, whereas with the single INSERT ... SELECT ... the SELECT part would determine all records than need to be inserted in a single join operation.

  8. #8
    Join Date
    Mar 2004
    Posts
    16

    thanks

    thanks, good explanation
    t

Posting Permissions

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