enabled, DB2 will automatically run the RUNSTATS utility in the background to ensure the correct statistics are collected and maintained.
Starting in DB2 Version 9, automatic statistics collection is enabled by default when a new database is created.
The performance impact of automatic statistics collection is minimized in several ways:
vStatistic collection is performed using throttled RUNSTATS. Throttling controls the amount of resources consumed by the RUNSTATS utility based on current database activity: as database activity increases, the RUNSTATS utility runs more slowly, reducing its resource demands.
vOnly the minimal set of statistics for optimizing performance are collected. This is achieved through the use of statistics profiling which uses information about previous database activity to determine which statistics are required by the database workload, and how quickly those statistics will become out of date given the type of activity in the database.
vOnly tables with high levels of activity (measured through the number of updates, deletes and inserts) are considered for statistic collection. Large tables (consisting of more than 4000 pages) are also sampled to determine whether the high table activity has indeed changed the statistics. Statistics for these large tables are only collected if warranted.
vThe RUNSTATS utility is automatically scheduled to execute during the optimal maintenance window specified in your maintenance policy definition. This policy also specifies the set of tables that are within the scope of the automatic statistics collection, further minimizing unnecessary resource consumption.
vWhile automated statistic collection is being performed, the affected tables are still available for regular database activity (updates, inserts, deletes)as if RUNSTATS were not running on the table.
Related concepts:
v“Automatic features enabled by default” in Administration Guide: Planning
v“Collecting statistics using a statistics profile” in Performance Guide
Related tasks:
v“Using automatic statistics collection” in Performance Guide
Related reference:
v“RUNSTATS command using the ADMIN_CMD procedure” in Administrative SQL Routines and Views
v“auto_maint - Automatic maintenance configuration parameter” in Performance Guide
v“util_impact_lim - Instance impact policy configuration parameter” in Performance Guide
v“RUNSTATS command” in Command Reference
