Stored procedure Or RPG or...

[tdaly@sddsystems.com]

> File A has millions of records and File B (dependent on A) has many more related records. Based on cut-off date, I need to extract data from A and related data from B and write to a workfile.

> Can someone suggest me what is the best way to achieve the same

I would suggest that you recommend to your boss that this project be outsourced. Personally, I'd have a CL doing a OPNQRYF on FILE A to select on the date range, and call an RPG with a input primary on FILE A with a SETLL/DOW/READ loop on FILEB. But that's just me. Depends on the details of the situation. Plus old habits die hard. Good Luck.

[asheesh_ks@infosys.com]

Hi All, I nee a help in following situation: File A has millions of records and File B (dependent on A) has many more related records. Based on cut-off date, I need to extract data from A and related data from B and write to a workfile. Can someone suggest me what is the best way to achieve the same— 1. Create join logical on A and B and process in RPG, Or 2. Use stored proc (which one SQL or RPG) Or 3. Use SQLRPGLE Or 4. Use OPNQRYF Or 5. …… Thanks, Asheesh

[dacust]

Your choices are not "or"s. A stored procedure is not another method, but a way you could implement one of the other methods. It depends on several factors. Will this be run often? Do the selection criteria change each time? Is it run often? An OPNQRYF is just another way to run an SQL, so below, when I say SQL, that could mean SQL, SQLRPG, OPNQRYF, etc. The SQL solution will cause an index to be built every time. If it is run once a month, this may not be a problem. This can be tuned and sped up dramatically by building the right permanent indexes. The LF solution will make it run faster, but will use disk space and impact performance on the rest of your system (not much with only 5 million records, but how many in the detail file?) It's really a complex question. Many a series of articles have been written on just this subject. Actually, many classes and articles have been written just on the subject of how to create indexes to speed up SQL. I've been addressing this type of situation for years now, and still have to evaluate each one carefully when it starts involving millions of records. Sometimes I run benchmarks to make sure my theorizing gets the results I think it should. You can keep giving us more information in here and we'll try to help, but your best bet is someone on-site with the years experience needed to know what to look for and what to try. Other than that, just experiment. BTW, my pet solution is usually dynamic SQLRPG or SQL with combinations of LFs and EVIs for performance. I like keeping the SQL with the program rather than separate in an OPNQRYF.