MODIFY STATISTICS

The MODIFY STATISTICS online utility deletes unwanted statistics history records from the corresponding catalog tables. You can remove statistics history records that were written before a specific date, or you can remove records of a specific age. You can delete records for an entire table space, index space, or index.

Run MODIFY STATISTICS regularly to clear outdated information from the statistics history catalog tables. By deleting outdated information from those tables, you can improve performance for processes that access data from those tables.

Restriction: MODIFY STATISTICS does not delete statistics history records for clone tables because statistics are not collected for these tables.

Output

MODIFY STATISTICS deletes rows from the following catalog tables:

  • SYSIBM.SYSCOLDIST_HIST
  • SYSIBM.SYSCOLUMNS_HIST
  • SYSIBM.SYSINDEXES_HIST
  • SYSIBM.SYSINDEXPART_HIST
  • SYSIBM.SYSINDEXSTATS_HIST
  • SYSIBM.SYSLOBSTATS_HIST
  • SYSIBM.SYSTABLEPART_HIST
  • SYSIBM.SYSTABSTATS_HIST
  • SYSIBM.SYSTABLES_HIST
  • SYSKEYTARGETS_HIST
  • SYSKEYTGTDIST_HIST

Authorization required

To execute this utility, you must use a privilege set that includes one of the following authorities:

  • STATS privilege for the database to run MODIFY STATISTICS.
  • DBADM, DBCTRL, or DBMAINT authority for the database. If the object on which the utility operates is in an implicitly created database, DBADM authority on the implicitly created database or DSNDB04 is required.
  • Start of changeSQLADM authority.End of change
  • Start of changeSystem DBADM authority.End of change
  • SYSCTRL or SYSADM authority.

A user ID with installation SYSOPR authority can also execute MODIFY STATISTICS, but only on a table space in the DSNDB01 or DSNDB06 database.

Execution phases of MODIFY STATISTICS

The MODIFY STATISTICS utility operates in these phases:

Phase
Description
UTILINIT
Performs initialization and setup
MODIFYS
Deletes records
UTILTERM
Performs cleanup