Using runstats

The optimizer uses the catalog tables from a database to obtain information about the database, the amount of data in it, and other characteristics, and uses this information to choose the best way to access the data. If current statistics are not available, the optimizer may choose an inefficient access plan based on inaccurate default statistics.

It is highly recommended that you use the runstats command to collect current statistics on tables and indexes, especially if significant update activity has occurred or new indexes have been created since the last time the runstats command was executed. This provides the optimizer with the most accurate information with which to determine the best access plan.

Be sure to use runstats after making your table updates; otherwise, the table may appear to the optimizer to be empty. This problem is evident if cardinality on the Operator Details window equals zero. In this case, complete your table updates, rerun the runstats command and recreate the explain snapshots for affected tables.

Note:

• Use runstats on all tables and indexes that might be accessed by a query.

• The quantile and frequent value statistics determine when data is unevenly distributed. To update these values, use runstats on a table with the WITH DISTRIBUTION clause.

• In addition to statistics, other factors (such as the ordering of qualifying rows, table size, and buffer pool size) may influence how an access plan is selected.

• Applications should be rebound (and their statements optionally re-explained) after you run the runstats command or change configuration parameters.

The runstats command (which can be entered from the DB2 CLP prompt) can provide different levels of statistics as shown in the following syntax:

Basic Statistics

Table:

RUNSTATS ON TABLE tablename

Index:

RUNSTATS ON TABLE tablename FOR INDEXES ALL

Both tables and indexes:

RUNSTATS ON TABLE tablename AND INDEXES ALL

Enhanced Statistics

Table:

RUNSTATS ON TABLE tablename WITH DISTRIBUTION

Index:

RUNSTATS ON TABLE tablename FOR DETAILED INDEXES ALL

Both tables and indexes:

RUNSTATS ON TABLE tablename WITH DISTRIBUTION AND DETAILED INDEXES ALL

For more information on the runstats command, see the Administration Guide