SQL rewrite

Contrary to some listers opinions that SQL should only be used for
set-at-a-time logic, and that the existence of a cursor is proof that
you're doing something wrong, I'd like to relate a little story.

We have a program that we've been running for double digit years. Original
programmer has moved into operations many years ago. One modifier quit to
become a stay at home mom back in 97, (which was a good decision for her
as the Lord took her less than a year ago at the tender age of 44 due to
heart failure - which, looking at her, you'd have never suspected).

After installing Mimix I noticed that file audits were failing because of
transaction thresholds. I discovered that this program was running for 35
minutes and generating over 3M+ I/O's to the file it regen'd every
morning. The resultant file only has around 63k+ records.
I took this logic and changed it over from SETLL/READ on the primary keys
of a header and detail file to a SQL cursor over the fields used in the
resultant file. And, instead of writing/updating on every read of the
detail file, I only updated the resultant file on a control break.

New program dropped the run time from 35 minutes to 1 minute and the
number of I/O's on the resultant file from 3M+ down to less than 64k.

Could I have done this with RLA? Sure, but I would have had to create new
logical files over the header, and the detail to get it in the right
order. And, y'all know that IBM will NOT let you create a join LF with
sort fields from both the header and the detail file. Something that was
simply done with a SQL select statement.

This is on a 570-7748 lpar with 83 disk arms.

And I ran hash totals on every column, string or numeric, and everything
matched except for the total on the column affected by the foreign
currency calculation that was wrong all these years.

Rob Berendt