RE: Index advisor advising index on primary key

Perhaps IBM has changed the underlying implementation with V6R1, but I think
the way that interface works is that iNav looks at the file over which index
is being advised and then interrogates SQE plan cache for any statements
that go against that file and 'picks' good candidates. Obviously, if SQE
plan cache has been cleared since the index was advised (i.e. IPL), you're
out of luck. Also, if the index advice came from CQE, you're out of luck,
since no CQE statements are maintained in SQE plan cache (obviously).
Bottom line is that two pieces of information are disconnected (index advice
and statements that caused it) so finding it after the fact may or may not
work. It should work most of the time since SQE restrictions are very
limited with V6R1 (going against the LF in the FROM clause being the chief
one).

With dbmon (aka SQL Performance Monitors) these two pieces of information
are connected and you can see which statement caused the index advice in the
iNav reports. Or <vendor hat on> use Centerfield tooling to get at index
advice </vendor hat off>.

All that said, I'd be interested to see what IBM says as they keep changing
the rules in this realm (that's usually a good thing), which makes it all
the more fun :)

HTH, Elvis


Celebrating 11-Years of SQL Performance Excellence on IBM i5/OS and OS/400
www.centerfieldtechnology.com

-----Original Message-----
Subject: Re: Index advisor advising index on primary key

Jeff,

We have a similar situation. System value, and suggested index, are set
to *HEX. Now, how do I pin down which user or query, or whatever is
causing this recommendation? When using Index Advisor I ask it to show
statements and I get nothing.

Rob Berendt