× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. RPG400-L
  3. April 2005

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.

As an Amazon Associate we earn from qualifying purchases.

This thread ...
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.