RE: What would be your SQL solution? -- RPG400-L


Haha...victory is mine =).  The d**n thing works.  It doesn't only work,
it's twice as fast.  I accomplished my goal.  I think I can still tweak the
SQL more to make it go faster.  Let's go to happy hour, drinks are on me
;-).....

Thanks for everyones input.  I wouldn't have figured it out without your
help.  Have a good weekend.

Frank





rob@xxxxxxxxx@midrange.com on 04/07/2005 04:49:35 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?


Read the following:
http://publib.boulder.ibm.com/infocenter/iseries/v5r3/ic2924/info/db2/rbafzmst02.htm

You will be using the PREPARE statement and the EXECUTE statement.

You'll end up doing something like
/free
 myvar='DECLARE........WHERE FHDATE>=' + FDATE + '.............';
/end-free

then you'll be doing a sql
prepare stmt from :myvar

then you'll be doing a sql
execute stmt

Ok?

Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





fkany@xxxxxxxxxxxxxxxxxx
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
04/07/2005 04:31 PM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
CN=RPG programming on the AS400/O=iSeries <rpg400-l@xxxxxxxxxxxx>
cc

Subject
RE: What would be your SQL solution?








You're right.  It doesn't like host variables in the RPGIV program for
this
DECLARE statement.  How can I code it to except my host variable?  Here's
my current code:

C/EXEC SQL
C+ DECLARE GLOBAL TEMPORARY TABLE FR47P457A AS (SELECT FHOT, FHPRO,
C+ FHDT, FHDDAT, FHDTIM, FHDADT, FHPUDT, FHSCD, FHCCD, FHCCT, FHCZIP
C+ FROM FRL00144 WHERE FHDDAT >= :FDATE AND FHDDAT <= :TDATE) WITH
C+ DATA
C/END-EXEC

with the colons in front of FDATE and TDATE, the program gives a syntax
error message.

Thanks,

Frank





"Ross Hartford" <ross.hartford@xxxxxxxxxxx>@midrange.com on 04/07/2005
10:46:21 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?


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)
     '*'* ----------
     '*'* ----------------------
     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
     ?**?*