DB2 point-in-time recovery concepts

Recovery is not one procedure

Crash recovery runs at START DB2: redo/undo to current. You do not pick a timestamp. Table space recovery to current restores a copy and applies log to now—typical after restoring a damaged VSAM data set. Index recovery is RECOVER INDEX (if COPY YES and copies exist) or REBUILD INDEX from the table. Page recovery (RECOVER … PAGE) fixes one page. PIT adds a TO* keyword so you stop earlier.

RBA, LRSN, and timestamp recovery

A log RBA is a location in one member’s log stream. An LRSN is a group-wide sequence number in data sharing (timestamp-derived).RBA recovery (TORBA) is correct for a standalone subsystem. LRSN recovery (TOLOGPOINT with an LRSN) is required once data sharing exists. Timestamp recovery means “find the log point that corresponds to this clock reading”—tools and some RECOVER forms help, but you are still recovering to a log point, not magically to wall-clock without logs.

IBM and practitioners recommend standardizing on TOLOGPOINT even when the value is an RBA, so you do not grab the wrong TORBA after the group was created.

The RECOVER utility

RECOVER can restore from sequential image copies, FlashCopy image copies, a system-level backup, or the log alone (from CREATE/LOAD/REORG if no copy remains and SYSLGRNX still has the range).

1
2
3
4
5
6
7
8
RECOVER TABLESPACE HRDB.EMPTS
  TOLOGPOINT X'000000001C2A3B4C5D6E'
  PARALLEL
  REUSE;
 
RECOVER TABLESPACE HRDB.EMPTS
  TORBA X'000000001C2A3B4C5D6E'
  BACKOUT YES;

Important phases for PIT:

• RESTORE — merge full + incremental copies to a base (PARALLEL splits read/write)
• LOGAPPLY — redo logged changes up to the target
• LOGCSR — identify inflight, indoubt, inabort, postponed-abort URs at that point (only with TORBA/TOLOGPOINT)
• LOGUNDO — roll back those URs so objects are transactionally consistent
• BACKOUT YES — if the object is intact, skip restore and back out committed work from current down to the PIT (plus undo of URs active at the PIT)

TOCOPY of a SHRLEVEL CHANGE copy without going through a log point can leave uncommitted data in the restored pages. Prefer TOLOGPOINT at the copy’s log point if you need consistency. IBM documents extra PIT restrictions (which objects may be recovered together, not to a point inside some utilities, and so on)—read those before a production PIT.

Points of consistency and QUIESCE

A point of consistency is a moment with no inflight URs on the objects you care about. The QUIESCE utility writes SYSCOPY rows (and can write a log-range bookmark) after draining activity. Recovering a referential set to the same quiesce RBA/LRSN keeps parent and child aligned.

Without a quiesce, PIT consistency still rolls back URs that were active at the target, but applications that committed “bad” data before that point stay bad—you chose the time, not the business meaning.

Recovery history: SYSCOPY and MODIFY RECOVERY

SYSIBM.SYSCOPY is the recovery catalog: COPY, QUIESCE, LOAD, REORG, RECOVER events. SYSIBM.SYSLGRNX records which log ranges an object used. The REPORT RECOVERY utility prints what RECOVER would use.

MODIFY RECOVERY deletes old SYSCOPY/SYSLGRNX rows to reclaim catalog space. It also inserts rows that document the deletion. After MODIFY, you cannot recover to the discarded copies or through discarded log ranges. Align MODIFY with image-copy and archive-log retention. Running MODIFY because “the table looked messy” is a classic way to make tomorrow’s RECOVER fail.

Recovery dependencies

• Table space sets — RI parents and children to the same PIT
• LOB/XML — base and auxiliary spaces together
• Indexes — COPY YES indexes recover with the table; COPY NO indexes are rebuilt
• Clones — special rules; do not PIT the base and forget the clone
• Catalog/directory — order of recovery is strict; this is DBA specialist work, not a first PIT

Recovering one table space to Friday and its child to Thursday breaks foreign keys even if both jobs return RC 0.

System recovery and disaster recovery

System recovery uses BACKUP SYSTEM / RESTORE SYSTEM (DFSMShsm copy pools, FlashCopy volumes of data and logs) or a SET LOG SUSPEND + volume dump procedure. You truncate the log to a chosen point (DSNJU003) and bring the whole subsystem back to that time.

Disaster recovery is the same toolkit after the primary site is gone: mirrored or shipped archives, remote image copies or system-level backups, dual BSDS copies, documented restart order for data sharing members. Object-level PIT at the home site does not replace a DR rehearsal. The next page covers image copies, COPY, redo, and undo in backup language.

Explain It Like I'm Five

Crash recovery is “the lights flickered; put the Lego table back the way it was when the lights went out.” Point-in-time recovery is “throw away today’s mess and rebuild the castle from the photo we took Friday, then replay the building instructions (the log) up to Friday night.” RBA/LRSN are page numbers in the instruction book. QUIESCE is making everyone put bricks down so the photo is not blurry. MODIFY RECOVERY is throwing old photos away—fine until you need one.

Exercises

1. Run REPORT RECOVERY on a test table space and identify the latest full copy and log ranges.
2. QUIESCE a test table space set and find the SYSCOPY row. Write a RECOVER TOLOGPOINT using that value.
3. Explain when BACKOUT YES is appropriate versus a full RESTORE + LOGAPPLY.
4. List three objects that must share a PIT with a base table that has LOBs and two COPY NO indexes.
5. Describe what MODIFY RECOVERY DELETED would do to your REPORT output if it removed last month’s copies.