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:
v Statistic 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.
v Only 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.
v Only 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.
v The 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.
v While 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
Chapter 6. Automatic maintenance 37
