Results 1 to 2 of 2

Thread: Db2 Cursor

  1. #1
    Join Date
    Mar 2009

    Post Unanswered: Db2 Cursor


    Please provide me best solution for the following requirement.

    1. I have the following four tables called TABLE-A,B,C & D and the row counts on each table as follows.

    Table-A has got 300 Million rows.
    Table-B has got 1 Billion rows.
    Table-C has got 3 Billion rows.
    Table-D has got 3 Billion rows.

    The table A is parent table for B and Grand Parent for C. B is a Parent table for C. Table-D is child table for A.

    The Parent to Child relationship is One-Many on the above tables.

    Here is the requirement....

    I need to extract data from Table-A based on the Time-range and I need to fetch data from the child tables B, C (by join)for the rows that i extracted from Table-A.

    Extract #2:
    Also, I need to fetch from the another table called D.

    Finally, I need to load the Extract # 1 & #2 into two different tables.

    Please note that my appliction running on Mainframes.

    I have got two solutions in my mind for the above requirement ...

    1. Do a DB2 Unload on Table-A for the given TIME range and create a flat file.
    2 Feed the above flat file to a COBOL program and open a cursor for each read agaist the table B & C(Cursor has got JOIN query on B ,C), and fetch required data for all the Keys in Flat file.
    3. In the same program, Open another cursor against Table-D and extract required data into a Flat file.
    4. Finally Load the above 2 flat files into 2 different tables.


    1. Do a Db2 Unload on Table-A for the given time range and load this file into another table called A1.
    2. Do Db2 Unload by joining A1,B & C and extract required data into a file.
    3. Do Db2 Unload by joining A1 & D and extract data into a file.
    4. Finally load above 2 files into 2 diff tables.

    I believe that CURSORs are big performance hitters compare to normal Joins.

    Can you guys advise me which is best approach among the above 2 solutions wrt to Performance.

    Also, Provide if any other better solutions other than above specified one.


  2. #2
    Join Date
    Jan 2003
    Provided Answers: 5
    Cursors are a construct for returning a result set of a query. The query can and often contains joins.

    The best construct for performance is an index.


Posting Permissions

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