What would be your SQL solution?

[fkany@xxxxxxxxxxxxxxxxxx]

I've been trying to figure out an embedded SQL solution for my RPGIV READ
loop for over a week now.  I feel like I'm "spinning my wheels".  I'm going
to post the details of my project.  If anyone would like to share how they
would use SQL to complete the task, please do so.

The program currently takes an hour or more to process.  I don't want to
create anymore logicals over the file it currently uses because the file
just seems to have too many logicals(close to 100).  Even with all the
logicals the file currently has, our users are still accustomed to waiting
a day or more for a large report to print.  I'd like to help speed things
up.  Nobody in our shop, including me, is aware of how fast SQL really is
at getting large amounts of data at 1 time.

Thanks,

Frank

System Information:           V5R2
Programming language:   RPGIV

(Part 1) - This part takes an hour or more to process using an RPGIV READ
loop with a typical date range of 1 month(20040501 to 20040531)
Program(RPGIVPGM1) is called with parameters(FROMDATE & TODATE):
CALL RPGIVPGM1(FROMDATE TODATE)

RPGIVPGM1 will populate BFILE with all records from AFILE within the date
range specified(FROMDATE & TODATE).  The only fields needed from AFILE to
populate BFILE are: FHOT, FHPRO,  FHDT, FHDDAT, FHDTIM, FHDADT, FHPUDT,
FHSCD, FHCCD, FHCCT, & FHCZIP.

BFILE is keyed only on FHDDAT, which is what date the FROMDATE & TODATE
should be referencing.

(Part 2) - After BFILE is built from Part 1, this section only takes a few
seconds to run.
RPGIVPGM2 will access BFILE by FHDDAT and print a report.

AFILE has 7 million records.  BFILE, if populated with 1 month of
data(20040501 to 20040531) will have 400,000 records.

RPGIV shell, please make any necessary changes:
     H DFTACTGRP(*NO)
     '*     '* ----------
     '* Files Used
     '* ----------
     '*     '* ----------------------
     '* Program Defined Fields
     '* ----------------------
     D FROMDATE        S              8S 0
     D TODATE          S              8S 0
     '*
     '* ----------------------
     '* Arrays/Data Structures
     '* ----------------------
     '*     '* -----------
     '* Prototypes:
     '* -----------
     ?* Main Processing
     D MAIN_PRCS       PR
     **********************************************************************
     '*     '* ---------------------
     '* Start Main Processing
     '* ---------------------
     C                   CALLP     MAIN_PRCS
     '*     '* -------------------
     '* End Main Processing
     '* -------------------
     ?* End Program, Return to Caller
     C                   EVAL      *INLR = *ON
     C                   RETURN
     *     
**********************************************************************
     *     * *INZSR - Initialization Subroutine
     *     
**********************************************************************
     C     *INZSR        BEGSR
     ?*
     ?* Incoming Parameter List
     C     *ENTRY        PLIST
     C                   PARM                    FROMDATE
     C                   PARM                    TODATE
     ?*
     C                   ENDSR
     *     
**********************************************************************
     *     * MAIN_PRCS - Main Processing Subprocedure
     *     
**********************************************************************
     P MAIN_PRCS       B
     ?*
     '*     P MAIN_PRCS       E