Db2 Tools

 View Only
Back to discussions
Expand all | Collapse all

What does PDA statistics really do? How it is different from RUNSTATS?

  • 1.  What does PDA statistics really do? How it is different from RUNSTATS?

    Broadcom Employee
    Posted Dec 20, 2019 09:11 AM
    Edited by Himanshu Gupta Feb 05, 2020 05:45 AM

    These are some questions which DB2 DBA's have in their life who are using Database Analyzer for DB2 for Z/OS.

    Customers currently run PDA Statistics periodically and run RUNSTATS regularly this has become their schedule for years.

    Why PDASTATS are collected?

    1. Dependency on PDA Functionalities like PDA relies on the statistics that it gathers in order to carry out its other functions which include responding to situations that require a utility to be run to correct a problem while RUNSTATS are collected for the DB2 Optimizer.
    2. To allow PDA with correct JCL allocations for work datasets.
    3. To make you generate right PRIQTY to have your data in one extent.
    4. Update the DB2 catalog instead ONLY when you need to without risking BAD OPTIMIZER statistics and changed access path as a result. You can schedule the CATALOG to ONLY be updated IF certain values have changed.
    5. PDA Statistics also serve as a Reporting Tool. You can generate trend analysis and forecasting to see when certain thresholds will be met.
    6. Use PDA Statistics as a Pro-Active tool - look at the objects being triggered for Reorg or Alter or some other admin functions and see the trend. If the SAME object is meeting your thresholds every second week, it could be an idea to ALTER one of the parameters for the table-space /Index to avoid some of the Reorgs or to postpone the 55 extent situation.
    7. Use the Reporting to analyze WHERE in the table-space/Index you are having the problem with REALLOCATED rows, HOLES, Many Updates, (HOT SPOT DETECTION). Which KEY ranges are facing the problem etc.


    As a DBA you need to know about the health of your DB2 objects. However, does DB2 itself always need to know that your tables/indexes are getting disorganized?

    PDA allows you to collect stats that only YOU can see and it will not impact DB2 Optimizer. Since IBM changes its DB2 Optimizer algorithm. We don't want customer to update DB2 catalog with PDASTATS as it may sometime adversely impact DB2 performance.

    CA-Database Analyzer (PDA) catalog update – You can update catalog not related to DB2 Optimizer these statistics (non-optimizer a.k.a. space statistics) are 'primarily' used for object maintenance. All of these statistics are indeed collected by PDA and stored our history tables. These can easily be used to set thresholds/triggers to generate JCL which will perform object maintenance.