Re: Query optimizer tells me to build an access path thatalready exists

Rick,

It's an interesting topic! We (I was with SSA then) were pretty surprised
too, when we found out that the SQL Optimizer  was looking at our code and
then doing something completely different! But it is worth finding out some
more, because it can make a HUGE performance difference.
You need to remember that an SQL index (you create one by doing STRSQL and
CREATE INDEX, if you prompt on the file it gives you the fields to pick) is
different from a Logical File, and better. The system itself keeps track of
indexes, they don't need to be in the library list. (This is all
counter-intuitive stuff!)
I don't think you need the SQL add-on product to be able to do STRSQL. Try
it and see.
If the Optimizer doesn't find the indexes it needs, it will create them
dynamically and then throw them away - and that takes TIME if the file is
large.
If you see any job in WRKACTJOB doing IDX, then it needs an index. If you
look at the stack of a slow job and see that it is running QDBGETM (get
multiple records), it needs an index.
To really tune up your system and probably get back 40% or so, use the
Database Monitor (usually on the STRPFRMON option or you can start it with
STRDBMON), and use Query to look for records that have QQIDXA (Index
advised) =Y. It's an interesting file, and has lots of different record
formats. These will tell you which files need indexes, and also an estimate
of how long it is taking without them.
Create the indexes, run it again, and so on.
Of course this works best if your application uses SQL, but Query, OPNQRYF,
ODBC etc also go through the SQL Optimizer.
The statistics thing is interesting too - the system needs to know how many
records will meet the criteria, to help it decide how to process the access.
It can usually best get this info from an index created on the key. And
there are other factors too, one is how much memory is in the pool, is
Expert Cache on, and is the activity level as low as possible etc.
If you want an expert tune-up, I can do that for a price too. Or Centerfield
Technology have a product that helps, but that also has a price!
But the easiest way to see if it will help you is to try it - you'll be
amazed...

Have fun,

Clare

Clare Holtham
Director, Small Blue Ltd - Archiving for BPCS
Web: www.smallblue.co.uk
IBM Certified AS/400 Systems Professional
E-Mail: Clare.Holtham@xxxxxxxxxxxxxxx
Mobile: +44 (0)7960 665958

----- Original Message -----
From: <rick.baird@xxxxxxxxxxxxxxx>
To: "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
Sent: Thursday, March 25, 2004 1:04 PM
Subject: Re: Query optimizer tells me to build an access path thatalready
exists


>
> Clare,
>
> No, I haven't created an index with the same keys as the physical.  Are
you
> saying that I should ALWAYS build an index (logical or sql) over the
> primary key of a big file? That seems contrary to everything I've ever
> known in 20 years of midrange development.
>
> We don't have the sql product right yet (new box, June install).  But to
> create a logical over the physical's primary key seems a little redundant,
> don't you think?
>
> I've seen lines of thought bandied about here and other forums where all
> physicals should be un-keyed, with logicals built for any indices needed,
> but the reasons for doing so weren't for performance.
>
> Anyway, I'm trying to get this job to run faster.  I can't imagine that
> actually following the QO's recommendation and creating a logical over the
> same keys as the physical would do any good.  Do you think creating a join
> LF would help?
>
> Rick
>
> -----original message-------
> That's one of the indexes you ALWAYS want to build. The Optimizer may well
> use it for statistics (data skew). Or it may want to use an index from
> index
> access method or whatever.
> Or do you mean you have already created the index? That can happen too.
> When
> the optimizer looks for an index, it may use different criteria or time
out
> before it gets to the right one. These rules are different depending which
> version of the OS/400 you have. Sometimes it searches fifo, sometimes the
> other way, etc.
> If the file is really huge you could also try creating an EVA index.
> If you are on V5R2 there are now two different SQL Optimizers with
> different
> rules, and a new statistics gatherer.
> If you're in the US, I believe IBM do workshops on this subject....
>
> cheers,
>
> Clare
>
>
> _______________________________________________
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.