© Copyright IBM Corp. 2007. All rights reserved. 223
Chapter 9. Statistics Advisor
The DB2 optimizer seeks out the least costly way of executing SQL. Its cost formulas are
dependent on statistics gathered by the RUNSTATS utility (because DB2 does not sample the
data directly). It is the installation’s task to execute RUNSTATS in a timely and complete
fashion and to provide the optimizer with the best information about the characteristics of the
data to be accessed.
About half the access path issues reported to IBM DB2 Support are the result of incorrect
statistics that do not match the data or statistics that had not been gathered at all. “Garbage
in, garbage out”; in other words, this results in pervasive DB2 performance degradations that
execute sub-optimal access paths optimized to the wrong set of statistics.
The problem of Access Path Statistics is a mix of practical and theoretical concerns. There
are several types of statistics available, with many possible permutations. Furthermore,
executing the required RUNSTATS within the utility windows can itself become an issue
because RUNSTATS variations are costly to gather.
The Statistics Advisor provides a targeted solution, recommending statistics needed to
optimize the SQL submitted for its analysis. It formats RUNSTATS statements, ready to run.
The Statistics Advisor tool was introduced in Visual Explain (“Analyze”).
Optimization Service Center includes the Statistics Advisor and extends its scope to
encompass entire workloads (collections of SQL statements, culled from various sources),
not only individual SQL statements. It also takes advantage of the increased usability
provided by the Project framework of the Optimization Expert for z/OS.
9

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.