RE: What would be your SQL solution?

["Wilt, Charles" <CWilt@xxxxxxxxxxxx>]

Frank,

SQL is fast when it has indexes/logical files it can use.

If you don't already have a logical file keyed by FHDDAT, you're going to need 
one.

Your current RPG program should already be using such a logical, but it sounds 
like it is not; if it was there'd be no need for BFILE.

With such a simple selection/sort criteria, the only advantages SQL (with the 
proper indexes) would have over RPG (with the right logical) would be:
-Multi-row fetch (proper blocking could help the RPG program)
-Retrieving only the fields needed (though RPG accessing a logical whose format 
had just the needed fields would see this benefit).

I guess what I'm trying to say is that as Joe Pluta likes to point out, SQL 
isn't magic.  Given the simplicity and static nature of your needs here, SQL 
isn't going to give you much if anything over RPG native IO.

Here's what I see to be your problem:
1) Extra I/O needed to write to and read an unnecessary work file (BFILE)
2) Apparently don't have a logical keyed by date.

Here's a quick fix:
Create a logical file named BFILE keyed by date over AFILE.  Optionally, give 
BFILE logical a new format containing just the FHOT, FHPRO,  FHDT, FHDDAT, 
FHDTIM, FHDADT, FHPUDT, FHSCD, FHCCD, FHCCT, & FHCZIP fields.

Now change RPGPGM2 to accept the FROMDATE and TODATE parms.  Use the FROMDATE 
in a SETLL on BFILE before beginning its read.  Then change it's read loop so 
that it exits once FHDDAT is greater than TODATE parm.

With these changes, you don't need to run RPGPGM1 at all, just run RPGPGM2.

See how that performs, if not acceptable, it should be considerably better and 
you'll have a good baseline for more improvements.  Either with RPG Native I/O 
or SQL.


HTH,

Charles Wilt
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 

> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of
> fkany@xxxxxxxxxxxxxxxxxx
> Sent: Thursday, April 07, 2005 10:16 AM
> To: CN=RPG programming on the AS400/O=iSeries
> 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.
> 
>