2011-01: APARS for RTS, RUNSTATS, and SQL PERFORMANCE for DB2 z/OS – Which are needed and why?

 

The Programmers‘ 1st Law of Bugdom; all non-trivial programs have bugs –  ergo   DB2 has bugs!

Of course, software being software means “keeping an eye” on the “APARs of interest”.  This is hard work because you must search the IBM technical problem database – lots of people do not like doing this, have no time to do this, or do not know how to do this!  At the end of this newsletter, I’ve attached lists of important APARs to save you the work.

 

First up: Real-Time Statistics (RTS):

Real-time statistics came in as an optional extra in DB2 V7.  They are now firmly embedded in the architecture of DB2 to the point of being used for space allocation, and even the Optimizer is starting to use this data. In this case, it would be very sensible to make sure that the data is as accurate as possible.  To do this, any bugs must be found and removed from the code.
Of course, you are already using RTS to drive your major DB2 utilities, aren’t you?  I hope so!  This makes 90% of all RUNSTATS irrelevant and stops 95% of REBINDs, which stops 99% of bad access paths in production from ever occurring.

 

Next up: RUNSTATS:

As you all know, the DB2 Optimizer makes its cost-based decisions on several columns in the DB2 catalog, and RUNSTATS updates some of these as well – just as RTS has bugs, so does RUNSTATS.  When you have bad catalog data, access paths and performance can suffer. SEGUS has a licensed FREEWARE product called “Statistics HealthCheck”, which checks the DB2 catalog for internal consistency and plausibility.  There is also a fully licensed version that will actually generate “corrective” RUNSTATS.  But, of course, if the bad catalog data comes from a bug in RUNSTATS, a new run of RUNSTATS will not help you and you will still suffer from bad performance.

 

Last up: SQLPERFORMANCE:

This is a keyword available in the IBM technical problem database search that I use because the Optimizer also has bugs.  (Of course, it also has more features than you can shake a stick at).  In other words, “it has lots of switches and buttons and dials” that can be tweaked, pushed, prodded, and flipped.  All of these have a direct effect on performance, so when Terry Purcell et al decides to change the composite filter factor for negative square-roots, it is nice if you can see this and apply the new feature where and when needed.

As an “aide de camp”, I maintain a most current APAR lists.  I track these and update my lists every month (I only keep about two years’ worth, because that old enough). Please contact us if you wish to receive this list. As soon as it becomes available, you will get an updated list containing the latest APARs for RTS, RUNSTATS and SQLPERFORMANCE.

 

Feel free to send me your comments and ask questions.

TTFN,
Roy Boxwell
Senior Architect