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)
򐂰 Partitions (cardinality)
򐂰 Indexes (cardinalities, clusterratiof, # leaf pages, # levels)
These statistics are gathered by the basic RUNSTATS syntax:
RUNSTATS TABLESPACE
RUNSTATS TABLESPACE TABLE
RUNSTATS INDEX
Tablespace
Partition
Partition
Table
Key target
Key target
Index
Index
Column
Card Range
Column
Card Range
Multi-
column
Card
Multi-
column
Card
Frequency
Frequency
BASE
yyy
yyy
9
?
?
open-ended?
closed set
COLUMNS
Histograms
Histograms
Frequency
Frequency
Histograms
Histograms

Get IBM DB2 9 for z/OS: New Tools for Query Optimization now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.