MODIFY RECOVERY
The MODIFY online utility with the RECOVERY option deletes certain records from the DB2® catalog, directory, and DBD. This utility also recycles DB2 version numbers for reuse.
The records that can be deleted include:
- Records from the SYSIBM.SYSCOPY catalog table
- Records from the SYSIBM.SYSOBDS catalog table
- Related log records from the SYSIBM.SYSLGRNX directory table
- Entries from the DBD
- Records that were written before a specific date
- Records of a specific age
- Records for an entire table space, partition, or data set
You can also ensure that a specified number of records is retained.
Run MODIFY 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 performance for processes that access data from these tables.
MODIFY RECOVERY automatically removes the SYSCOPY and SYSLGRNX recovery records that meet the specified criteria for all indexes on the table space with the COPY YES attribute.
Output
In most cases, MODIFY RECOVERY inserts a SYSIBM.SYSCOPY row with ICTYPE='M' and STYPE='R' to record the RBA or LRSN of the most recently deleted SYSCOPY or SYSLGRNX record.
MODIFY RECOVERY does not insert this SYSCOPY row when no SYSLGRNX rows are deleted or when the only SYSCOPY rows that it 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)
For each full and incremental SYSCOPY record that is deleted from SYSIBM.SYSCOPY, the utility returns a message that identifies the name of the copy data set.
If MODIFY RECOVERY deletes at least one SYSCOPY record, and the target table space or partition is not recoverable from SYSCOPY records or from system-level backups, the target object is placed in COPY-pending status.
For table spaces and indexes that are defined with COPY YES, the MODIFY RECOVERY utility 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 for a table space that contain an OLDEST_VERSION value of 0, 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 to run MODIFY RECOVERY
- System DBADM 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.
- SYSCTRL or SYSADM authority
- DBADM, authority
An ID with installation SYSOPR authority can also execute MODIFY RECOVERY, but only on a table space in the DSNDB01 or DSNDB06 database.
SYSIBM.SYSCOPY or SYSIBM.SYSLGRNX does not contain records for DSNDB06.SYSCOPY, DSNDB01.SYSUTILX, DSNDB01.DBD01, or DSNDB01.SYSDBDXA. You can run MODIFY RECOVERY on these table spaces, but you receive message DSNU573I, indicating that no SYSCOPY records were found. No SYSCOPY or SYSLGRNX records are deleted.
Execution phases of MODIFY RECOVERY
The MODIFY RECOVERY phase operates in these phases:
- UTILINIT
- Performs initialization and setup
- MODIFY
- Deletes records
- UTILTERM
- Performs cleanup