Collecting index statistics

Collect index statistics to help the optimizer decide whether a specific index should be used to resolve a query.

About this task

The following example is based on a database named SALES that contains a CUSTOMERS table with indexes CUSTIDX1 and CUSTIDX2.

For privileges and authorities that are required to use the RUNSTATS utility, see the description of the RUNSTATS command.

Procedure

To collect detailed statistics for an index:

Connect to the SALES database.
Execute one of the following commands from the Db2® command line, depending on your requirements:
- To collect detailed statistics on both CUSTIDX1 and CUSTIDX2:
```
   runstats on table sales.customers and detailed indexes all
```
- To collect detailed statistics on both indexes, but with sampling instead of detailed calculations on each index entry:
```
   runstats on table sales.customers and sampled detailed indexes all
```
  The SAMPLED DETAILED parameter requires 2 MB of the statistics heap. Allocate an additional 488 4-KB pages to the stat_heap_sz database configuration parameter setting for this memory requirement. If the heap is too small, the RUNSTATS utility returns an error before it attempts to collect statistics.
- To collect detailed statistics on sampled indexes, as well as distribution statistics for the table so that index and table statistics are consistent:
```
   runstats on table sales.customers
     with distribution on key columns
       and sampled detailed indexes all
```