Point-in-Time Recovery After a Committed Mass Delete
At 02:14 a production batch job committed a DELETE of 4.2 million rows from PAYMENTS, a 180-million-row table in a 1.4-TB Db2 12 subsystem. The job was supposed to delete one day of stale rows but omitted a predicate. The last full image copy was Sunday at 23:00; incremental copies run nightly at 23:30. Archive logs go to disk and are duplexed to tape every hour; the 02:00 tape copy completed. Active logs are on DASD. The table has three indexes and two DELETE triggers that insert audit rows. By 02:30 the application team confirms the deleted rows are needed and that new payments have been inserted since 02:14. The business wants recovery to 02:13:59, but the batch window ends at 05:00. The team asks whether to use RECOVER TABLESPACE with TOLOGPOINT or TORBA, whether indexes and triggers matter, and how to handle the new payments.
1. Which RECOVER options and prerequisites would you use, and how do you establish the log point?
Read the worked answer
Use a point-in-time recovery to a specific log point before the delete, not a generic backout. Run REPORT RECOVERY on the PAYMENTS table space to list the available image copies and the log ranges needed. Identify the RBA or LRSN that corresponds to 02:13:59, before the DELETE committed, and verify that all archive and active log data sets covering that range are available; recall tape copies if necessary. Then use RECOVER TABLESPACE ... TOLOGPOINT with that point. TORBA is not the right control here because the delete committed and the team needs a named point before it, not recovery to a prior consistency point that may not match 02:13:59. Before starting, stop or quiesce applications that update PAYMENTS and its dependents, confirm the batch window and space for copies, and take a fresh image copy if the recovery itself will be the new baseline. Coordinate the outage with the application owner rather than assuming a table-space recovery is invisible.
2. What happens to the three indexes and to the audit rows produced by the DELETE triggers?
Read the worked answer
RECOVER rebuilds the indexes for the recovered table space, but verify their state afterward: check for rebuild pending, copy pending, and check pending, and rebuild or repair only as required by the recovery options used. Do not assume the indexes are usable just because the table space recovered. The trigger-generated audit rows are a more subtle problem. They are ordinary committed rows in another table, and recovering PAYMENTS does not remove them. After recovery, the audit table may contain rows saying that payments were deleted even though those payments are back. If the audit table must be consistent with PAYMENTS, recover it to the same point in time, or reconcile the audit rows explicitly. Also check referential integrity dependents and any summary tables populated by the batch job. A table-space recovery restores one object; it does not automatically restore the logical business transaction across related objects.
3. Will payments inserted after 02:14 survive the recovery? What if the business needs them?
Read the worked answer
They will not survive a recovery to 02:13:59. Point-in-time recovery is not row-selective: every change after the target point in the recovered table space is removed, including the new payments. Before starting, export or copy the post-02:14 payments to a separate table, or identify them from the application's source or journal so they can be reinserted after recovery. The alternative is to recover to a later point, but then the deleted rows remain deleted, which fails the primary requirement. The team should make that tradeoff explicit with the business: restore the 4.2 million rows and replay the new payments, or preserve the new payments and lose the deleted rows. Given the 05:00 window, estimate the extract, recovery, index rebuild, and replay time before choosing. Do not overlook triggers during replay, or the audit table will be populated twice.