Much like Larry's recent issue this one was given the standard answer from
IBM to build the indexes suggested by the index advisor.
When pressured to explain why this ran for months, years, without an issue
I was given
<snip>
There are many reasons query runtime can change if proper indexes aren't
in place. As data is constantly changing, the optimizer tries to do/change
to the best implementation using the (limited) information it has. Without
indexes, you can get different optimization paths, so it's not guaranteed
it will always pick the best one. It's also possible that the OS had built
temporary indexes for these queries before, too (they can get wiped out
during IPLs, PTF applies, etc.)
You can take a look at this tech note which has a few more comprehensive
reasons for changes in query implementation/runtimes:
Why did my query performance fluctuate?
http://www-01.ibm.com/support/docview.wss?uid=nas8N1021655
</snip>
The possibility of a PTF issue is given minor lip service.
<snip>
Consider collecting Plan Cache Snapshots on a regular basis or before
changes occur so that these Snapshots can be used for comparison purposes.
</snip>
Then it becomes: Do I capture these on a periodic basis and retain for x
days? When do I run one? How long do I run one?
One might assume "when your load is the busiest" or some such thing but
when it's a 2:30 in the morning batch run that jumps hours and has a
negative effect on stuff (like job queues) it's a whole different ball
game.
Run it for 24 hours, once a week, alternating between each of the 5
business days. Keeping 10 weeks so that you have a minimum of 2 runs per
each weekday? Do also a DSPPTF or a snapshot of QSYS2.PTF_INFO?
Rob Berendt
midrange.com
