RUNSTATS

The RUNSTATS online utility gathers summary information about the characteristics of data in table spaces, indexes, and partitions. DB2® records these statistics in the DB2 catalog and uses them to select access paths to data during the bind process.

You can use these statistics to evaluate the database design and determine when table spaces or indexes must be reorganized. To obtain the updated statistics, you can query the catalog tables.

The two formats for the RUNSTATS utility are RUNSTATS TABLESPACE and RUNSTATS INDEX. RUNSTATS TABLESPACE gathers statistics on a table space and, optionally, on tables, indexes or columns; RUNSTATS INDEX gathers statistics only on indexes. RUNSTATS does not collect statistics for clone tables or index spaces.

RUNSTATS can collect statistics on any single column or set of columns. RUNSTATS collects the following two types of distribution statistics:

Frequency: The percentage of rows in the table that contain a value for a column or combination of values for a set of columns.
Cardinality: The number of distinct values in the column or set of columns.

When you run RUNSTATS TABLESPACE, you can use the COLGROUP option to collect frequency and cardinality statistics on any column group. You can also collect frequency and cardinality statistics on any single column. When you run RUNSTATS INDEX, you can collect frequency statistics on the leading column of an index and multi-column frequency and cardinality statistics on the leading concatenated columns of an index.

When you run RUNSTATS TABLESPACE, you can use the HISTOGRAM option, with the COLGROUP option, to indicate that histogram statistics are to be gathered for the specified group of columns. RUNSTATS TABLESPACE does not collect histogram statistics for LOB table spaces or XML table spaces. When you run RUNSTATS INDEX, histogram statistics can only be collected on the prefix columns with the same order. Key columns with a mixed order are not allowed for histogram statistics. RUNSTATS INDEX does not collect histogram statistics for XML node ID indexes or XML indexes.

Restriction: Start of change

You cannot run RUNSTATS on directory objects. End of change

Output

RUNSTATS updates the DB2 catalog with table space or index space statistics, prints a report, or both.

Important: Start of change

Use care when issuing SQL statements or using tools to update statistics values in catalog tables. If such updates introduce invalid data, unpredictable results can occur, including abends for RUNSTATS and other utilities. If such problems occur, you can run the RUNSTATS utility and collect statistics at the table space level to resolve the problems, in most cases. End of change

Authorization required

To execute this utility, you must use a privilege set that includes one of the following authorities:

STATS privilege for the database
DBADM, DBCTRL, or DBMAINT authority for the database. If the object on which the utility operates is in an implicitly created database, DBADM authority on the implicitly created database or DSNDB04 is required.
System DBADM authority
SQLADM authority
SYSCTRL or SYSADM authority

An ID with installation SYSOPR authority can also execute the RUNSTATS utility, but only on a table space in the DSNDB06 database.

To use RUNSTATS with the REPORT YES option, you must have the SELECT privilege on the reported tables. RUNSTATS does not report values from tables that the user is not authorized to see.

To gather statistics on a LOB table space, you must have SYSADM or DBADM authority for the LOB table space.

Execution phases of RUNSTATS

The RUNSTATS utility operates in the following phases:

Phase: Description
UTILINIT: Performs initialization
RUNSTATS: Scans table space or index and updates catalog.
If you specify COLGROUP, RUNSTATS also performs a subtask that sorts one or more column group's data. If you specify FREQVAL with COLGROUP or are collecting frequency statistics for data-partitioned secondary indexes, RUNSTATS also performs a subtask that sorts the partition-level frequency data.
UTILTERM: Performs cleanup

RUNSTATS TABLESPACE syntax and options
RUNSTATS TABLESPACE utility control statements define operations completed by RUNSTATS utility jobs.
RUNSTATS INDEX syntax and options
RUNSTATS INDEX utility control statements define the operations completed by RUNSTATS utility jobs.
Statistics profile syntax
The options of a statistics profile are stored within the PROFILE_TEXT column of the SYSTABLES_PROFILES catalog table.
Before running RUNSTATS
Certain activities might be required before you run the RUNSTATS utility, depending on your situation.
Collecting distribution statistics for column groups
When RUNSTATS collects distribution statistics for columns groups, the utility invokes a sort program to sort the distribution statistics. This sort requires its own work data set. The DD name is STATWK01.
Updating statistics for a partitioned table space
You can run RUNSTATS on one or more single partitions of one or more table spaces or indexes (including data-partitioned secondary indexes). When you run the utility on a single partition of an object, RUNSTATS uses the resulting partition-level statistics to update the aggregate statistics for the entire object.
Collection of statistics on the DB2 catalog and directory
You can use the RUNSTATS utility to gather statistics for the DB2 catalog. DB2 uses the collected statistics on the catalog to determine the access path for user queries of the catalog. You cannot run RUNSTATS on any DB2 directory objects.
Collecting frequency statistics for data-partitioned secondary indexes
When RUNSTATS collects frequency statistics on at least one data-partitioned secondary index, the utility invokes a sort program to sort the statistics. This sort requires temporary sort work data sets. The DD name is ST01WKnn.
Collecting statistics history
You can collect statistics history by using the RUNSTATS utility.
Collection of statistics on LOB table spaces
You can specify that RUNSTATS is to collect space statistics on a LOB table space. You can use these statistics to determine when the LOB table space should be reorganized. No statistics on the LOB table space affect access path selection.
Collection of statistics on XML objects
You can use separate RUNSTATS control statements to collect statistics on XML table spaces, or on their associated base table spaces.
Termination or restart of RUNSTATS
You can terminate and restart the RUNSTATS utility.
Review of RUNSTATS output
The RUNSTATS utility updates columns in the catalog tables. When you specify REPORT YES, the RUNSTATS utility also generates a report of the statistics that it gathered.
After running RUNSTATS
Certain activities might be required after you run the RUNSTATS utility, depending on your situation.
Sample RUNSTATS control statements
Use the sample control statements as models for developing your own RUNSTATS control statements.

Parent topic: DB2 online utilities

Related concepts:

Statistics profiles

Related tasks:

Maintaining DB2 database statistics

Automating statistics maintenance

Invalidating statements in the dynamic statement cache

Related reference:

Case study: Stored procedure that runs RUNSTATS in parallel (DB2 for z/OS® Stored Procedures: Through the CALL and Beyond)