|
Hello, The best way to solve your problem is to define two Standalone fields (15,5) and move the contents of startdate & enddate into this fields and then use these standalone fields in your embedded SQL Statements. Avoid using using the actual parameters in SQL statements, but rather move their contents into workfields. "The information in this email and in any attachments is confidential and may be privileged. If you are not the intended recipient, please destroy this message, delete any copies held on your systems and notify the sender immediately. You should not retain, copy or use this email for any purpose, nor disclose all or any part of its contents to any other person". -----Original Message----- From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of rpg400-l-request@xxxxxxxxxxxx Sent: 07 July 2004 23:25 To: rpg400-l@xxxxxxxxxxxx Subject: RPG400-L Digest, Vol 3, Issue 481 Send RPG400-L mailing list submissions to rpg400-l@xxxxxxxxxxxx To subscribe or unsubscribe via the World Wide Web, visit http://lists.midrange.com/mailman/listinfo/rpg400-l or, via email, send a message with subject or body 'help' to rpg400-l-request@xxxxxxxxxxxx You can reach the person managing the list at rpg400-l-owner@xxxxxxxxxxxx When replying, please edit your Subject line so it is more specific than "Re: Contents of RPG400-L digest..." Today's Topics: 1. RE: Secret SEU commands was (RE: SEU vs. ?) (Jon Paris) 2. RE: SQL embeded in RPG with passed parameters won't use indexes (Steffan, Otto (GE Consumer Finance)) 3. Re: Ternary operator (Barbara Morris) 4. RE: SQL embeded in RPG with passed parameters won't use indexes (DeLong, Eric) ---------------------------------------------------------------------- message: 1 date: Wed, 7 Jul 2004 16:34:25 -0400 from: "Jon Paris" <Jon.Paris@xxxxxxxxxxxxxx> subject: RE: Secret SEU commands was (RE: SEU vs. ?) >> We are at V4R5 but are supposed to be upgrading this month to V5R3. If you are on software subscription you still have the rights to CODE etc. However, I'm not certain that the current version of CODE/WDSc will connect correctly to V4R5. Maybe one of the IBMers - or someone else who is on that release can comment. Jon Paris Partner400 www.Partner400.com ------------------------------ message: 2 date: Wed, 7 Jul 2004 22:36:55 +0200 from: "Steffan, Otto \(GE Consumer Finance\)" <Otto.Steffan@xxxxxx> subject: RE: SQL embeded in RPG with passed parameters won't use indexes Hello, my experience with SQL optimizer is that I must use exactly matching data types/formats in where, join etc clauses so that SQL optimizer makes use of indexes I have defined. In Your situation, SQL cannot use indexes as the format of numerics You provide are wider I suppose. What errors do You get when passing 7P,0 params?? How do You pass them to the program? Otto Steffan -----Original Message----- From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Chris Payne Sent: Wednesday, July 07, 2004 10:09 PM To: 'RPG programming on the AS400 / iSeries' Subject: RE: SQL embeded in RPG with passed parameters won't use indexes Hi, DTMV95 has 7P 0, I originally defined startdate and enddate as 7P 0 but got many strange errors when I passed in the parameters, they seemed to be getting corrupted somehow? Chris -----Original Message----- From: Steffan, Otto (GE Consumer Finance) [mailto:Otto.Steffan@xxxxxx] Sent: Wednesday, July 07, 2004 3:57 PM To: RPG programming on the AS400 / iSeries Subject: RE: SQL embeded in RPG with passed parameters won't use indexes Hello, what data format does DTMV95 column have? It ought to be 15P 5 I guess if host vars are 15P 5... Otto Steffan -- 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. ------------------------------ message: 3 date: Wed, 07 Jul 2004 17:46:06 -0400 from: Barbara Morris <bmorris@xxxxxxxxxx> subject: Re: Ternary operator Joe Pluta wrote: > ... > I know that at one point the RPG compiler folks floated the possibility > of a ternary operator (in Java, it's the "?", as in: > > maxval = (val1 > val2) ? val1 : val2; > > In this, the condition to the left of the "?" is tested, and if true, > the value to the left of the ":" is used, otherwise the value to the > right is used. > > Did this show up in V5R3? > No. Me, I think the only place that's really useful is in C function-type macros, where you can't an "if" structure. Personally, I don't see this construct ever getting into RPG. To see what's new in RPG for each release, look at the beginning of the Programmer's Guide, in the "What's New in This Release" section. ------------------------------ message: 4 date: Wed, 7 Jul 2004 17:21:45 -0500 from: "DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx> subject: RE: SQL embeded in RPG with passed parameters won't use indexes Did you specify AlwCpyData *Optimize when you compiled the RPG program? If you compiled with *yes, then the optimizer will only use the index scan if it's "absolutely necessary"... See the following site for some suggestions from IBM teraplex: http://www-1.ibm.com/servers/enable/site/bi/teraplex/lessons.html You might also want to experiment with the QAQQINI file that your job uses. This topic is covered in the same document, so be sure to read it through carefully. Eric DeLong Sally Beauty Company MIS-Project Manager (BSG) 940-898-7863 or ext. 1863 -----Original Message----- From: Chris Payne [mailto:CPayne@xxxxxxxxxxxxxxx] Sent: Wednesday, July 07, 2004 2:42 PM To: 'RPG programming on the AS400 / iSeries' Subject: RE: SQL embeded in RPG with passed parameters won't use indexes I don't think that it is the like operator because when it read Select stuff >From tables where (PNUM95 LIKE '* LABOR%' OR PNUM95 like '*LABOR%') and DTMV95 >= 1040101 and dtmv95 <= 1040231 it accessed my index and ran in about 8 seconds. But when I changed it to select stuff from tables where (PNUM95 LIKE '* LABOR%' OR PNUM95 like '*LABOR%') and DTMV95 >= :startdate and dtmv95 <= :enddate it did a table scan And took about 15 minutes I did run it with strdbg and got the following output Additional Message Information Page 1 5722SS1 V5R1M0 010525 S103PN4M 07/07/04 15:35:23 Message ID . . . . . . : CPI432C Severity . . . . . . . : 00 Date sent . . . . . . : 07/07/04 Time sent . . . . . . : 13:54:14 Message type . . . . . : Information From program . . . . . . . . . : QQQIMPLE From library . . . . . . . . : QSYS From module . . . . . . . . : QQQIMPLE From procedure . . . . . . . : QQQSNDPMSG From statement . . . . . . . : 3248 To program . . . . . . . . . . : QSQOPEN To library . . . . . . . . . : QSYS To module . . . . . . . . . : QSQOPEN To procedure . . . . . . . . : FULL_OPEN To statement . . . . . . . . : 20576 Coded character set ID . . . . : 65535 Message . . . . : All access paths were considered for file INP95. Cause . . . . . : The OS/400 Query optimizer considered all access paths built over member INP95 of file INP95 in library OSLD1F3. The list below shows the access paths considered. If file INP95 in library OSLD1F3 is a logical file then the access paths specified are actually built over member INP95 of physical file INP95 in library OSLD1F3. Following each access path name in the list is a reason code which explains why the access path was not used. A reason code of 0 indicates that the access path was used to implement the query. CPAYNE/LABOR 6, OSLD1F3/STKMVTR 4, OSLD1F3/INP95L03 4, OSLD1F3/STKMVTW 4, OSLD1F3/STKMVTP 4, OSLD1F3/STKMVTC 4, OSLD1F3/INP95L01 4, OSLD1F3/STKMVTNA 4, OSLD1F3/STKMVTPSL 4, OSLD1F3/INP95LWK 4, OSLD1F3/INP95L02 4, OSLD1F3/STKMVTAR 4, OSLD1F3/STKMVTA 4, OSLD1F3/STKMVT 4, OSLD1F3/STKMVTPL 4, OSLD1F3/STKMVTPS 4. The reason codes and their meanings follow: 1 - Access path was not in a valid state. The system invalidated the access path. 2 - Access path was not in a valid state. The user requested that the access path be rebuilt. 3 - Access path is a temporary access path (resides in library QTEMP) and was not specified as the file to be queried. 4 - The cost to use this access path, as determined by the optimizer, was higher than the cost associated with the chosen access method. 5 - The keys of the access path did not match the fields specified for the ordering/grouping criteria. For distributed file queries, the access path keys must exactly match the ordering fields if the access path is to be used when ALWCPYDTA(*YES or *NO) is specified. 6 - The keys of the access path did not match the fields specified for the join criteria. 7 - Use of this access path would not minimize delays when reading records from the file. The user requested to minimize delays when reading records from the file. 8 - The access path cannot be used for a secondary file of the join query because it contains static select/omit selection criteria. The join-type of the query does not allow the use of select/omit access paths for secondary files. 9 - File INP95 contains record ID selection. The join-type of the query forces a temporary access path to be built to process the record ID selection. Additional Message Information Page 2 5722SS1 V5R1M0 010525 S103PN4M 07/07/04 15:35:23 10 and greater - View the second level message text of the next message issued (CPI432D) for an explanation of these reason codes. Recovery . . . : The user may want to delete any access paths no longer needed. * * * * * E N D O F L I S T I N G * * * * * -----Original Message----- From: DeLong, Eric [mailto:EDeLong@xxxxxxxxxxxxxxx] Sent: Wednesday, July 07, 2004 3:16 PM To: 'RPG programming on the AS400 / iSeries' Subject: RE: SQL embeded in RPG with passed parameters won't use indexes Chris, Just passing through your code quickly just brings to mind a dim memory... As I recall, whenever you use the LIKE op in the WHERE clause, it forces SQL to revert to table-scan process, where every record in the file is tested individually. It absolutely will not allow the use of index bitmaps for record selection, and performance is about what you'd expect for a string scan.... Try changing your logic to something like this... C+ (Left(PNUM95,7) = '* LABOR' OR C+ Left(PNUM95,6) = '*LABOR') and and see if it helps.... You didn't say if you ran the job in debug mode, so if you did not, I suggest you try it and look in you joblog for the query optimizer messages. They'll give an explanation of some of the issues the optimizer had with your query. Eric DeLong Sally Beauty Company MIS-Project Manager (BSG) 940-898-7863 or ext. 1863 -----Original Message----- From: Chris Payne [mailto:CPayne@xxxxxxxxxxxxxxx] 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) digest 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. End of RPG400-L Digest, Vol 3, Issue 481 ****************************************
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
