DB2 Catalog and Performance Trace Mismatch
MartinPacker 11000094DH Visits (3735)
If you ask Performance Trace nicely enough it'll tell you what statement in a package is consuming all the CPU.
Over the past couple of years we've been working on code to analyse the DB2 Catalog and PLAN_TABLE tables to do SQL tuning. It's been an interesting journey - and, I guess, a never-ending one.
Each client provides fresh insight and challenges. Our current Chinese one is just such a client: The statement numbers in Performance Trace and SYSI
It turns out that the static data and the Performance Trace were collected some weeks apart. In a busy installation code gets edited, tested, recompiled all the time. Particularly if the code is underperforming. I think we got bitten by that.
So in the future we'll be surer to try and get fresh Catalog/PLAN_TABLE data when we take a trace - which probably means a respin as we generally home in on a particular package to trace after the main DB2 (and z/OS) study is done. Unfortunate but necessary.