|
midrange.com
No data after the insert statement for newfile in debug mode. Is there
anything that I could be missing. I entered everything except for the (H)
specs. Can you explain what the (H) specs mean? Are the only fields I
need to change 'RESA' and 'IO_BNDDIR' , 'RLIRESAOBJ' or can I just copy the
entire H spec and leave it as is?
Thanks,
Frank
"Richard ECUYER" <recuyer@xxxxxxx>@midrange.com on 04/07/2005 10:12:59 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?
here is a little sample
* ________________________________________________________________________
* INSERT FROM
* ________________________________________________________________________
H DEBUG DATEDIT(*DMY)
h DftActgRp(*NO)
h ActGRp('RESA')
h fixnbr(*zoned:*INPUTPACKED)
h DECEDIT(*JOBRUN)
h Bnddir('IO_BNDDIR': 'RLIRESAOBJ')
* ________________________________________________________________________
* Paramètres
* ________________________________________________________________________
c *entry Plist
c Parm DATe1 8 0
c Parm DATe2 8 0
c/EXEC SQL
c+ Set Option
c+ Naming = *Sys,
c+ Commit = *None,
c+ UsrPrf = *User,
c+ DynUsrPrf = *User,
c+ Datfmt = *iso,
c+ CloSqlCsr = *EndMod ,
c+ Commit = *none
c/END-EXEC
*
* change after the select filed with your field name in the right order
c/exec sql
c+ insert into newfile (select
c+ field1, field2 .....
c+ from file where date in (:date1 , :date2) )
C/END-EXEC
i made a little try form a 4.500.000 records file, it take about 2 minutes
to extract 500.000 records based on a in statement without index built on
it.
hope this help.
----- Original Message -----
From: <fkany@xxxxxxxxxxxxxxxxxx>
To: "CN=RPG programming on the AS400/O=iSeries" <rpg400-l@xxxxxxxxxxxx>
Sent: Thursday, April 07, 2005 4:49 PM
Subject: Re: What would be your SQL solution?
>
>
> >first , try to use interactive SQL for the select :
>
> >STRSQL
> >F13 to change sql option : use a new file
> >Select your fields from yourfile where filedate in (firstdate ,
lastdate)
> >Take the time it make.
>
> Users don't have access to SQL, so I think the solution would still have
to
> still be embedded SQL inside the RPGIV. All the user is going to do is
> enter a date range and press enter.
>
> >Second :
> >Why do you use a work file ? IO are time consumer so perhaps you can
avoid
> >this.
>
> The work file only takes a few seconds to run, hardly time consuming
IMHO.
> It's the gathering of the data that's taking so long.
>
> >Third :
> >I can send you a template of embedded sql if you need.
>
> Please do so. I open to all suggestions.
>
> Thanks,
>
> Frank
>
>
>
>
>
>
>
>
> "Richard ECUYER" <recuyer@xxxxxxx>@midrange.com on 04/07/2005 09:29:58 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?
>
>
> first , try to use interactive SQL for the select :
>
> STRSQL
> F13 to change sql option : use a new file
> Select your fields from yourfile where filedate in (firstdate , lastdate)
> Take the time it make.
>
> Second :
> Why do you use a work file ? IO are time consumer so perhaps you can
avoid
> this.
>
> Third :
> I can send you a template of embedded sql if you need.
>
> ----- Original Message -----
> From: <fkany@xxxxxxxxxxxxxxxxxx>
> To: "CN=RPG programming on the AS400/O=iSeries" <rpg400-l@xxxxxxxxxxxx>
> Sent: Thursday, April 07, 2005 4:16 PM
> 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.
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.
