|
My understanding is that it would only be created at run time if the statement is dynamically prepared. That's why I wasn't sure it would apply in your case. Having a static statement (even though it contains variables) allows the pre-compiler to create the access plan and attach it to the program at compile time providing a performance boost. I'm sure there are others out there who could provide a more in depth description. Rick -----Original Message----- From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Chris Payne Sent: Thursday, July 08, 2004 12:03 PM To: 'RPG programming on the AS400 / iSeries' Subject: RE: SQL embeded in RPG with passed parameters won't use indexes 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. -- 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.
midrange.com
