That's right.  It was a surprise to me when IBM told me this little tidbit.
We have an application that was giving between 3 to 6 minute response time
after we went to V5R2.  It always had poor response but not that bad.  I
called Tech Support at IBM and was told to create an index over the file.  I
said I had an index and that is what the SQL used in it's select,  They said
no, create an SQL index.  I forget the statistic but it has to do with the
amount of data SQL will fetch when using an SQL index vs. the access path of
a logical file.  I ran the application under debug, reviewed the optimizers
suggestions and created every index suggested.  The application now gives 1
second or better response.  My problem is how to predict what index SQL will
want to use.
Bill Erhardt

-----Original Message-----
From: rick.baird@xxxxxxxxxxxxxxx [mailto:rick.baird@xxxxxxxxxxxxxxx] 
Sent: Thursday, March 25, 2004 8:05 AM
To: Midrange Systems Technical Discussion
Subject: Re: Query optimizer tells me to build an access path that already
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.

-----------------------------------------
This message was scanned for viruses.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2022 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.