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.

Syntax diagram

Read syntax diagramSkip visual syntax diagram MODIFY STATISTICS LIST listdef-nameTABLESPACEdatabase-name.table-space-namePARTintegerINDEXSPACEdatabase-name.index-space-namePARTintegerINDEXcreator-id.index-namePARTintegerDELETEALLACCESSPATHSPACEAGE( integer)(*)DATE( integer)(*)

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, partitions, or any combination of those items. 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.
Start of changePARTEnd of change
Start of changeIdentifies a single partition for which records are to be deleted for the specified table space, index space, or index. MODIFY STATISTICS removes the history statistics records for only the specified partition; aggregate statistics are not deleted.

If PART is specified, but the specified table space, index space, or index is not partitioned, an error is returned. Also, if the part number specified is out of range, an error is returned.

integer
The physical partition number. The number must be in the range from 1 to the number of partitions that are defined for the table space, index space, or index. The maximum is 4096.
End of change
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.