Re: What would be your SQL solution?

>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.

Users don't have access to SQL, so I think the solution would still have to
still be embedded SQL inside the RPGIV.  All the user is going to do is
enter a date range and press enter.

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

The work file only takes a few seconds to run, hardly time consuming IMHO.
It's the gathering of the data that's taking so long.

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

Please do so.  I open to all suggestions.

Thanks,

Frank








"Richard ECUYER" <recuyer@xxxxxxx>@midrange.com on 04/07/2005 09:29:58 AM

Please respond to RPG programming on the AS400 / iSeries
       <rpg400-l@xxxxxxxxxxxx>

Sent by:    rpg400-l-bounces@xxxxxxxxxxxx


To:    "RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx>
cc:

Subject:    Re: What would be your SQL solution?


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
>      ?*>      '*>