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