Re: What would be your SQL solution? -- RPG400-L

first , try to use interactive SQL for the select :

STRSQL
F13 to change sql option : use a new file
Select your fields from yourfile where filedate in (firstdate , lastdate)
Take the time it make.

Second :
Why do you use a work file ? IO are time consumer so perhaps you can avoid
this.

Third :
I can send you a template of embedded sql if you need.

----- Original Message ----- 
From: <fkany@xxxxxxxxxxxxxxxxxx>
To: "CN=RPG programming on the AS400/O=iSeries" <rpg400-l@xxxxxxxxxxxx>
Sent: Thursday, April 07, 2005 4:16 PM
Subject: What would be your SQL solution?


>
> 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
>      *
>      *
>      *
>      C     *INZSR        BEGSR
>      ?*
>      ?* Incoming Parameter List
>      C     *ENTRY        PLIST
>      C                   PARM                    FROMDATE
>      C                   PARM                    TODATE
>      ?*
>      C                   ENDSR
>      *
>      *
>      *
>      P MAIN_PRCS       B
>      ?*
>      '*
>
>
>
> -- 
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
>
>