224 DB2 9 for z/OS: New Tools for Query Optimization
9.1 Access Path statistics introduction
This section summarizes:
The various types of statistics used for access path selection. See DB2 Version 9.1 for
z/OS Performance Monitoring and Tuning Guide, SC18-9851.
The RUNSTATS syntax and overhead to collect each type. See DB2 Version 9.1 for z/OS
Utility Guide and Reference, SC18-9855.
The types of SQL that benefit from these statistics.
Figure 9-1 shows the statistic types.
Figure 9-1 Type of statistics
DB2 can also keep a history of each of these statistics in a separate set of tables, but they are
not used by the optimizer.
9.1.1 BASE Statistics
These statistics provide basic information about the tables and indexes used in the SQL:
Tables (cardinality, that is # rows, # pages, compression percentage)
Table spaces (# active pages)
Indexes (cardinalities, clusterratiof, # leaf pages, # levels)
These statistics are gathered by the basic RUNSTATS syntax:
RUNSTATS TABLESPACE TABLE