MODIFY RECOVERY

Run the MODIFY RECOVERY utility regularly to remove outdated information from SYSIBM.SYSCOPY and SYSIBM.SYSLGRNX. These tables, particularly SYSIBM.SYSLGRNX, can become very large and take up a considerable amount of space. By deleting outdated information from these tables, you can help improve the performance of processes that access data from these tables.

MODIFY RECOVERY deletes records from the following tables:
  • SYSIBM.SYSCOPY catalog table
  • SYSIBM.SYSOBDS catalog table
  • SYSIBM.SYSLGRNX directory table
The utility also deletes entries from the DBD.

You specify the criteria for deleting these records in the MODIFY RECOVERY statement. You can delete records based on age or a specific date. Alternatively, you can specify that you want to keep a specified number of the most recent records.

MODIFY RECOVERY can delete records for an entire table space, partition, or data set. For all indexes that were defined with the COPY YES attribute on the target table space, the utility automatically deletes the SYSCOPY and SYSLGRNX records that meet the specified criteria.

You can also use MODIFY RECOVERY to recycle Db2® version numbers for reuse.

Restriction: Start of changeIf a table space is in REORG-pending (REORP) status because a RECOVER job was run to recover the data to a point in time before the materialization of pending definition changes, you cannot run MODIFY RECOVERY on that table space. You must run a REORG TABLESPACE job to complete the point-in-time recovery process before you run MODIFY RECOVERY.End of change

Start of changeSYSIBM.SYSCOPY and SYSIBM.SYSLGRNX do not contain records for DSNDB06.SYSTSCPY, DSNDB01.SYSUTILX, DSNDB01.DBD01, or DSNDB01.SYSDBDXA. If you run MODIFY RECOVERY on these table spaces, no SYSCOPY or SYSLGRNX records are deleted.End of change

Output

In addition to deleting catalog and directory records and entries from the DBD, MODIFY RECOVERY can also affect the following items:

SYSIBM.SYSCOPY records

Start of changeIn most cases, MODIFY RECOVERY inserts a row into SYSIBM.SYSCOPY to record the RBA or LRSN of the most recently deleted SYSCOPY or SYSLGRNX record. That row has ICTYPE='M' and STYPE='R'.End of change

MODIFY RECOVERY does not insert this row in SYSCOPY when no SYSLGRNX rows are deleted or when the only SYSCOPY rows that were deleted are rows with the following values:

Start of change
  • ICTYPE='F' and STYPE is blank (full image copy)
  • ICTYPE='F' and STYPE='C' (concurrent copy of the "I" instance)
  • ICTYPE='F' and STYPE='J' (concurrent copy of the "J" instance)
  • ICTYPE='I' (incremental image copy)
  • ICTYPE='Q' (quiesce point)
End of change
COPY-pending status

If MODIFY RECOVERY deletes at least one SYSCOPY record, and the target table space or partition is not recoverable from remaining SYSCOPY records or from system-level backups, the target object is placed in COPY-pending status.

Version numbers

For table spaces and indexes that are defined with COPY YES, MODIFY RECOVERY updates the OLDEST_VERSION column of the following catalog tables:

  • SYSIBM.SYSTABLESPACE
  • SYSIBM.SYSTABLEPART
  • SYSIBM.SYSINDEXES
  • SYSIBM.SYSINDEXPART

When MODIFY RECOVERY deletes all of the SYSCOPY records that contain an OLDEST_VERSION value of 0 for a table space, MODIFY RECOVERY deletes the corresponding rows for that table space from SYSIBM.SYSOBDS. The reason is because point-in-time recovery for the table space is no longer possible.

Authorization required

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

  • IMAGCOPY privilege for the database
  • System DBADM authority
  • DBADM authority
  • SYSCTRL or SYSADM authority
  • 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.

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

Execution phases of MODIFY RECOVERY

The MODIFY RECOVERY utility operates in these phases:

UTILINIT
Initialization and setup.
MODIFY
Records are deleted.
UTILTERM
Cleanup.

Syntax diagram

