I've been around Db2 since the mid-80s, but brand new to Db2 on the i. So forgive me if this is a foolish question.
I've been reading a lot about performance on the i (Kent Milligan, Mike Cain, Toronto Users Group, Redbooks).
However, I'm a bit confused from what I'm seeing when using Index Advisor. It is returning some recommendations that have high values in "Times Advised for Query Use" and then 0 for MTI Used. One I see has been Advised for Query Use over 1 billion times (with a B) and MTI Used is 0 - the Last Advised for Query Use timestamp was 3 hours ago from my typing this. Perhaps I'm missing the true intent of these columns...
I'm assuming an MTI was created for the columns that the output lists in the "Keys Advised" column (my assumption could very well be wrong). Why is it not used? Does it create this MTI and then the optimizer looks at the statistics for it and figure it won't be beneficial?
And since I'm on the subject of MTIs - does it hurt to have all these MTIs around? Should I drop those that are more than 6 months old or so or just used once or twice? When DML is run against a table, are all of these MTIs updated even if they've not been used in months or years? I've seen one table with over 2000 MTIs - some of the recommendations are from 2014 (and this confuses me - I thought they disappeared after an IPL - certainly this machine has been rebooted since then - or perhaps the MTI is gone, but the recommendation is still in the system table). Most of the number of advised times are under 10 times total. Only 25 of these 2000+ indexes have an MTI Used count of more than 10. On the z and LUW, they say having bunches of indexes effects DML/Load performance and if they aren't being used enough, drop them to improve overall performance. 2000 would be considered a bit excessive (to put it lightly). ****** Editted - looks like MTI Created with a non-zero means the MTI was created - is that correct? If so, I show 57 with non-zero entries - that's still a lot, but nothing like 2000+.
Hope my questions are clear - thanks for any help or direction. Any recent good books/sites you can point me to?