RE: SQL embeded in RPG with passed parameters won't use indexes

["Steffan, Otto \(GE Consumer Finance\)" <Otto.Steffan@xxxxxx>]

>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"...
>Eric DeLong
>Sally Beauty Company

Hello Eric, 
thanks for the reference to the IBM performance tips from Teraplex. 

What I don't understand in Your mail is that SQL optimizer implements indexes 
to process a select with option AlwCpyData = *YES only if it's "absolutely 
necessary". 

I tried a simple select on my database that returned just 1 record from 
1million table. Regardless of AlwCpyData setting (*YES or *Optimize), SQL 
optimizer decided to choose the same logical file to access the record. I 
cannot figure out why was "absolutely necessary" to implement the logical in 
case of AlwCpyData = *YES. SQL optimizer could create a temporary index over 
the 1million table and get the record using the temp index but it didn't do it.
The help texts on AlwCpyData in STRSQL, RUNSQLSTM or DSPPGM DETAIL(*MODULE) on 
compiled SQL procedure are rather laconic, humble. 

I may be overlooking something...we are on V5R2.

Kind regards
Otto Steffan