Re: What would be your SQL solution?

["Richard ECUYER" <recuyer@xxxxxxx>]

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.
>
>