After migrating to Db2 9 why is the CLUSTERRATIO value collected by Database Analyzer for Db2 for z/OS (PDA) different from IBM RUNSTATS

Products

• Database Analyzer for DB2 for z/OS

Issue/Introduction

After migrating to Db2 9, the value for CLUSTERRATIO calculated by Database Analyzer for Db2 for z/OS (PDA) compared to that calculated by
IBM RUNSTATS is different. In some isolated cases this has led Db2 to select a different access path.

Cause

With Db2 9 for z/OS when the DSNZPARM STATCLUS is set to ENHANCED, Db2 will use a different cluster ratio formula and uses the new statistic
column DATAREPEATFACTOR. IBM indicates this cluster ratio and new column may be used for indexes that contain many duplicate key values or
key values that are highly clustered in reverse order.

When using PDA to update the Db2 Catalog, PDA will set DATAREPEATFACTOR to -1. This will cause Db2 to use the same cluster ratio formula used in prior Db2 releases.

Resolution

The recommendation is to use a combination of IBM RUNSTATS and PDA Extract Procedures. It is recommended to use IBM RUNSTATS to update the DB2 catalog for
improved optimization statistics and access path selection. IBM RUNSTATS includes additional table and column specific statistics keywords like HISTOGRAM and COLGROUP.

PDA Extract Procedures can still be used to collect statistics for historical or trending reasons such as space utilization or other data disorganization indictors.
The Real Time Object Selection (RTOS) Extract feature can select objects automatically via the IBM RTS statistics to find objects that should have RUNSTATS run on them.
The Database Analyzer Action Procedure RS (RUNSTATS) Utility Code can be used to automatically generate the RUNSTATS JCL.