Article written by Steen Rasmussen – Steen.Rasmussen@broadcom.comRecently we have seen several questions related to PDASTATS and updating the Db2 Catalog, so hopefully this article will remove any confusion.Historical background
Let's take a step back and look at the history in order to get all the pieces in the puzzle together.Appropriate and accurate catalog statistics are vital in order for the Db2 Optimizer to select the optimal access path. Runstats is the IBM utility to collect these statistics and update the catalog, but there were serious issues. The only way to identify which objects had a bad clusterratio, leafdist, relocated rows etc. was to execute Runstats. The issue was - executing Runstats prior to a reorg in order to identify reorg candidates updated the catalog, meaning subsequent BIND, REBIND or Dynamic SQL executions could choose a different (worse) access path. Later versions of Runstats offered the ability to execute Runstats using REPORT ONLY so the catalog didn't get updated with non-optimal statistics for the Optimizer. Starting with Db2 V7, RTS became an option to identify reorg candidates and is now recommended best practice as the "trigger" to identify reorg candidates.
This was one of the reasons why PDASTATS was invented - having PDA doing very detailed analysis of the pagesets and inserting these statistics into the associated PDA-tables via Extract Procedures so the associated Action Procedures could generate the needed reorg's etc.
Runstats was also quite expensive to execute in the early days of Db2, so many customers also used PDA to collect new statistics after the reorg and used PDA to update the catalog. This both saved CPU and eliminated the Runstats issue of getting "bad statistics" into the catalog which potentially could impact the Optimizer's access path selection in a negative fashion.
For years CA recommended completely replacing IBM Runstats with PDSTATS, but this really changed when Db2 10 became available, so let's cover the details:
DISABLE_DB2_CAT_UPDATE (PDA,RC4) /* Stop updating the DB2 cat- */ /* alog as that feature has */ /* been deprecated over time. */ /* IBM RUNSTATS generates */ /* additional statistics that */ /* the optimizer can utilize. */ /* Allows for site level con- */ /* trol to disable and opt- */ /* ionally set a step RC. */ /* Can be used to identify */ /* job steps that need to be */ /* reviewed and/or updated. */ /* Keyword values: */ /* (NO) = do not disable */ /* or format (<funct>,<action>)*/ /* (fct,MSG) = display message*/ /* (fct,RC4) = step RC 4 */ /* (fct,RC10)= step RC 10 */ /* fct can be any of these 3:*/ /* PDA means PDA Extracts, */ /* PS Utility Code, */ /* UC Utility Code, or */ /* PDASTATS utility */ /* CAU means CA Utility */ /* (reorg or load) */ /* ALL means PDA or CAU */ /* listed functions */ /* (PDA,RC4) is the DEFAULT */
The PDASTATS component is not the biggest selling point of Database Analyzer. PDA's strength using either PDASTATS or RTS to automate REORG generation or any other Db2 housekeeping processes is the real power. The models are highly customizable with extensive IF-ELSE capabilities. Also, once you get a good grip of the capabilities within Action Procedures, you can automate generating almost anything, but this is a topic for another DIFU where I will cover some scenarios like generating a RC/Migrator strategy for every database in a Db2 subsystem among other use cases.
Steen Rasmussen (Services Architect) : Steen.Rasmussen@broadcom.com
Jan Marek (Product Manager) : Jan.Marek@broadcom.com
Himanshu Gupta (Lead Developer) : email@example.com