SQL SELECT RETRIEIVAL TIME

["Smith, Dave" <DSmith@xxxxxxxxx>]

I recently converted a RPGLE program that was reading through several files to 
produce a subfile like screen to present information to the user.   I converted 
this program to use embedded SQL.  In a nutshell, the user enters any 
combination of Item, Item Group, and/or Buyer to display.  The program 
dynamically builds the SQL statement, PREPAREs the statement, and loads the 
CURSOR with the result set.  This program is used every day (all day) by about 
50 different users.  It appears that the first person in each day to use this 
program has a 15-20 second wait time for the SELECT  to execute.  All access by 
all users (including the original user) after that time is almost immediate 
regardless if the selection criteria changes.   I'm assuming the query 
optimizer is doing something that first time that is affecting the retrieval 
time.  What I want to know, is what can be done so it does not have to re-do 
this every day the first time in?  This is not a huge issue, but more a 
curiosity.

We are running V5R1.  I have run all the various combinations of the SELECT 
statement via the Run and Explain option in iSeries Navigator and created all 
the suggested indexes.  There is one View involved in the select statement that 
is joining two files.  I do not see any new access paths being created at run 
time.  Any ideas would be appreciated.  Thanks!

David Smith
IT Consultant
dsmith@xxxxxxxxx
517.546.5400