Deleting statistics history

Deleting outdated information from the statistics history catalog tables can improve performance for processes that access data from those tables. Deleting these old statistics also makes space available in the catalog.

After you delete statistics history, the next time that you update the relevant statistics by using RUNSTATS TABLESPACE, REBUILD INDEX, or REORG INDEX, Db2 repopulates the statistics history catalog tables with more recent historical data.

About this task

Start of changeThe MODIFY STATISTICS utility lets you delete some or all statistics history rows for a table space, an index space, an index, or a partition of any of these objects. End of change

Alternatively, you can issue SQL DELETE statements. However, be aware that when you manually delete (or insert or update) catalog information, Db2 does not store the historical information for those operations in the historical catalog tables. Therefore, consider using MODIFY STATISTICS; it simplifies the purging of old statistics without requiring you to write DELETE statements.

Procedure

To delete statistics history:

  1. Decide which statistics history rows to delete.

    After analyzing trends by using the relevant historical catalog information and possibly taking actions based on this information, consider deleting all or some of the statistics history catalog rows. Later, when the statistics are updated by a subsequent utility, you can examine the updated statistics data to determine the efficacy of any adjustments that you made as a result of your previous analysis.

  2. Optional: To avoid time outs when you delete historical statistics with MODIFY STATISTICS, increase the LOCKMAX value for DSNDB06.SYSHIST by using an ALTER TABLESPACE statement.
  3. Run MODIFY STATISTICS according to the following guidelines:
    • To delete rows that meet certain age and date criteria, specify the corresponding keywords (AGE and DATE) for a particular object.
    • To delete only the statistics rows that relate to access path selection, specify the ACCESSPATH option. Alternatively, to delete the rows that relate to space statistics, use the SPACE option. To delete rows in all statistics history catalog tables, including the SYSIBM.SYSTABLES_HIST catalog table, specify DELETE ALL.
    • Start of changeTo delete rows for a specific partition, specify the PART option or use a LISTDEF list with the PARTLEVEL option. End of change
    • Start of changeTo delete rows for a range of partitions, use a LISTDEF list with the PARTLEVEL option.End of change
    Note: Start of changeNote the following differences when running MODIFY STATISTICS at the partition level instead of the object level:
    • When MODIFY STATISTICS is executed at the table-space, index-space, or index level without the PART keyword or using LISTDEF PARTLEVEL specifications, the utility is executed once for the entire object. When MODIFY STATISTICS is executed at the table-space, index-space, or index level with the PART keyword or using LISTDEF PARTLEVEL specifications, the utility is executed once for each partition.
    • Executing MODIFY STATISTICS without PART or LISTDEF PARTLEVEL specifications is not the same as executing MODIFY STATISTICS LIST PARTLEVEL. Even though the rows from catalog tables are deleted at the partition level when MODIFY STATISTICS PARTLEVEL is specified, the rows from the aggregate tables, such as SYSTABLES_HIST and SYSCOLDIST_HIST, are not deleted.
    • When MODIFY STATSTICS is executed with LISTDEF PARTLEVEL on nonpartitioned secondary indexes (NPIs), the utility is executed once for each NPI, and rows from the aggregate catalog history tables (such as SYSIBM.SYSCOLDIST_HIST, SYSIBM.SYSCOLUMNS_HIST AND SYSIBM.SYSINDEXES_HIST) will be deleted.
    End of change