172 DB2 UDB V8.2 on the Windows Environment
statistics. Random sampling of table data on which the statistics are to be
collected can reduce the amount of time that it takes to collect statistics. For
I/O-bound or CPU-bound systems, the performance benefits can be enormous.
The smaller the sample, the faster statistics collection completes.
Starting in Version 8.2, the RUNSTATS command provides the option to collect
statistics on a sample of the data in the table by using the TABLESAMPLE
option. This feature can increase the efficiency of statistics collection because
sampling uses only a subset of the data. At the same time, the sampling methods
ensure a high level of accuracy.
You can specify the table sampling methods Bernoulli or System the same way
you do in SQL queries (5.1.3, “Data sampling in SQL queries” on page 162). The
new parameters are:
These two options can be specified by the REPEATABLE parameter if there is a
need for exactly the same results. Example 5-4 shows the RUNTSTATS
command using new options.
Example 5-4 Runstats using the data sampling method with repeatable results
RUNSTATS ON TABLE db2user.employee WITH DISTRIBUTION
TABLESAMPLE BERNOULLI(30) REPEATABLE(4196)
5.3.2 Throttling of the RUNSTATS utility
Executing resource-intensive utilities like RUNSTATS can negatively impact
overall database performance. However, in order to maintain efficient database
operation, statistics must be collected regularly, leaving database administrators
with the task of identifying periods of time when the impact of utility execution is
most tolerated. In many environments, there are no regular windows of reduced
Throttling of the RUNSTATS utility limits the amount of resources consumed by
the utility, based on the current level of database activity. When database activity
is low, the utility runs more aggressively; when database activity increases, the
resources allocated to executing RUNSTATS are reduced.
You can now specify the RUNSTATS command with the priority parameter
UTIL_IMPACT_PRIORITY. You can specify values from 1 to 100. A value of 100
means no throttling of the RUNSTATS command and has the same effect as not
specifying the UTIL_IMPACT_PRIORITY, because the default is not throttling the