Syntax and options of the MODIFY STATISTICS control statement

The MODIFY STATISTICS utility control statement, with its multiple options, defines the function that the utility job performs.

You can create a control statement with the ISPF/PDF edit function. After creating it, save it in a sequential or partitioned data set. When you create the JCL for running the job, use the SYSIN DD statement to specify the name of the data set that contains the utility control statement.

Start of change

Syntax diagram

Read syntax diagramSkip visual syntax diagram MODIFY STATISTICS LIST listdef-name1TABLESPACEdatabase-name.table-space-nameINDEXSPACEdatabase-name.index-space-nameINDEXcreator-id.index-nameDELETEALLACCESSPATHSPACEAGE( integer)(*)DATE( integer)(*)
Notes:
  • 1 You cannot specify a LISTDEF list that contains the PARTLEVEL keyword.
End of change

Option descriptions

LIST listdef-name
Specifies the name of a previously defined LISTDEF list name. You cannot repeat the LIST keyword or specify it with TABLESPACE, INDEXSPACE, or INDEX.

Start of changeThe list can contain index spaces, table spaces, or both. The list cannot contain the PARTLEVEL keyword. MODIFY STATISTICS is invoked once for each object in the list. End of change

TABLESPACE database-name.table-space-name
Specifies the database and the table space for which catalog history records are to be deleted.
database-name
Specifies the name of the database to which the table space belongs. database-name is optional.

The default value is DSNDB04.

table-space-name
Specifies the name of the table space for which statistics are to be deleted.
INDEXSPACE database-name.index-space-name
Specifies the qualified name of the index space for which catalog history information is to be deleted. The utility lists the name in the SYSIBM.SYSINDEXES table.
database-name
Optionally specifies the name of the database to which the index space belongs.

The default value is DSNDB04.

index-space-name
Specifies the name of the index space for which the statistics are to be deleted.
INDEX creator-id.index-name
Specifies the index for which catalog history information is to be deleted.
creator-id
Optionally specifies the creator of the index.

If you omit the qualifier creator-id, Db2 uses the user identifier for the utility job.

index-name
Specifies the name of the index for which the statistics are to be deleted. Enclose the index name in quotation marks if the name contains a blank.
DELETE
Indicates that records are to be deleted.
ALL
Deletes all statistics history rows that are related to the specified object from all catalog history tables.

Rows from the following history tables are deleted only when you specify DELETE ALL:

  • SYSTABLES_HIST
  • SYSTABSTATS_HIST
  • SYSINDEXES_HIST
  • SYSINDEXSTATS_HIST
  • SYSKEYTARGETS_HIST
ACCESSPATH
Deletes all access-path statistics history rows that are related to the specified object from the following history tables:
  • SYSIBM.SYSCOLDIST_HIST
  • SYSIBM.SYSCOLUMNS_HIST
  • SYSKEYTGTDIST_HIST
SPACE
Deletes all space-tuning statistics history rows that are related to the specified object from the following history tables:
  • SYSIBM.SYSINDEXPART_HIST
  • SYSIBM.SYSTABLEPART_HIST
  • SYSIBM.SYSLOBSTATS_HIST
AGE (integer)
Deletes all statistics history rows that are related to the specified object and that are older than a specified number of days.
(integer)
Specifies the number of days in a range 0 - 32 767. This option cannot delete records that are created today (age 0).
(*)
Deletes all records, regardless of their age.
DATE (integer)
Deletes all statistics history rows that were written before a specified date.
(integer)
Specifies the date in an eight-character format. Specify a year (yyyy), month (mm), and day (dd) in the form yyyymmdd.
(* )
Deletes all records, regardless of the date on which they were written.