iSeries Navigator index advisor...

[bryce4president]

Ok, I tossed around the idea of where to ask this and decided to put it here...

I'm trying to run an SQL statement through iSeries Navigator. When I do the Explain it says that Creation of an Index Advised is No. but if I hit the toolbar button for Index Advisor it tells me to create an index. It was creating a temporary index over one of the files in my sql statement and tells me to create the index over that file, only if I open the Index Advisor, not in the panel to the right. Over on the right it says not to create an index. So that is the first confusing part.

The next part that is confusing is that I do create the index, but the sql engine still isn't using it and it still tells me the exact same thing as before. I'm a bit confused... Why wouldn't it use it after it told me to create it? Why would it tell me to create it in one spot but not another?

I'm just trying to speed up my sql statement SHeesh!

[tomholden]

Re: iSeries Navigator index advisor...

The next part that is confusing is that I do create the index, but the sql engine still isn't using it and it still tells me the exact same thing as before. I'm a bit confused... Why wouldn't it use it after it told me to create it? Why would it tell me to create it in one spot but not another?

welcome to index advisor! this happens more often than not that the index advisor will say build xyz index, you build the index and reanalyze the same SQL statement and the index advisor refuses to use the index (not sure if it's a bug or what but it sure is annoying!) to make a long story short, use the index advisor, create it's best guess index then use common sense and create an index that you think should be used based on your query. then you have both options covered (actually most of the time the ones created without using the index advisor's recommendation will be used, go figure)...

[bryce4president]

Re: iSeries Navigator index advisor...

when you look at the right panel in the Explain window, there is a spot that shows all the indexes that were optimized. This shows all logicals and indexes over the file that the optimizer checked, and next to each of them there is a number. two of them have a 6, another one has a 4, and another one has a 0. Any idea what those numbers mean? Any idea if 6 is better than 4 is better than 0 or the other way around?

[tomholden]

Re: iSeries Navigator index advisor...

not sure on that one...maybe Birgitta will chime in. i've gotten to the point that i usually run the SQL statement interactively on the system and if the status message shows creating access path from xyz index i'll take a look at that index and see how it relates to my SQL statement and build an index on what i *think* the optimizer is looking for. i've used the index advisor but it's still kinda kulnky to me even after all these years. Navigator in and of itself sucked when it came out...it's improved but not as much as it should have IMNSHO.

[bryce4president]

Re: iSeries Navigator index advisor...

maybe there is a way that I can optimize the query itself... here is the query I'm trying to run....let me know if there is anything I can do to speed it up...

SELECT sord, sopno, sddte, sprod, iclas, icsbucde from bpcsffg/fso 
JOIN bpcsffg/iim ON sprod = iprod 
JOIN bpcsusrffg/pns101pf ON iclas = icclass 
WHERE sid='SO' and sofac='MU' and iclas IN('DI','DE','DR')

any thoughts?

[bryce4president]

Re: iSeries Navigator index advisor...

I always use STRSQL to run sql statement interactively, how do I see the details you are talking about? there wasn't anything in the job log that I could see...

[tomholden]

Re: iSeries Navigator index advisor...

I always use STRSQL to run sql statement interactively, how do I see the details you are talking about? there wasn't anything in the job log that I could see...

it's on the status messages on the message line...not in the job log.

[tomholden]

Re: iSeries Navigator index advisor...

not sure it would run faster but i prefer something like this:

SELECT a.sord, a.sopno, a.sddte, a.sprod, a.iclas, a.icsbucde from bpcsffg/fso a, 
 bpcsffg/iim b,bpcsusrffg/pns101pf c  
WHERE  a.sprod = b.iprod and a.iclas = c.icclass and
a.sid='SO' and a.sofac='MU' and a.iclas IN('DI','DE','DR')

also an index on fso keyed by sprod, iclas (or vice versa) might help out some.

[bryce4president]

Re: iSeries Navigator index advisor...

Well I have the proper indexes for FSO. The problem is with PNS101PF. It keeps creating a temporary over that.

So what you are doing is letting the optimizer decide how to join the files?

[bryce4president]

Re: iSeries Navigator index advisor...

My solution ran in 141ms yours ran in 156ms.

Is there any way to know what the libl looks like that its using? Or will it use my standard libl?

[tomholden]

Re: iSeries Navigator index advisor...

Well I have the proper indexes for FSO. The problem is with PNS101PF. It keeps creating a temporary over that.

So what you are doing is letting the optimizer decide how to join the files?

then you need to create an index over that table that will be potentially usable by the optimizer (like keyed by the icclass field). your library list will be used by default for finding the tables used in the query, the optimizer will check all indexes regardless of library list for a usable access path.

[Mercury]

Re: iSeries Navigator index advisor...

The next part that is confusing is that I do create the index, but the sql engine still isn't using it and it still tells me the exact same thing as before. I'm a bit confused... Why wouldn't it use it after it told me to create it? Why would it tell me to create it in one spot but not another?

When I started using Index Advisor I was confused too.

The Index Advisor keeps showing the advised indexes until all the panel is cleared by someone. This is why you'll still see the advised index even after creating it. We do clear the Index Advisor panel each week before the next IPL.

This following link can help http://www.mcpressonline.com/tips-techniques/database/techtip-cut-to-the-chase-with-db2-index-advisor-ptfs.html

[bryce4president]

Re: iSeries Navigator index advisor...

All I got was an about:blank page Mercury...

[Mercury]

Re: iSeries Navigator index advisor...

You must be registered (no charge) on mcpressonline to read the article. First go to

http://www.mcpressonline.com/

and register using the upper right corner on the displayed page then go to

http://www.mcpressonline.com/tips-techniques/database/techtip-cut-to-the-chase-with-db2-index-advisor-ptfs.html

[arrow483]

Re: iSeries Navigator index advisor...

Just something to keep in mind: The optimizer will use the method it thinks is best. It will guess (using EVI index, binary index, statisics or data sample) at what % of records will be returned from a file. If there is more than a certain % (I'm thinking 50% - not sure) then it will use a table scan instead of an index. Table scans are much faster than indexed read when a big % of records get returned. Also, sometimes it uses an index to determine that a table scan is the best processing (so the index is used, but not used!).
Note, too, that interactive SQL if different than batch. Interactive by default tries to get the first records ASAP (since you only display a few records at a time), whereas in batch it tries to get the fastest time with all selected records. This means different methods get used. Memory is also part of the optimization, and different amounts of memory are available interactive vs batch.
FWIW, if you are running in 156ms, thats sub-second, I think I would be happy with that. How much time are you going to save for the amount of time trying to speed it up ?
Its not the same on all systems, but Index Advisor sometimes runs in a different subsystem than either batch or interactive, and may have different results.