RE: What would be your SQL solution?

There's no reason to build a temporary table.

All he's going to do is waste I/O.

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 Ross Hartford
> Sent: Thursday, April 07, 2005 10:46 AM
> To: 'RPG programming on the AS400 / iSeries'
> Subject: RE: What would be your SQL solution?
> 
> 
> Mine takes one SQL statement and creates a temporary table in 
> QTEMP with the
> selected records:
> 
> declare global temporary table temp1 
> as (                   
> select x1,x2,n1,n2 from ross/xxxx where n1 > 0 and n2 > 0)  
> 
> with data                                                   
> 
> I think you may have to build this as a dynamic SQL statement 
> and execute
> it, versus doing the where clause with host variables.  Just 
> replace the
> x1,x2, etc with your list of fields and give it a shot in 
> STRSQL or your
> favorite SQL test tool.
> 
> Ross Hartford
> Cothern Computer Systems, Inc.
> 1640 Lelia Drive, Suite 200
> Jackson, MS  39216
> 
> Phone:     601-718-0803
> Fax:       601-969-1184
> Support:   888-222-5577
> 
> email:     rossh@xxxxxxxxxxx
> website:   www.ccslink.com
>  
> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx 
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
> On Behalf Of fkany@xxxxxxxxxxxxxxxxxx
> Sent: Thursday, April 07, 2005 9: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.
> 
> 
> 
> -- 
> 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.
> 
>