|
midrange.com
Hi Frank,
Yes, please select the fields that you have declare in the DS. Or create a ds
with only 2/3 fields that you are selecting. Please also remove the qualified,
this also works from v5r3.
SQL is great and I was commenting more on your multiple row fetch which you
need not do for the kind of process you are working on. If you are replacing a
read, I suggest you could fetch in a loop as below. (If you are going this
route, please change you multiple occurs DS to single.)
Declare cursor ..
Open cursor
Dou sqlstt/sqlcod check
Fetch cursor into :MyDs
doAllProcess()
enddo
close cursor
HTH.
Thanks,
Sudha
Sudha Ramanujan
SunGard Futures Systems
sramanujan@xxxxxxxxxxxxxxxxxx
(312) 577 6179
(312) 577 6101 - Fax
-----Original Message-----
From: fkany@xxxxxxxxxxxxxxxxxx [mailto:fkany@xxxxxxxxxxxxxxxxxx]
Sent: 2005年4月5日 10:14
To: CN=RPG programming on the AS400/O=iSeries
Subject: RE: SQLRPGLE won't compile...
Hello Sudha,
>>As mentioned by others earlier, there seems to be a mismatch in the
>>fields retrieved and the DS declared. Please try to declare only the
>>fields you fetch as the DS or try to declare for all the fields in the
>>DS for it to compile.
In the 'DECLARE FREIGHT CURSOR' I need to 'SELECT' all the fields I'll be
using?
>>I have never found any particular reason (other than say a subfile) to
>>fetch 50 records at a time, since you have to loop through them
>>individually to process them any way. Is there any reason for not
>>fetching in a loop? As I see it, the cursor is declared and retained
>>till the entire process is finished and there may not be any
>>process/memory gain by fetching 50 records at a time.
I've used FETCH in a loop to replace a READ, but did not notice any program
performance gains. I'm hoping to use SQL in the most effecient manner to
increase program performance.
There was an earlier topic on SQL VIEWS that sparked my interest in trying
out SQL. I've asked for explanations and have received a lot of feedback,
but I could only understand some of the syntax of the explanations. Kind
of like when you learn Spanish or French, then you actually listen to a
fluent spanish or french speaking person and you can only pick up some of
what they're saying.
Currently on Chapter 2(Getting Started with SQL) of "DB2 UDB for AS/400 SQL
Programming".....=)
Frank
<SRamanujan@xxxxxxxxxxxxxxxxxx>@midrange.com on 04/05/2005 10:48:27 AM
Please respond to RPG programming on the AS400 / iSeries
<rpg400-l@xxxxxxxxxxxx>
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
To: <rpg400-l@xxxxxxxxxxxx>
cc:
Subject: RE: SQLRPGLE won't compile...
Frank,
As mentioned by others earlier, there seems to be a mismatch in the
fields retrieved and the DS declared. Please try to declare only the
fields you fetch as the DS or try to declare for all the fields in the
DS for it to compile.
As also mentioned earlier, the DIM could not be used until v5r3. I have
successfully used occurs.
In the code, I see couple of unnecessary End-Exec in the code. I presume
it is because this is a snapshot of the code. I am not certain if you
are using the SQLCA to determine the EOFFLG.
SQLERRD(3) will return the number of rows fetched.
SQLERRD(5) will return the DB2_LAST_ROW (100 if eof)
I have never found any particular reason (other than say a subfile) to
fetch 50 records at a time, since you have to loop through them
individually to process them any way. Is there any reason for not
fetching in a loop? As I see it, the cursor is declared and retained
till the entire process is finished and there may not be any
process/memory gain by fetching 50 records at a time.
Sudha Ramanujan
SunGard Futures Systems
sramanujan@xxxxxxxxxxxxxxxxxx
(312) 577 6179
(312) 577 6101 - Fax
-----Original Message-----
From: fkany@xxxxxxxxxxxxxxxxxx [mailto:fkany@xxxxxxxxxxxxxxxxxx]
Sent: Tuesday, April 05, 2005 9:22 AM
To: CN=RPG programming on the AS400/O=iSeries
Subject: Re: SQLRPGLE won't compile...
I need those fields in my " 1 DO 50 X " processing loop. I'm
using
the SQL fetch to grab
50 records, process the records, write records to a file, then go get
the
next 50 records. This loop was created for the SQL block fetch.
I'd rather not use a loop with in a loop if possible.
Is there a simpler method of getting all the data I need at 1 time and
just
loop through those records, instead of getting just 50 at a time and
looping through
those 50, then grabbing another 50?
Thanks,
Frank
"Russell Conerly" <rconerly@xxxxxxxxxxx>@midrange.com on 04/04/2005
10:52:02 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: SQLRPGLE won't compile...
Ok...The other thing I noticed is that you are selecting two fields in
the
cursor Freight. Your DS has around 12 fields...eliminate the extra
fields.
Regarding performance...change your Where clause to: WHERE FHDDAT
between
:FDATE AND :TDATE. This statement is a little bit easier to code and to
follow. I also believe it will help from a performance standpoint.
This
statement is inclusive of the FROM and the TO date.
I also noticed you have DISTINCT coded. I'm assuming that you have
multiple FHOT, FHPRO and you are trying to pair these down. If
not...nix
the distinct. Do you have an index already ordered by FHOT & FHPRO?
The
query optimizer will probably require the addition of FHDDAT to the
index
also.
Once you get the program to compile...place it in debug and place a
breakpoint after the OPEN statement....check the job log...it will tell
you which access path it used or it will tell you what index it needs to
perform efficiently.
We'll keep plugging until we get it.
Regards,
Russell Conerly
Tupelo, MS
>>
>> The program below(only relevant code included), won't compile. I get
> this
>> one error:
>> "Position 49 Host structure array FHSQLREC not defined or not
usable."
>> I've marked the section of code that is
>> giving the message with "ERROR".
>>
>> Can anyone see what the problem is? Also, please let me know if I'm
not
>> using the embedded SQL correctly.
>>
>> TIA,
>>
>> Frank
>>
>
========================================================================
==============
>
>> ?* SQL block FETCH array data structure
>> D FHSQLREC DS QUALIFIED
>> D FHSQLFLDS DIM(50) LIKE(DUMMY)
>> D FHOT LIKE(DUMMY.FHOT)
>> INZ(*ZEROS)
>> D OVERLAY(FHSQLFLDS)
>> D FHPRO LIKE(DUMMY.FHPRO)
>> INZ(*ZEROS)
>> D OVERLAY(FHSQLFLDS:*NEXT)
>> D FHDT LIKE(DUMMY.FHDT)
>> INZ(*ZEROS)
>> D OVERLAY(FHSQLFLDS:*NEXT)
>> D FHDDAT LIKE(DUMMY.FHDDAT)
>> INZ(*ZEROS)
>> D OVERLAY(FHSQLFLDS:*NEXT)
>> D FHPUDT LIKE(DUMMY.FHPUDT)
>> INZ(*ZEROS)
>> D OVERLAY(FHSQLFLDS:*NEXT)
>> D FHDADT LIKE(DUMMY.FHDADT)
>> INZ(*ZEROS)
>> D OVERLAY(FHSQLFLDS:*NEXT)
>> D FHSCD LIKE(DUMMY.FHSCD)
>> INZ(*ZEROS)
>> D OVERLAY(FHSQLFLDS:*NEXT)
>> D FHCCD LIKE(DUMMY.FHCCD)
>> INZ(*ZEROS)
>> D OVERLAY(FHSQLFLDS:*NEXT)
>> D FHCCT LIKE(DUMMY.FHCCT)
> INZ(*BLANKS)
>> D OVERLAY(FHSQLFLDS:*NEXT)
>> D FHCST LIKE(DUMMY.FHCST)
> INZ(*BLANKS)
>> D OVERLAY(FHSQLFLDS:*NEXT)
>> D FHCZIP LIKE(DUMMY.FHCZIP)
>> INZ(*ZEROS)
>> D OVERLAY(FHSQLFLDS:*NEXT)
>> D FHDTIM LIKE(DUMMY.FHDTIM)
>> INZ(*ZEROS)
>> D OVERLAY(FHSQLFLDS:*NEXT)
>> '*
>> D FHDDAT LIKE(AFHDDAT)
>> D FHPUDT LIKE(AFHPUDT)
>> D FHDADT LIKE(AFHDADT)
>> D FHSCD LIKE(AFHSCD)
>> D FHCCD LIKE(AFHCCD)
>> D FHCCT LIKE(AFHCCT)
>> D FHCST LIKE(AFHCST)
>> D FHCZIP LIKE(AFHCZIP)
>> D FHDTIM LIKE(AFHDTIM)
>>
>
========================================================================
====
>
>> *
**********************************************************************
>> P GET_TERM_BILLS B
>> ?*
>> C #OT1 CHAIN AAL03002
>> C IF %FOUND(AAL03002)
>> C EVAL NUM#OT1 = CT1TID
>> C ENDIF
>> C ENDIF
>> ?*
>> C #DT1 CHAIN AAL03002
>> C IF %FOUND(AAL03002)
>> C EVAL NUM#ODT = CT1TID
>> C ENDIF
>> C ENDIF
>> ?*
FHPRO FROM
>> C+ FRL00144 WHERE FHDDAT >= :FDATE AND FHDDAT <= :TDATE ORDER BY
>> C+ FHOT, FHPRO
>> C/END-EXEC
>> '*
>> C DOU EOFFLG = 'Y'
>> ?*
>> ?*
>> C CALLP CHK_TERM_BILLS
>> ?*
>> C ENDIF
>> ?*
>> C ENDIF
>> ?*
?*
--
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.