Read syntax diagramSkip visual syntax diagram MODIFY RECOVERY LISTlistdef-nameTABLESPACEDSNDB04.database-name. table-space-nameDSNUMALLDSNUMinteger CLONE DELETEAGEinteger(*)DATEinteger(*)RETAINLAST( integer)LOGLIMITGDGLIMITLAST( integer)LOGLIMITNOCOPYPEND

Option descriptions

LIST listdef-name
Specifies the name of a LISTDEF list. This list must contain only table spaces or table space partitions. MODIFY RECOVERY processes each object in the list.
TABLESPACE
Specifies the table space for which records are to be deleted.
database-name.table-space-name
The name of the database and table space. The default value for database-name is DSNDB04.
DSNUM integer
Identifies a single partition or data set for which records are to be deleted.
integer
The number of the partition or data set.

Start of changeFor a partitioned table space, specify the physical partition number. The maximum is 4096.End of change

For a nonpartitioned table space, use the data set integer at the end of the data set name as cataloged in the VSAM catalog. The data set name has the following format:
catname.DSNDBx.dbname.tsname.y000z.Annn
where y is either I or J, z is either a 1 or 2, and nnn is the data set integer.

If you specify DSNUM integer, MODIFY RECOVERY does not delete any partition SYSCOPY records that have an RBA that is greater than that of the earliest point to which the entire table space could be recovered. (That point might indicate a full image copy, a LOAD operation with LOG YES or a REORG operation with LOG YES.)

If the partition that you specify is in PRO restricted status, the RETAIN value is set to LAST(2).

If you specify DSNUM integer for a partitioned table space, MODIFY RECOVERY also deletes SYSCOPY records for all partitioned index spaces and updates the version numbers in the SYSIBM.SYSINDEXES catalog table. Db2 does not perform these functions for the nonpartitioned indexes.

ALL
Records are deleted for the entire data set and table space. The default value is ALL.

If image copies are taken by partition or data set and you specify DSNUM ALL, the table space is placed in COPY-pending status if a full image copy of the entire table space does not exist.

If DSNUM ALL is implicitly or explicitly specified for a table space that has a partition in PRO restricted status, MODIFY RECOVERY fails.

CLONE
Indicates that MODIFY RECOVERY is to delete SYSCOPY and SYSLGRNX records for only clone objects. If CLONE is not specified, only records for the base objects are deleted.

If you want MODIFY RECOVERY to process clone objects in a LISTDEF list, specify CLONE in the MODIFY RECOVERY statement. (The CLONED YES option in the LISTDEF statement only specifies whether to include clone objects in list; it has no effect on whether MODIFY RECOVERY processes those objects.)

DELETE
Indicates that records are to be deleted.
AGE integer
Deletes all SYSCOPY and SYSLGRNX records that are older than the specified number of days.
integer
The number of days. The value can range from 0 to 32767. Records that are created today are of age 0 and cannot be deleted by this option.
(*)
Deletes all records, regardless of their age.
DATE integer
Deletes all SYSCOPY and SYSLGRNX records that were written before the specified date.
integer
The date. Specify this value in eight- or six-character format: yyyymmdd or yymmdd, where yyyy or yy is the year, mm is the month, and dd is the day.

If you specify a six-character date, Db2 checks the system clock and converts the date to the most recent, previous eight-character equivalent.

(*)
Deletes all records, regardless of the date on which they were written.

Start of changeSYSLGRNX records that meet the specified deletion criteria are deleted even if no SYSCOPY records are deleted. One exception exists: in a non-data sharing environment, if no SYSLGRNX records were created in DB2® 9 new-function mode or later and no SYSCOPY records are deleted, MODIFY RECOVERY does not deletes SYSLGRNX records, even if they fit the deletion criteria.End of change

RETAIN
Indicates that certain records are to be retained. Older records are deleted.
LAST (integer)
Specifies the number of recent records to retain in SYSIBM.SYSCOPY.
LOGLIMIT
Deletes records that are older than the oldest archive log timestamp. For data sharing, this value is the oldest archive log timestamp across all the members.

The utility determines the oldest archive log timestamp by querying the BSDS. If the BSDS is not available for one of the members and the corresponding member is quiesced, this BSDS is ignored.

Start of changeIf you use a replication product that reads Db2 log records, consider using the RETAIN LOGLIMIT option to ensure that the version information for table spaces and indexes is retained with the same duration as the logs.End of change

