Collecting statistics by partition

You can collect statistics for a single data partition or index partition to avoid the cost of running utilities against unchanged partitions.

About this task

For a partitioned table space, Db2 keeps statistics separately by partition and also collectively for the entire table space. The following table shows the catalog tables that contain statistics by partition and, for each one, the table that contains the corresponding aggregate statistics.

Table 1. The catalog tables that contain statistics by partition and the table that contains the corresponding aggregate statistics
Statistics by partition are in Aggregate statistics are in
SYSTABSTATS SYSTABLES
SYSINDEXSTATS SYSINDEXES
SYSCOLSTATS SYSCOLUMNS
SYSCOLDISTSTATS SYSCOLDIST
SYSKEYTARGETSTATS SYSKEYTARGETS
SYSKEYTGTDISTSTATS SYSKEYTGTDIST

Procedure

To collect statistics by partition:

Specify the PART option when you collect statistics with the RUNSTATS utility or inline with another utility.

When you run utilities by partition, Db2 uses the results to update the aggregate statistics for the entire table space or index. If statistics do not exist for each separate partition, Db2 can calculate the aggregate statistics only if the utilities are run under one of the following options:

  • The FORCEROLLUP YES option is specified when the utility is run.
  • The value of the STATROLL subsystem parameter is YES.

If you do not use specify these options, you must collect statistics on the entire object before you collect statistics on separate partitions.