Rules for updating table and nickname statistics manually
There are certain guidelines that you should follow when updating statistics in the SYSSTAT.TABLES catalog view.
- The only statistical values that you can update in SYSSTAT.TABLES are CARD, FPAGES, NPAGES, AVGCOMPRESSEDROWSIZE, AVGROWCOMPRESSIONRATIO, PCTROWSCOMPRESSED, OVERFLOW and, for multidimensional clustering (MDC) tables, ACTIVE_BLOCKS.
- The value of the CARD statistic must meet the following criteria:
- It must be greater than or equal to all COLCARD statistic values for the corresponding table in SYSSTAT.COLUMNS.
- It must be greater than the value of the NPAGES statistic.
- It must not be less than or equal to any
fetchvaluein the PAGE_FETCH_PAIRS column of any index (assuming that the CARD statistic is relevant to the index).
- The value of the FPAGES statistic must be greater than the value of the NPAGES statistic.
- The value of the NPAGES statistic must be less than or equal to
fetchvalue in the PAGE_FETCH_PAIRS column of any index (assuming that the NPAGES statistic is relevant to the index).
- Valid values for the AVGCOMPRESSEDROWSIZE statistic are -1 or between 0 and the value of the AVGROWSIZE statistic.
- Valid values for the AVGROWCOMPRESSIONRATIO statistic are -1 or greater than 1.
- Valid values for the PCTROWSCOMPRESSED statistic are -1 or 0 - 100, inclusive.
In a federated database system, use caution when manually updating statistics for a nickname over a remote view. Statistical information, such as the number of rows that a nickname returns, might not reflect the real cost of evaluating this remote view and therefore might mislead the Db2® optimizer. In certain cases, however, remote views can benefit from statistics updates; these include remote views that you define on a single base table with no column functions applied to the SELECT list. Complex views might require a complex tuning process in which you tune each query. Consider creating local views over nicknames so that the Db2 optimizer knows how to derive the cost of those views more accurately.