MODIFY RECOVERY utility in DB2 for z/OS

What MODIFY RECOVERY deletes

• SYSIBM.SYSCOPY — image copies, quiesce points, and related events
• SYSIBM.SYSOBDS — versioned object descriptors when all SYSCOPY rows with OLDEST_VERSION 0 for that space are gone (point-in-time recover to version 0 is no longer possible)
• SYSIBM.SYSLGRNX — log range directory rows
• Matching entries in the DBD

For indexes defined COPY YES on the target table space, SYSCOPY and SYSLGRNX rows that meet the criteria are deleted automatically. You can recycle Db2 version numbers for reuse as OLDEST_VERSION in SYSTABLESPACE, SYSTABLEPART, SYSINDEXES, and SYSINDEXPART is updated.

DSNDB06.SYSTSCPY, DSNDB01.SYSUTILX, DSNDB01.DBD01, and DSNDB01.SYSDBDXA do not keep SYSCOPY/SYSLGRNX the same way—MODIFY RECOVERY on them deletes nothing from those tables.

Phases and authorization

UTILINIT, MODIFY (rows deleted), DELETEDS (if specified), UTILTERM. You need IMAGCOPY, database admin authorities, System DBADM, SYSCTRL, or SYSADM. Installation SYSOPR: catalog and directory only.

DELETE AGE and DATE

AGE(integer) deletes rows older than that many days (0–32767). Age 0 is today and is not deleted by a numeric AGE. AGE(*) deletes all ages. DATE(yyyymmdd) or six-digit yymmdd (converted to the most recent previous eight-digit date) deletes rows written before that date. DATE(*) deletes regardless of date.

1
2
3
4
5
6
7
8
9
MODIFY RECOVERY TABLESPACE PAYDB.PAYTS
  DELETE AGE(30);
 
MODIFY RECOVERY TABLESPACE PAYDB.PAYTS DSNUM 2
  DELETE DATE(20240101);
 
MODIFY RECOVERY LIST PAYTSLIST
  DELETE AGE(*)
  NOCOPYPEND;

RETAIN LAST, LOGLIMIT, and GDGLIMIT

• RETAIN LAST(n) — keep n recent local primary full image copies (ICTYPE=F, ICBACKUP blank) at the specified DSNUM, then delete older rows. Because the cutoff is a date, extra copies taken the same day stay
• RETAIN LOGLIMIT — delete rows older than the oldest archive log timestamp in the BSDS (oldest across members in data sharing; a quiesced member with unavailable BSDS is skipped). Useful with replication products that need version information as long as the logs exist
• GDGLIMIT — if the most recent SYSCOPY row is a GDS, keep as many of that GDG as the GDG limit allows; other GDGs are deleted by date. LAST and LOGLIMIT under GDGLIMIT apply when the newest row is not a GDS

FLASHCOPY ONLY deletes FlashCopy SYSCOPY rows only when an equivalent sequential copy (same START_RBA, PIT_RBA, DSVOLSER) exists—typically created by COPY or COPYTOCOPY. It does not delete SYSLGRNX/SYSOBDS, does not update DBDs or versions, and does not insert the ICTYPE='M' STYPE='R' row because recoverability is unchanged.

DSNUM, COPY-pending, and DELETEDS

DSNUM ALL (default) is the whole table space. If you copy by partition and specify DSNUM ALL without a full-space image copy, the space can go COPY-pending. DSNUM integer is one partition or, for nonpartitioned spaces, the data set number from the VSAM name (…Annn). With DSNUM integer, MODIFY RECOVERY will not delete partition SYSCOPY rows whose RBA is newer than the earliest point to which the entire table space could be recovered (full copy, LOAD LOG YES, or REORG LOG YES). A partition in PRO forces RETAIN LAST(2). DSNUM ALL with any PRO partition fails. DSNUM on a partitioned space also cleans partitioned COPY YES index SYSCOPY rows and version numbers—not NPSIs.

If at least one SYSCOPY row is deleted and the object is not recoverable from remaining SYSCOPY rows or system-level backups, Db2 sets COPY-pending unless NOCOPYPEND. After a successful cleanup Db2 often inserts SYSCOPY ICTYPE='M' STYPE='R' for the RBA/LRSN of the most recently deleted row (per partition when run at table-space level). That insert is skipped when no SYSLGRNX rows were deleted or when the only deleted SYSCOPY rows were full copies, concurrent I/J copies, incrementals, or quiesce points.

DELETEDS deletes the cataloged copy data sets that belonged to those SYSCOPY rows (disk, HSM-migrated, sequential, inline, FlashCopy, GDG), even if unexpired.

1
2
3
4
5
6
MODIFY RECOVERY TABLESPACE PAYDB.PAYTS
  RETAIN LAST(3)
  DELETEDS;
 
MODIFY RECOVERY TABLESPACE PAYDB.PAYTS
  RETAIN LOGLIMIT;

Before you run it

• Run REPORT RECOVERY so you see the chain you are about to shorten
• DSNDB01.SYSLGRNX must not be in a restrictive state—MODIFY updates it. Deleting all recovery info for SYSLGRNX itself can set it COPY-pending
• Reset RECOVER-pending on the target; you cannot MODIFY a RECP space
• If the space is REORP because of a PIT recover before pending DDL materialized, REORG TABLESPACE first
• REORG DSNDB01.SYSLGRNX regularly to reduce contention

Explain It Like I'm Five

SYSCOPY is a shoebox of ticket stubs from every time you photocopied a book (image copies) or froze a chapter (quiesce). SYSLGRNX is a list of which diary pages cover each book. MODIFY RECOVERY is throwing away stubs older than your backup policy so the shoebox still closes. DELETE AGE(30) means “stubs older than 30 days.” RETAIN LAST(3) means “keep the last few photocopy days.” DELETEDS also throws away the photocopies in the filing cabinet. If you throw away every photocopy, Db2 sticks a COPY-pending note on the shelf unless you promised NOCOPYPEND—which you should only do for books you can reprint from scratch.

Exercises

1. Write MODIFY RECOVERY to delete SYSCOPY/SYSLGRNX older than 14 days for PAYDB.PAYTS and delete the copy data sets.
2. You take seven full copies in one day and RETAIN LAST(3). How many of those seven remain, and why?
3. Your shop switched to BACKUP SYSTEM and SYSCOPY is almost empty but SYSLGRNX is huge. Which DELETE/RETAIN options still help?
4. Why would DSNUM ALL after years of partition-level COPY put the table space in COPY-pending?
5. List two reasons IBM says to run REPORT RECOVERY and to keep SYSLGRNX out of restrictive states before MODIFY.