DB2 UDB V8.2 on the Windows Environment

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:

򐂰 TABLESAMPLE BERNOULLI

򐂰 TABLESAMPLE SYSTEM

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

database activity.

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

command.