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

The MODIFY STATISTICS utility lets you delete some or all statistics history rows for a table space, an index space, or an index.

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.