Perform database maintenance

For best performance, perform periodic maintenance activity on large tables by running a command such as RUNSTATS.

The DB2® RUNSTATS command updates statistics in the system catalog about the characteristics of a table and/or associated indexes, or statistical views. It is highly recommended that you use the DB2® 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 run. This command provides the optimizer with the most accurate information with which to determine the best access plan.


runstats on table DB2INST2.UA_CONTACTHISTORY and detailed indexes all

Consider the case of a table that could have a large variation in the amount of data it contains, at any given moment. The volatility or extreme changeability of this type of table makes reliance on the statistics collected by RUNSTATS inaccurate. Statistics are gathered at, and only reflect, a single point in time.

To generate an access plan that uses a volatile table can result in an incorrect or poorly performing plan. For example, if statistics are gathered when the volatile table is empty, the optimizer tends to favor accessing the volatile table using a table scan rather than an index scan.

To prevent this type of issue, consider declaring the table as volatile using the ALTER TABLE statement. By declaring the table volatile, the optimizer will consider using an index scan rather than a table scan. Access plans that use declared volatile tables do not depend on the existing statistics for that table.