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.
- SYSIBM.SYSCOPY catalog table
- SYSIBM.SYSOBDS catalog table
- SYSIBM.SYSLGRNX directory table
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.
If
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.
SYSIBM.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.
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
-
In
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'.
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:

- 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)
- 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
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.Related information:
- 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.
For a
partitioned table space, specify the physical partition number. The maximum is 4096.
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:
where y is either I or J, z is either a 1 or 2, and nnn is the data set integer.catname.DSNDBx.dbname.tsname.y000z.AnnnIf 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.
SYSLGRNX 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.
- 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.
If 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.
- 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.
NOCOPYPEND
Specifies 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.

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:
| 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.
