DB2 Version 9.7 for Linux, UNIX, and Windows

Rules for updating index statistics manually

There are certain guidelines that you should follow when updating statistics in the SYSSTAT.INDEXES catalog view.

The following rules apply to PAGE_FETCH_PAIRS:
- Individual values in the PAGE_FETCH_PAIRS statistic must not be longer than 10 digits and must be less than the maximum integer value (2 147 483 647).
- Individual values in the PAGE_FETCH_PAIRS statistic must be separated by a blank character delimiter.
- There must always be a valid PAGE_FETCH_PAIRS statistic if CLUSTERFACTOR is greater than zero.
- There must be exactly 11 pairs in a single PAGE_FETCH_PAIRS statistic.
- Buffer size values in a PAGE_FETCH_PAIRS statistic (the first value in each pair) must appear in ascending order.
- Any buffer size value in a PAGE_FETCH_PAIRS statistic cannot be greater than MIN(NPAGES, 524 287) for a 32-bit operating system, or MIN(NPAGES, 2 147 483 647) for a 64-bit operating system, where NPAGES (stored in SYSSTAT.TABLES) is the number of pages in the corresponding table.
- Page fetch values in a PAGE_FETCH_PAIRS statistic (the second value in each pair) must appear in descending order, with no individual value being less than NPAGES or greater than CARD for the corresponding table.
- If the buffer size value in two consecutive pairs is identical, the page fetch value in both of the pairs must also be identical.
An example of a valid PAGE_FETCH_PAIRS statistic is:
```
   PAGE_FETCH_PAIRS =
     '100 380 120 360 140 340 160 330 180 320 200 310 220 305 240 300
      260 300 280 300 300 300'
```
where
```
   NPAGES = 300
   CARD = 10000
   CLUSTERRATIO = -1
   CLUSTERFACTOR = 0.9
```
The following rules apply to CLUSTERRATIO and CLUSTERFACTOR:
- Valid values for CLUSTERRATIO are -1 or between 0 and 100.
- Valid values for CLUSTERFACTOR are -1 or between 0 and 1.
- At least one of the CLUSTERRATIO and CLUSTERFACTOR values must be -1 at all times.
- If CLUSTERFACTOR is a positive value, it must be accompanied by a valid PAGE_FETCH_PAIRS value.
For relational indexes, the following rules apply to FIRSTKEYCARD, FIRST2KEYCARD, FIRST3KEYCARD, FIRST4KEYCARD, FULLKEYCARD, and INDCARD:
- For a single-column index, FIRSTKEYCARD must be equal to FULLKEYCARD.
- FIRSTKEYCARD must be equal to SYSSTAT.COLUMNS.COLCARD for the corresponding column.
- If any of these index statistics are not relevant, set them to -1. For example, if you have an index with only three columns, set FIRST4KEYCARD to -1.
- For multiple column indexes, if all of the statistics are relevant, the relationship among them must be as follows:
```
   FIRSTKEYCARD <= FIRST2KEYCARD <= FIRST3KEYCARD <= FIRST4KEYCARD
     <= FULLKEYCARD <= INDCARD == CARD
```
For indexes over XML data, the relationship among FIRSTKEYCARD, FIRST2KEYCARD, FIRST3KEYCARD, FIRST4KEYCARD, FULLKEYCARD, and INDCARD must be as follows:
```
   FIRSTKEYCARD <= FIRST2KEYCARD <= FIRST3KEYCARD <= FIRST4KEYCARD
     <= FULLKEYCARD <= INDCARD
```
The following rules apply to SEQUENTIAL_PAGES and DENSITY:
- Valid values for SEQUENTIAL_PAGES are -1 or between 0 and NLEAF.
- Valid values for DENSITY are -1 or between 0 and 100.