|
midrange.com
sometimes it helps to compare for yourself........
"Wilt, Charles" <CWilt@xxxxxxxxxxxx>@midrange.com on 04/07/2005 03:12:11 PM
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?
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.
>
>
--
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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2025 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.
