Hi

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

Extract#1:
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 ...

Solution#1:
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.

Solution#2:

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.

Thanks
Kishore.Y

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.

Andy