DB2 10.5 for Linux, UNIX, and Windows

Enabling automatic statistics collection

Having accurate and complete database statistics is critical to efficient data access and optimal workload performance. Use the automatic statistics collection feature of the automated table maintenance functionality to update and maintain relevant database statistics.

About this task

You can enhance this functionality in environments where a single database partition operates on a single processor by collecting query data and generating statistics profiles that help the DB2® server to automatically collect the exact set of statistics that is required by your workload. This option is not available in partitioned database environments, certain federated database environments, or environments in which intrapartition parallelism is enabled.

To enable automatic statistics collection, you must first configure your database by setting the auto_maint and the auto_tbl_maint database configuration parameters to ON.

Procedure

After setting the auto_maint and the auto_tbl_maint database configuration parameters to ON, you have the following options:

To enable background statistics collection, set the auto_runstats database configuration parameter to ON.
To enable background statistics collection for statistical views, set both the auto_stats_views and auto_runstats database configuration parameters to ON.
To enable background statistics collection to use sampling automatically for large tables and statistical views, also set the auto_sampling database configuration parameter to ON. Use this setting in addition to auto_runstats (tables only) or to auto_runstats and auto_stats_views (tables and statistical views).
To enable real-time statistics collection, set both auto_stmt_stats and auto_runstats database configuration parameters to ON.