Point-in-time recovery (PITR) returns Db2 objects to an earlier, consistent state without recovering to current. On Db2 for z/OS you choose a recovery point, restore from image copies or back out with log, then verify object status, indexes, referential integrity, and application readiness. This how-to walks through prerequisites, steps, verification, and common errors.
Recover-to-current restores the latest usable image copies and applies log until the objects are current. Point-in-time recovery stops earlier: you name a log RBA or LRSN, an image-copy data set, or another supported recovery target. After a successful PITR with consistency, the objects contain only committed work as of that point; uncommitted units of recovery that were active at the target are rolled back on the recovered objects.
PITR is the usual response when bad application logic, a mistaken mass UPDATE or DELETE, a failed migration window, or corrupted input was committed after a known-good moment. It is not a substitute for disaster recovery of the whole subsystem, and it is not a casual undo for a single SQL statement. Plan the recovery set (table spaces, related indexes, LOB/XML spaces, and RI parents and children) so you do not leave CHECK-pending or REBUILD-pending surprises.
Confirm authority before you schedule the window. RECOVER typically requires RECOVERDB privilege on the database, or DBADM, DBCTRL, System DBADM, SYSCTRL, or SYSADM as your site grants them. Installation SYSOPR can recover catalog and directory spaces under documented rules. You also need access to image-copy data sets, archive logs covering the recovery range, and a change ticket that names the business reason and the approved recovery point.
Inventory the recovery set. Include every table space that must stay consistent together: RI parents and children, base and LOB or XML table spaces, and indexes (COPY YES indexes can be recovered; COPY NO indexes are often rebuilt after the table space recovers). Run REPORT RECOVERY so you see SYSCOPY history, available copies, and log ranges. Confirm that the chosen image copies and archive logs still exist on disk or tape and are readable.
Agree on the recovery point with application owners. A QUIESCE WRITE YES point taken for the table space set is usually safer and faster than an arbitrary mid-transaction RBA. If you must recover to a moment that was not quiesced, modern RECOVER still aims for transactional consistency, but related objects must share the same TORBA or TOLOGPOINT value. In data sharing, use LRSN with TOLOGPOINT; in non-data-sharing, use RBA with TORBA (shops often use TOLOGPOINT for either).
If you still control the environment before the damage, establish a planned quiesce for the recovery set. QUIESCE records the current RBA or LRSN in SYSIBM.SYSCOPY so later RECOVER jobs can target that exact consistency point. WRITE YES (the usual default) flushes changed buffer-pool pages for the table spaces and their indexes to disk before the quiesce is recorded.
If damage already occurred, identify the last good moment from SYSCOPY quiesce rows, application timestamps converted to RBA/LRSN, log-analysis tools, or the PIT_RBA associated with a useful image copy. Prefer a quiesce or a carefully chosen log point over TOCOPY of a SHRLEVEL CHANGE copy alone; SHRLEVEL CHANGE copies are not a single instant, and IBM guidance favors TORBA or TOLOGPOINT using the copy’s PIT_RBA so consistency processing can complete.
12345678// Establish a shared quiesce for a recovery set QUIESCE TABLESPACE PAYDB.PAYTS TABLESPACE PAYDB.DEPT WRITE YES // Review recovery assets before changing anything REPORT RECOVERY TABLESPACE PAYDB.PAYTS REPORT RECOVERY TABLESPACE PAYDB.DEPT
Applications must not continue writing to objects you are about to rewind. Coordinate STOP DATABASE or STOP with SPACENAM, drain CICS and batch work, and confirm claimers are gone with DISPLAY DATABASE. Document which transactions will be re-driven after recovery. If replication, Q replication, or CDC tools read these spaces, pause capture so the target does not keep applying post-PIT changes.
Decide whether you recover in place or to alternate data sets for validation. In-place recovery is the production path once the point is approved. Alternate-outspace recoveries (vendor utilities or independent outspace options) are useful when you need to inspect data before cutting over, but they follow different registration rules in SYSCOPY.
Build one RECOVER control statement (or LISTDEF list) that names every object in the recovery set and the same TORBA or TOLOGPOINT. Parallel options can shorten elapsed time for large lists, but keep the recovery point identical across related objects. After RESTORE and LOGAPPLY, PITR runs LOGCSR and LOGUNDO so inflight work at the target point is rolled back on the recovered objects.
Use BACKOUT YES only when the spaces are undamaged and the target log point lies within the recent system checkpoints recorded in the BSDS for each member. BACKOUT skips image-copy restore and reverses committed changes from current back to the named point, which can be much faster for a recent mistake. If the point is older than that checkpoint window, use classic restore-and-apply PITR instead.
12345678910// Classic PITR to a quiesce LRSN/RBA (data sharing example uses TOLOGPOINT) RECOVER LIST PAYSET TOLOGPOINT X'000000001A2B3C4D5E6F' PARALLEL // Recent undo without restoring copies (only if within BSDS checkpoint range) RECOVER TABLESPACE PAYDB.PAYTS TABLESPACE PAYDB.DEPT TOLOGPOINT X'000000001A2B3C4D5E6F' BACKOUT YES
After table-space PITR, indexes defined COPY NO usually need REBUILD INDEX. COPY YES indexes recovered to the same log point should match the data; if they were omitted from the recover list, expect REBUILD-pending. LOB and XML spaces that belong to the set must share the recovery point or you risk CHECK-pending relationships between base and auxiliary data.
Run CHECK INDEX and CHECK DATA when RI or auxiliary relationships exist and your site standards require verification after PITR. Resolve CHECK-pending with the documented CHECK DATA / SCOPE PENDING workflow, not by casually resetting statuses. Take a fresh full image copy after a successful PITR so the next recovery chain starts from a known-good post-event base.
DISPLAY DATABASE for every recovered space and confirm statuses are RW (or the intended restricted state your procedure documents). Confirm utility return codes, RECOVER messages for LOGCSR and LOGUNDO completion, and that SYSCOPY shows the PITR event. Spot-check business keys and row counts against the expected post-recovery snapshot, and have application owners validate critical screens or batch control totals.
Verify archive-log and image-copy retention still support a second recovery if validation fails. Update the change ticket with the exact TORBA or TOLOGPOINT hex value, member or group name, list of objects, start and end times, and who signed off. Only then reopen application access.
Missing or unreadable image copies and archive logs are the most common blockers. REPORT RECOVERY before the window surfaces gaps. Wrong RBA versus LRSN in data sharing produces invalid recovery points; always use the log clock your topology requires. Recovering only a child or only a parent leaves CHECK-pending and broken RI.
BACKOUT YES outside the BSDS checkpoint window fails or is rejected—fall back to restore-and-apply. Recovering mid-unit-of-work without consistency handling used to leave dirty pages; current RECOVER with TORBA or TOLOGPOINT is designed for transactional consistency, but you still must recover related objects together. Authority failures, STOP conflicts, and claimers that never drain show up as utility messages—resolve access and drains before restarting RECOVER from the documented restart phase.
Do not invent a recovery point from a wall-clock guess alone. Convert times carefully, prefer SYSCOPY quiesce rows, and keep hex values exactly as reported. A one-nibble typo can recover to the wrong moment and force a second, more expensive recovery.
Imagine a shared coloring book. Point-in-time recovery erases every crayon mark after a page you already saved, and puts the book back to that saved page. Everyone who drew after that page has to redraw. You need the saved page (image copy), the story of what happened in between (logs), and you must reset every related page in the set so the pictures still match.
1. In data sharing, which recovery target is the usual log clock?
2. Why is QUIESCE valuable before planned PITR?
3. What does BACKOUT YES avoid?