|
Rick, I think the standard behavior is for the access plan to be re-evaluated at runtime every time it executes. By re-evaluation, I mean it regathers the base statistics and checks that they are similar to the existing plan. If any significant changes are detected (like the addition of many new records, or the creation of new indexes), the optimizer rebuilds the access plan to match the new environment. I remember crossing paths with a document on IBMs DB2 site that explained this in detail, but that was last year sometime.... Eric DeLong Sally Beauty Company MIS-Project Manager (BSG) 940-898-7863 or ext. 1863 -----Original Message----- From: Rick.Chevalier@xxxxxxxxxxxxxxx [mailto:Rick.Chevalier@xxxxxxxxxxxxxxx] Sent: Thursday, July 08, 2004 1:02 PM To: rpg400-l@xxxxxxxxxxxx Subject: RE: SQL embeded in RPG with passed parameters won't use indexes 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. -- 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