GDGLIMIT
If the most recent record in SYSIBM.SYSCOPY refers to a generation data set (GDS), GDGLIMIT specifies that the utility is to consider only those records that reference the same GDG and retain as many as it can without exceeding the GDG limit value.

Db2 does not consider other GDGs that are referenced by SYSIBM.SYSCOPY records. The records that reference other GDGs are deleted in accordance with the deletion date.

LAST (integer)
If the most recent record in SYSIBM.SYSCOPY does not refer to a GDS, LAST specifies the number of recent records to retain in SYSIBM.SYSCOPY.
LOGLIMIT
If the most recent record in SYSIBM.SYSCOPY does not refer to a GDS, LOGLIMIT deletes records that are older than the oldest archive log timestamp.

When you specify RETAIN, the utility determines a cleanup date by checking SYSCOPY records. The utility considers only local primary full image copies (ICTYPE=F and ICBACKUP=blank) with the specified DSNUM value. The utility determines a date, not a complete timestamp. As a result, more copies might be kept than are specified by RETAIN. For example, if you specify RETAIN LAST (2) and the most recent five copies have been taken on the same day, all five copies remain in SYSCOPY.

Start of changeNOCOPYPENDEnd of change
Start of changeSpecifies that the table space is not to be placed in COPY-pending status if MODIFY RECOVERY deletes all image copy records from SYSIBM.SYSCOPY.

NOCOPYPEND does not remove any COPY-pending status that was set prior to the MODIFY RECOVERY utility being run. NOCOPYPEND also does not set informational COPY-pending (ICOPY) status for indexes.

Specify NOCOPYPEND only if the data in the table space can be easily re-created if the data is lost. If an image copy is not taken after MODIFY RECOVERY is run, the table space cannot be recovered by using the RECOVER utility and data might be lost.

End of change

Before running MODIFY RECOVERY

Before you run MODIFY RECOVERY, complete the following actions:

  • Make sure that DSNDB01.SYSLGRNX is not in a restrictive state. Because MODIFY RECOVERY updates DSNDB01.SYSLGRNX, any restrictive status on this table space might cause the utility to abend.

    A prior MODIFY RECOVERY run on DSNDB01.SYSLGRNX could inadvertently lead to this situation. For example, if all recovery information was deleted by the specified age or date criteria, DSNDB01.SYSLGRNX is set to COPY-pending status.

  • Reset RECOVER-pending status for any table spaces on which you plan to run MODIFY RECOVERY. You cannot run MODIFY RECOVERY on a table space that is in RECOVER-pending status.
  • Recommendation: If you plan to use MODIFY RECOVERY to delete SYSCOPY records, first run the REPORT utility with the RECOVERY option. This utility reports all SYSCOPY records for the object at the specified site. Looking at this report first helps you avoid deleting the wrong records.
  • Recommendation: To improve the performance of MODIFY RECOVERY and reduce contention on SYSLGRNX, run the REORG TABLESPACE utility on DSNDB01.SYSLGRNX on a regular basis.

Data sets that MODIFY RECOVERY uses

MODIFY RECOVERY always requires the following two data sets:

Table 1. Data sets that MODIFY RECOVERY uses
Data set Default DD name Description
In-stream data SYSIN An input data stream that contains the utility control statement.
Output data set SYSPRINT An output data set for utility messages.

Concurrency and compatibility for MODIFY RECOVERY

During processing, MODIFY RECOVERY sets the claim class of the target table space or partition to UTRW (Utility restrictive state - Read-write access allowed).

MODIFY RECOVERY can run concurrently on the same target object with any utility except the following utilities:

  • COPY TABLESPACE
  • LOAD
  • MERGECOPY
  • MODIFY RECOVERY
  • RECOVER TABLESPACE
  • REORG TABLESPACE

Db2 treats individual data partitions as distinct target objects. Utilities that operate on different partitions of the same table space are compatible.

Termination or restart of MODIFY RECOVERY

You can use the TERM UTILITY command to terminate MODIFY RECOVERY in any phase without any integrity exposure.

You can restart a MODIFY RECOVERY utility job, but it starts from the beginning again.