Command Reference

RUNSTATS

Updates statistics about the physical characteristics of a table and the associated indexes. These characteristics include number of records, number of pages, and average record length. The optimizer uses these statistics when determining access paths to the data.

This utility should be called when a table has had many updates, or after reorganizing a table.

Scope

This command can be issued from any node in the db2nodes.cfg file. It can be used to update the catalogs on the catalog node.

The command collects statistics for a table on the node from which it is invoked. If the table does not exist on that node, the first node in the nodegroup is selected.

Authorization

One of the following:

sysadm

sysctrl

sysmaint

dbadm

CONTROL privilege on the table.

Required Connection

Database

Command Syntax

>>-RUNSTATS ON TABLE--table-name--------------------------------> >--+-+-------------------------------------------------------------------+-+> | +-WITH DISTRIBUTION--+-------------------------------------------+--+ | | +-AND-+----------+---+-INDEXES ALL--------+-+ | | +-DETAILED-+ +-INDEX--index-name--+ | +-+-----------------------------------------------+---------------------+ +--+-AND-++----------+---+-INDEXES ALL--------+-+ +-FOR-++-DETAILED-+ +-INDEX--index-name--+ >--+-------------------------+--------------------------------->< | +-CHANGE----+ | +-SHRLEVEL--+-REFERENCE-+-+

Command Parameters

TABLE table-name

The table on which to update statistics. The fully qualified name or alias in the form: schema.table-name must be used. The schema is the user name under which the table was created. If no options are specified, only table statistics will be updated. Other users will have access to the table while the statistics are being gathered.

WITH DISTRIBUTION

Specifies that distribution statistics are requested. The number of most frequent values collected is defined by the num_freqvalues database configuration parameter. The number of quantiles collected is defined by the num_quantiles database configuration parameter. For information about nonuniform distribution statistics, see the Administration Guide.

AND INDEXES ALL

Update statistics on both the table and its indexes.

AND INDEX index-name

Update statistics on both the table and the specified index, where index-name is a fully qualified name in the form: schema.index-name.

FOR INDEXES ALL

Update statistics on the indexes only. If statistics on the table have never been generated, the database manager calculates statistics on the table as well as on the indexes.

FOR INDEX index-name

Update statistics on the specified index only. If table statistics have never been generated, the database manager calculates statistics on the table as well as on the index. The index-name is a fully qualified name in the form: schema.index-name.

DETAILED

Calculate extended index statistics.

SHRLEVEL

CHANGE

Specifies that other users can read from and write to the table while statistics are calculated.

REFERENCE

Specifies that other users can have read-only access to the table while statistics are calculated.

Examples

Collect statistics on table only, without distribution statistics:

   db2 runstats on table smith.table1

Collect statistics on table only, with distribution statistics:

   db2 runstats on table smith.table1 with distribution

Collect basic statistics on indexes only:

   db2 runstats on table smith.table1 for indexes all

Collect statistics on table and all indexes (basic level):

   db2 runstats on table smith.table1 and indexes all

Collect statistics on table, with distribution statistics and index statistics:

   db2 runstats on table smith.table1 with distribution and indexes all

Collect all possible statistics (distribution and extended index):

   db2 runstats on table smith.table1 with distribution and detailed index

Collect distribution statistics on index INDEX1 only:

   db2 runstats on table smith.table1 with distribution for index smith.index1

Usage Notes

Use RUNSTATS to update the statistics on tables:

• That have been modified many times (for example, if a large number of updates have been made, or if a significant amount of data has been inserted or deleted)

• That have been reorganized.

After statistics have been updated, new access paths to the table can be created by rebinding the packages using BIND.

Statistics for tables only should be collected before any indexes are created. This will ensure that statistics gathered during index creation are not overlaid by estimates gathered during the calculation of table statistics.

Statistics are collected based on the table partition that is resident on the node where the command executes. Global table statistics are derived by multiplying the values obtained at a node by the number of nodes on which the table is completely stored. The global statistics are stored in the catalog tables.

The node from which the command is issued does not have to contain a partition for the table:

• If the command is issued from a node that contains a partition for the table, the utility executes at this node.

• If the command is issued from a node that does not contain a table partition, the request is sent to the first node in the nodegroup that holds a partition for the table. The utility then executes at this node.

See Also

GET DATABASE CONFIGURATION

[ DB2 List of Books | Search the DB2 Books ]