DB2 Catalog and Performance Trace Mismatch

(Originally posted 2005-12-01.)

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 SYSIBM.SYSPACKSTMT/PLAN_TABLE don’t always tie up. SYSPACKSTMT and PLAN_TABLE agree with each other. It’s the Performance Trace that’s anomalous.

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.

Published by Martin Packer

I'm a mainframe performance guy and have been for the past 35 years. But I play with lots of other technologies as well.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: