|
In the example below, looping until SQLCOD=100 can cause an endless loop if there's an error reading from the file (admittedly not likely locally but very possibly in a distributed environment). A better way is to look at the first two characters of SQLSTT and see if they are either '00' (no error) or '01' (warning -- this happens, for example, when data is converted during a fetch or a single SELECT returns more than one row). SQLSTT '02000' is end of data but looping on just that causes the same problem. I typically use a data structure like this: DSQLState DS D SQLClass 1 2A D SQLStateCode 3 5A and when I execute and SQL statement, I set SQLState equal to SQLSTT. In read loops, I typically code DoW (SQLClass='00' or SQLClass = '01'). Also, it's much better to use SQLSTT than SQLCOD because SQLCOD's can and have changed in the past. SQLSTT, on the other hand, follows a cross-database standard that is very stable. Matt -----Original Message----- From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Holden Tommy Sent: Thursday, April 07, 2005 12:03 PM To: 'RPG programming on the AS400 / iSeries' Subject: RE: What would be your SQL solution? c/exec sql c+ declare BFile Cursor for c+ Select fhot,fhpro,fhdt,fhddat,fhdtim,fhdadt, c+ fhpudt,fhscd,fhccd,fhcct,fhczip from c+ AFile Where c+ fhddat>= :FromDate and fhddat<= :ToDate c/end-exec c/exec sql c+ Open BFile c/end-exec **** check SQlcod or SQLstate for error on open if no errors /free DoU SQLCOD=100; /end-free * this is equal to a read statement c/exec sql c+ Fetch BFile Into :Mydatastruct c/end-exec *** do more stuff /free EndDo; /end-free this is kinda ugly but I coded it in notepad LOL!!! Fromdate & todate would be your parameter fields. Thanks, Tommy Holden -----Original Message----- From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of fkany@xxxxxxxxxxxxxxxxxx Sent: Thursday, April 07, 2005 10:40 AM To: CN=RPG programming on the AS400/O=iSeries Subject: RE: What would be your SQL solution? FILEA is actually a logical file keyed on FHDDAT. Ok, so lets get rid of RPGPGM1. So how can RPGPGM2 be coded using SQL to get all the data needed with in the specified date range? I hope the below pseudocode helps. It shows what the original program was doing. It's the process of building fileb that takes the longest. Once fileb is built, the report only takes a few seconds to print. If you need more details, just let me know. The subprocedure collects all the records needed and any associated information about the record. I was thinking that maybe if I could narrow down the number of records that the subprocedure had to read through, that maybe the subprocedure would complete faster. Begin Subprocedure fhddat setll filea start loop: read filea by fhddat(delivery date) validate record if record is good get more information about record from file1(keyed on FHOT & FHPRO) get more information about record from file2(keyed on FHDT to convert FHDT from 001 to ABC, then keyed on FHOT to convert FHOT from 002 to XYZ) call program to gather more information from various files write record to fileb endif end loop: End Subprocedure "Wilt, Charles" <CWilt@xxxxxxxxxxxx>@midrange.com on 04/07/2005 09:50:36 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? 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) > '* > '* ---------- > '* > '* ---------------------- > D FROMDATE S 8S 0 > D TODATE S 8S 0 > '* > '* Arrays/Data Structures > '* ---------------------- > '* > '* ----------- > ?* Main Processing > D MAIN_PRCS PR > > ************************************************************* > ********* > '* > '* --------------------- > C CALLP MAIN_PRCS > '* > '* ------------------- > ?* End Program, Return to Caller > C EVAL *INLR = *ON > C RETURN > * > ?* > C *ENTRY PLIST > C PARM FROMDATE > C PARM TODATE > ?* > * > ?* > > > -- > 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. -- 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-2024 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.
midrange.com
