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.

For typed tables, the specified table name must be the name of the hierarchy's root table.

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

This command is not supported for declared temporary tables.

This utility does not support the use of nicknames.

Use RUNSTATS to update 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)
On tables that have been reorganized
When a new index has been created.

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

If index statistics are requested, and statistics have never been run on the table containing the index, statistics on both the table and indexes are calculated.

After issuing this command, a COMMIT should be issued to release the locks.

To allow new access plans to be generated, the packages that reference the target table must be rebound after issuing this command.

Statistics are collected based on the table data that is located on the database partition where the command executes. Global table statistics for an entire partitioned table are derived by multiplying the values obtained at a database partition by the number of database partitions in the nodegroup over which the table is partitioned. The global statistics are stored in the catalog tables.

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

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

If inconsistencies are found when running a portion of this command (resulting from activity on the table since the command was last issued), a warning message is returned. For example, if table distribution statistics were gathered on the first issue, and only index statistics are gathered on the second issue, then if inconsistencies are detected as a result of activity on the table, the table distribution statistics are dropped. At this point, it is recommended to issue the command again to refresh the table distribution statistics.

See Also

GET DATABASE CONFIGURATION

REORGANIZE TABLE

REORGCHK.

[ Top of Page | Previous Page | Next Page ]