Rules for updating distribution statistics manually

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

• Frequent-value statistics:
  • VALCOUNT values must be unchanging or decreasing with increasing values of SEQNO.
  • The number of COLVALUE values must be less than or equal to the number of distinct values in the column, which is stored in SYSSTAT.COLUMNS.COLCARD.
  • The sum of the values in VALCOUNT must be less than or equal to the number of rows in the column, which is stored in SYSSTAT.TABLES.CARD.
  • In most cases, COLVALUE values should lie between the second-highest and the second-lowest data values for the column, which are stored in HIGH2KEY and LOW2KEY in SYSSTAT.COLUMNS, respectively. There can be one frequent value that is greater than HIGH2KEY and one frequent value that is less than LOW2KEY.
• Quantile statistics:
  • COLVALUE values must be unchanging or decreasing with increasing values of SEQNO.
  • VALCOUNT values must be increasing with increasing values of SEQNO.
  • The largest COLVALUE value must have a corresponding entry in VALCOUNT that is equal to the number of rows in the column.
  • In most cases, COLVALUE values should lie between the second-highest and the second-lowest data values for the column, which are stored in HIGH2KEY and LOW2KEY in SYSSTAT.COLUMNS, respectively.

Suppose that distribution statistics are available for column C1 with R rows, and that you want to modify the statistics to correspond with a column that has the same relative proportions of data values, but with (F x R) rows. To scale up the frequent-value or quantile statistics by a factor of F, multiply each VALCOUNT entry by F.