I'm trying to replace some long running Query/400 jobs with RPG and embedded SQL. Some of the existing jobs run for almost 24 hours on our machine. I've already successfully managed to reduce a few of these jobs to a matter of minutes using embedded SQL. So far, so good.
My problem is when these jobs join very large numbers of records - we have files with over 200 million records. I've tried to replace these jobs with embedded SQL, but I don't really get the performance improvement I was hoping for. Has anybody got any tips for speeding these jobs up?
Here's an example of one of the embedded SQL statements :-
SUBSTR(DIGITS(DATE_FIELD),4,2) AS MONTH,
FILE02_C, FILE01_D, FILE01_E, FILE01_F
FROM FILE01, FILE02
WHERE FILE01_KEY01 = FILE02_KEY01
AND FILE01_KEY02 = FILE02_KEY02
AND (DATE_FIELD >= 1080101
AND CHECK_CODE <> 'AB'')