Use the RUNSTATS utility to collect
catalog statistics on tables, indexes, and statistical views. The
query optimizer uses this information to choose the best access plans
for queries.
About this task
For privileges and authorities that are required to use this
utility, see the description of the
RUNSTATS command.
To collect catalog statistics:
Procedure
- Connect to the database that contains the tables, indexes,
or statistical views for which you want to collect statistical information.
- Collect statistics for queries that
run against the tables, indexes, or statistical views by using one
of the following methods:
- From the DB2® command line, execute the RUNSTATS command
with appropriate options. These options enable you to tailor the statistics
that are collected for queries that run against the tables, indexes,
or statistical views.
- From IBM® Data Studio,
open the task assistant for the RUNSTATS command.
- When the runstats operation completes, issue a COMMIT statement
to release locks.
- Rebind any packages that access the tables, indexes, or
statistical views for which you have updated statistical information.
Results
Note: - The RUNSTATS command does not support the use
of nicknames. If queries access a federated database, use RUNSTATS to
update statistics for tables in all databases, then drop and recreate
the nicknames that access remote tables to make the new statistics
available to the optimizer.
- When you collect statistics for a table in a partitioned database
environment, RUNSTATS only operates on the database
partition from which the utility is executed. The results from this
database partition are extrapolated to the other database partitions.
If this database partition does not contain a required portion of
the table, the request is sent to the first database partition in
the database partition group that contains the required data.
Statistics
for a statistical view are collected on all database partitions containing
base tables that are referenced by the view.
- For DB2 V9.7
Fix Pack 1 and later releases, the following apply to the collection
of distribution statistics on a column of type XML:
- Distribution statistics are collected for each index over XML
data specified on an XML column.
- The RUNSTATS command must collect both distribution
statistics and table statistics to collect distribution statistics
for an index over XML data.
- As the default, the RUNSTATS command collects
a maximum of 250 quantiles for distribution statistics for each index
over XML data. The maximum number of quantiles for a column can be
specified when executing the RUNSTATS command.
- Distribution statistics are collected on indexes over XML data
of type VARCHAR, DOUBLE, TIMESTAMP, and DATE. XML distribution statistics
are not collected on indexes over XML data of type VARCHAR HASHED.
- Distribution statistics are not collected on partitioned indexes
over XML data defined on a partitioned table.
DB2 Version 9.7 for Linux, UNIX, and Windows
