Update Statistics (RUNSTATS) 

From the DBA Planning Calendar you can update the statistical information for one or all SAP tablespaces or for those tablespaces for which a RUNSTATS is recommended. See also section RUNSTATS needed.

Special Considerations for LOB Tablespaces

Many RUNSTATS options are not valid for LOB tablespaces. More precisely, the RUNSTATS jobs for these tablespaces only specify the tablespace name and SHRLEVEL(CHANGE). Other options are ignored when specified. It is possible that running RUNSTATS on a tablespace that owns one or more auxiliary LOB tablespaces will also trigger RUNSTATS on the auxiliary tablespaces.

Update Statistics for One SAP Object

There are several tablespaces, whose statistics should not be updated to make the DB2 optimizer favor an index-based access (see also section Maintaining DB2 Catalog Statistics). These are excluded automatically from any update for all SAP tables and recommended SAP tables.

If you have a special reason to update statistics for a certain tablespace that falls into the above category, you have to schedule an Update statistics for one SAP object and specify the table or tablespace. A dialog box appears prompting you for confirmation.

Profile Parameters for RUNSTATS

The following profile parameters exist for RUNSTATS. You maintain these with the JES Interface (transaction DB2J) function Profile parameters:

RUNSTATS

·        Number of parallel jobs

You can choose the number of jobs to be executed in parallel, speeding up the process.

·        Low/medium/high percentage of rows sampled (under RUNSTATS)

You can choose between three levels of accuracy (low, medium, high). These differ in the percentage of lines examined in the table(s). You can set the default level of accuracy and the corresponding percentages.

·        Age for MODIFY STATISTICS (under RUNSTATS)

DB2 V7 RUNSTATS includes a new option, HISTORY. If specified, every time the corresponding catalog tables are inserted or updated, some selected fields are copied to some new catalog history tables. This results in preserving the relevant statistics data across multiple RUNSTATS executions and creates a basis for monitoring the database objects growth and assistance in determining optimal allocation quantities. Additionally, the historical data could be used for reinstating old catalog statistics in case of access path degradation.

There is a utility named MODIFY STATISTICS to clean up the statistics history catalog tables that can grow considerably large, if you run RUNSTATS as frequently as recommended. MODIFY STATISTICS has a parameter AGE that can be maintained here. AGE can be set from 0 to 32767 days. The utility deletes all records older than the specified number of days. If you specify AGE=0, MODIFY STATISTICS will not be used.

Since SAP does not yet use the HISTORY option of RUNSTATS, the recommended value is 0. If you have updated the RUNSTATS utility statment to use the HISTORY option, then the recommendation depends on your usage of the data:

¡        If you just want to use the historical data for reinstating old catalog statistics in case of access path degradation, a value of 30 will be sufficient.

¡        If you want to monitor database objects growth, a value of 365 to 730 days is more appropriate.

General

·        Plan of program DSNTIAD(General parameters section).

For more information about the RUNSTATSutility, see Database Management, section RUNSTATS in the SAP DBA Guide for DB2.