Re: Embedded SQL Logical file use

Kyle, You mentioned that you are using SQLRPG, can I assume that you meant
SQLRPGLE?  If not, try converting the code to SQLRPGLE.  I have a program
that I prepare a statement and depending on the user selection, I pick
which file that needs to be used in the SQL statement.

For example, if the user chooses to search the employee master by last
name, I chose a logical file that has the last name as the first part of
the key.  If user chooses company and department, then I choose a logical
file that has company and department first.  My processing time in this
program went from 5 minutes to 10 seconds.

Something in the WHERE statement is causing SQL to search for a better
file.

You could always not write it in SQL... Then you will not have to worry
about it.

Michael Schutte
Work 614-492-7419
email  michael_schutte@xxxxxxxxxxxx


                                                                           
             "Kyle Collie"                                                 
             <kcollie@xxxxxxxx                                             
             m>                                                         To 
             Sent by:                  "RPG List" <rpg400-l@xxxxxxxxxxxx>  
             rpg400-l-bounces@                                          cc 
             midrange.com                                                  
                                                                   Subject 
                                       Embedded SQL Logical file use       
             03/29/2006 06:41                                              
             PM                                                            
                                                                           
                                                                           
             Please respond to                                             
              RPG programming                                              
              on the AS400 /                                               
                  iSeries                                                  
             <rpg400-l@midrang                                             
                  e.com>                                                   
                                                                           
                                                                           




We have an SQLRPG module that is opening a cursor based on a S/O
logical. Everytime the program is run the query optimizer is checking
for a better file to use and is eventually just coming back and using
the initial S/O logical that we specified in the select statement. Is
there a way to force the logical and prevent that processing time that
is wasted when it tries to find a better index?

The time it takes up is about 1 minute 20 seconds.

Thanks in advance,

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