× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.