Recovering a DB2 subsystem to a prior point in time

You can recover a DB2® subsystem and data sharing group to a prior point in time by using the BACKUP SYSTEM and RESTORE SYSTEM utilities.

About this task

In this recovery procedure, you create and populate a table that contains data that is both valid and invalid. You need to restore your DB2 subsystem to a point in time before the invalid data was inserted into the table, but after the point in time when the valid data was inserted. Also, you create an additional table space and table that DB2 will re-create during the log-apply phase of the restore process.

Procedure

To insert data into a table, determine the point in time that you want to recover to, and then recover the DB2 subsystem to a prior point in time:

  1. Issue the START DB2 command to start DB2 and all quiesced members of the data sharing group. Quiesced members are ones that you removed from the data sharing group either temporarily or permanently. Quiesced members remain dormant until you restart them.
  2. Issue SQL statements to create a database, a table space, and two tables with one index for each table.
  3. Issue the BACKUP SYSTEM DATA ONLY utility control statement to create a backup copy of only the database copy pool for a DB2 subsystem or data sharing group.
  4. Issue an SQL statement to first insert rows into one of the tables, and then update some of the rows.
  5. Use the LOAD utility with the LOG NO attribute to load the second table.
  6. Issue SQL statements to create an additional table space, table, and index in an existing database. DB2 will re-create the additional table space and table during the log-apply phase of the restore process.
  7. Issue the SET LOG SUSPEND command or the SET LOG RESUME command to obtain a log truncation point, logpoint1, which is the point you want to recover to. For a non-data sharing group, use the RBA value. For a data sharing group, use the lowest log record sequence number (LRSN) from the active members.
    The following example shows sample output for the SET LOG SUSPEND command:
     09.47.52           @set log suspend                                 
 09.47.52 STC00066  DSN9022I  @ DSNJC001 '-SET LOG' NORMAL COMPLETION
*09.47.55 STC00066 *DSNJ372I  @ DSNJC09A UPDATE ACTIVITY HAS BEEN    
*SUSPENDED FOR VA1A AT RBA 00004777B710, LRSN C31E5141E0C4, PRIOR    
*CHECKPOINT RBA 000047778090
  8. Issue an SQL statement to first insert rows into one of the tables and then to update and delete some rows.
  9. Issue the STOP DB2 command to stop DB2 and all active members of the data sharing group.
  10. Run the DSNJU003 change log inventory utility to create a SYSPITR CRCR record (CRESTART CREATE SYSPITR=logpoint1). The log truncation point is the value that you obtained from issuing either the SET LOG SUSPEND command, or the SET LOG RESUME command.
  11. For a data sharing group, delete all of the coupling facility structures.
  12. Issue the START DB2 command to restart DB2 and all members of the data sharing group.
  13. Run the RESTORE SYSTEM utility. For a data sharing group, this utility can be run only on one member. If the utility stops and you must restart it, you can restart the utility only on the member on which it was initially run.
  14. After the RESTORE SYSTEM utility completes successfully, issue the STOP DB2 command to stop DB2 and all active members of the data sharing group. The DB2 subsystem resets to RECOVER-pending status.
  15. Issue the START DB2 command to restart DB2 and all members of the data sharing group.
  16. Issue the DISPLAY command to identify the utilities that are active and the objects that are restricted. For example: 
    -DIS UTIL(*)
    -DIS DB(DSNDB01) SP(*)
    -DIS DB(DSNDB06) SP(*) LIMIT(*)
    -DIS DB(DSNDB06) SP(*) LIMIT(*)RESTRICT
  17. Stop all of the active utilities that you identified in the previous step.
  18. Recover any objects that are in RECOVER-pending status or REBUILD-pending status from the table that you created in step 6.