Recovery After LOAD REPLACE LOG NO
At 02:15 a batch job loads 40 million rows into SALES.ORDERS with LOAD REPLACE LOG NO, then runs an update that abends at 02:47. The table space is PBR with 36 partitions; the load used SHRLEVEL NONE and ENFORCE CONSTRAINTS. No image copy was taken after the load. The last full image copy is from Sunday 01:00; daily incremental copies are taken at 23:00. The job's updates were logged, but the load data is not logged. Db2 reports COPY-pending and CHECK-pending on 12 partitions. The business needs the table available by 06:00; the source extract for the load still exists and can be rerun in 50 minutes. The log archive is intact. There is a quiesce point at 01:55 before the load. SYSCOPY shows the LOAD REPLACE record at 02:15 and no subsequent copy.
1. Can you recover the table space to current? What point in time would you choose, and why?
Read the worked answer
Do not start with RECOVER TO CURRENT. LOAD REPLACE LOG NO writes the loaded pages without the log records needed to reconstruct them. Recovering to current would either fail or produce a table space that cannot be made consistent from the log alone. The recoverable point is before the load, for example the 01:55 quiesce point or the last consistent point immediately before 02:15. Use the quiesce point or SYSCOPY information to establish the target RBA or log point, then recover the affected partitions to that point. After recovery, rerun the load with a recoverable option. Clearing COPY-pending with REPAIR would only hide the missing baseline and is not a recovery strategy.
2. What changes should be made to the load job so that this situation is recoverable next time?
Read the worked answer
Choose between LOG YES and LOG NO with an image copy based on log volume and elapsed time. LOG YES makes the load recoverable through normal log apply if the log volume is acceptable for 40 million rows. If log volume is the constraint, use LOG NO with COPY YES and an inline copy to a copy pool, so a baseline image copy exists as part of the load. After the load, take an additional full image copy before the update step if the business needs a clean restart point. Also review SHRLEVEL: SHRLEVEL NONE makes the object unavailable during the load, while SHRLEVEL CHANGE can reduce the outage but increases log and resource use. Document the recovery point and verify that the copy is recorded in SYSCOPY before the batch proceeds.
3. How would you clear the CHECK-pending state and validate the data before opening the table to users?
Read the worked answer
Recover first; do not use REPAIR to reset CHECK-pending before the recovery target is correct. After recovery to the pre-load point, the check-pending state should be cleared by the recovery or by rerunning the load with constraint enforcement. If any check-pending remains, determine which constraint or auxiliary object is involved rather than issuing a blanket REPAIR. Validate referential integrity with CHECK DATA where appropriate, verify indexes with CHECK INDEX, and confirm that the LOB and auxiliary objects are consistent. Take a full image copy after validation and before application access. Rerun the load only after confirming that the source extract and the target partition ranges match.