× 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.



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 thread ...

Follow-Ups:

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.