|
midrange.com
Rick,
An earlier suggestion solved the problem, but I am interested in
your statements about preparing the statement. It seems like the access plan
for my program is built at run time even without a prepare statement, at
least judging from the job log, do you know under what conditions the access
plan is built at compile time?
Chris
-----Original Message-----
From: Rick.Chevalier@xxxxxxxxxxxxxxx [mailto:Rick.Chevalier@xxxxxxxxxxxxxxx]
Sent: Thursday, July 08, 2004 9:40 AM
To: rpg400-l@xxxxxxxxxxxx
Subject: RE: SQL embeded in RPG with passed parameters won't use indexes
Chris,
I may be off base here but try preparing the statement before declaring the
cursor. I suspect that the pre-compiler can't generate the access plan
properly at compile time. Changing the access plan generation to happen
during your program by using a prepare might cause it to generate a better
access plan.
I know the prepare is not required with the way you are handling the SQL
statement currently, but it might make a difference.
HTH,
Rick
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Chris Payne
Sent: Wednesday, July 07, 2004 1:10 PM
To: 'rpg400-l@xxxxxxxxxxxxx'
Subject: SQL embeded in RPG with passed parameters won't use indexes
Hello,
I have an RPG ILE program in which I embedded a cursor. I created a
index on date (dtmv95) for the cursor to hit when I open the file. It worked
like a charm. But then I changed the date range in the cursor from being
static to being brought in by a prompt. Now my program will not use my index
when it opens the cursor, it fails with reason code 6 which means that "The
keys of the access path did not match the fields specified for the join
criteria." I think that the optimizer is not 'seeing' the date criteria in
my select because they depend on variables, but I have no idea what to do
about it. Does anyone out there have any experience with this problem?
Below is the code
H dftactgrp(*no) actgrp(*caller)
dmain pr extpgm('labor')
d startdate 15p 5
d enddate 15p 5
dmain pi
d startdate 15p 5
d enddate 15p 5
d thearray ds
d cono 2a
d item 16a
d pdate 7p 0
d labor_$ 6p 0
d shift 2p 0
d pgmn 1p 0
D dclcursorC1 pr
D opencursorC1 pr
D fetchcursorC1 pr n
D UpdateDirect pr n
D UpdateIndirect pr n
D UpdateTemp pr n
D AddRecord pr
D closecursorC1 pr
/free
dclcursorC1();
opencursorC1();
dow fetchcursorC1();
if (item = '* LABOR-DIRECT' or item = '*LABOR-DIR PC1' or item =
'Labor-DIR PC2' or item = '* LABOR CPR DRT' or item =
'* LABOR-DIR WHS' or item = '* LABOR-DIR SEQ' or item =
'* LABOR-DIR PNT');
if not(UpdateDirect());
AddRecord();
UpdateDirect();
endif;
elseif (item = '* LABOR-INDIR' or item = '*LABOR-IND PC1' or
item = '*LABOR-IND PC2' or item = '* LABOR-IND WHS' or
item = '* LABOR-IND SEQ' or item = '* LABOR-IND PNT');
if not(Updateindirect());
AddRecord();
UpdateIndirect();
endif;
elseif (item = '* LABOR-TEMP' or item = '*LABOR-TEMP PC1' or
item = '*LABOR-TEMP PC2' or item = '* LABOR-TEMP WHS' or
item = '* LABOR-TEMP SEQ' or item = '* LABOR-TEMP PNT');
if not(UpdateTemp());
AddRecord();
UpdateTemp();
endif;
else;
//disaster
endif;
enddo;
closecursorC1();
*inlr = *on;
/end-free
*Function definitions
p dclcursorC1 b
C/Exec SQL
C+ DECLARE labor CURSOR FOR
C+ SELECT
C+ cono95,
C+ pnum95,
C+ dtmv95,
C+ mqty95,
C+ nshf62,
C+ decimal(substr(pgmn35,1,1),1)
C+ FROM
C+ OSLD1F3/INP95 T01,
C+ OSLPDF3/PCP62 T02,
C+ OSLD1F3/INP35 T03
C+ WHERE
C+ CONO95 = CONO35 AND
C+ PNUM95 = PNUM35 AND
C+ CONO95 = CONO62 AND
C+ PNUM95 = COMP62 AND
C+ TMMV95 >= REFT62-1 AND
C+ TMMV95 <= REFT62 AND
C+ REFD95 = REFD62 AND
C+ CONO95 >= '02' AND
C+ CONO95 <= '10' AND
C+ (PNUM95 LIKE '* LABOR%' OR
C+ PNUM95 like '*LABOR%') and
C+ DTMV95 >= :startdate and
C+ dtmv95 <= :enddate
C+ FOR READ ONLY WITH NC
C/End-Exec
p dclcursorC1 e
p opencursorC1 b
C/Exec SQL
C+ OPEN labor
C/End-Exec
p opencursorC1 e
p fetchcursorC1 b
D pi n
d continueread s n
C/Exec SQL
C+ FETCH NEXT FROM LABOR INTO :cono, :item, :pdate, :labor_$,
C+ :shift, :pgmn
C/End-Exec
/free
continueread = *on;
if (sqlstt = '02000');
continueread = *off;
endif;
return continueread;
/end-free
p fetchcursorC1 e
p UpdateDirect b
D pi n
d record s n
C/Exec SQL
C+ UPDATE CPAYNE/PACKOFF SET Direct_labor_$ = :labor_$ WHERE COMPANY
C+ = :cono and PDATE = :pdate and SHIFT=:shift and PROFIT_CENTER =
C+ :pgmn
C/End-Exec
/free
record = *on;
if (sqlstt = '02000');
record = *off;
endif;
return record;
/end-free
p UpdateDirect e
p UpdateIndirect b
D pi n
d record s n
C/Exec SQL
C+ UPDATE CPAYNE/PACKOFF SET Indirect_labor_$ = :labor_$ WHERE COMPANY
C+ = :cono and PDATE = :pdate and SHIFT=:shift and PROFIT_CENTER =
C+ :pgmn
C/End-Exec
/free
record = *on;
if (sqlstt = '02000');
record = *off;
endif;
return record;
/end-free
p UpdateIndirect e
p UpdateTemp b
D pi n
d record s n
C/Exec SQL
C+ UPDATE CPAYNE/PACKOFF SET Temp_labor = :labor_$ WHERE COMPANY
C+ = :cono and PDATE = :pdate and SHIFT=:shift and PROFIT_CENTER =
C+ :pgmn
C/End-Exec
/free
record = *on;
if (sqlstt = '02000');
record = *off;
endif;
return record;
/end-free
p UpdateTemp e
p closecursorC1 b
C/exec sql
C+ close labor
C/end-exec
p closecursorC1 e
p AddRecord b
C/Exec SQL
C+ INSERT INTO CPAYNE/PACKOFF
C+ (COMPANY, PDATE, SHIFT, PROFIT_CENTER)
C+ VALUES(:cono,:pdate,:shift,:pgmn)
C/End-Exec
p AddRecord e
--
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-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.
