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
  • Start of changeRecords from the SYSIBM.SYSOBDS catalog tableEnd of change
  • 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

Start of changeIn 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.End of change

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:

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

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.

Start of changeIf 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.End of change

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

Start of changeWhen 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.End of change

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
  • Start of changeSystem DBADM authorityEnd of change
  • 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.

Start of changeSYSIBM.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.End of change

Execution phases of MODIFY RECOVERY

The MODIFY RECOVERY phase operates in these phases:

UTILINIT
Performs initialization and setup
MODIFY
Deletes records
UTILTERM
Performs cleanup