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

Hi Otto,

Honestly, I doubt there are many who could give a definitive explanation of
how the optimizer arrives at an access solution, but you have the tools to
dig into this deeper if you want.  If you haven't tried it recently,
Operations Navigator offers a database monitoring facility that can dig out
the access plan information used by the query at runtime.  

I wonder if this might be related to the new SQE query engine versus the CQE
(classic query engine).  I really don't know much about the new SQE, as
we're still on V5R1.  I can't remember if SQE was released at V5R2 or V5R3.

Eric DeLong
Sally Beauty Company
MIS-Project Manager (BSG)
940-898-7863 or ext. 1863



-----Original Message-----
From: Steffan, Otto (GE Consumer Finance) [mailto:Otto.Steffan@xxxxxx]
Sent: Thursday, July 08, 2004 3:36 AM
To: RPG programming on the AS400 / iSeries
Subject: RE: SQL embeded in RPG with passed parameters won't use indexes



>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

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