× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.

As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.