Rebuild sessions - CLP examples

This topic provides a number of examples of rebuild operations.

Scenario 1

In the following examples, there is a recoverable database called MYDB with the following table spaces in it:
  • SYSCATSPACE (system catalogs)
  • USERSP1 (user data table space)
  • USERSP2 (user data table space)
  • USERSP3 (user data table space)
The following backups have been taken:
  • BK1 is a backup of SYSCATSPACE and USERSP1
  • BK2 is a backup of USERSP2 and USERSP3
  • BK3 is a backup of USERSP3
Example 1
The following rebuilds the entire database to the most recent point in time:
  1. Issue a RESTORE DATABASE command with the REBUILD option: 
       db2 restore db mydb rebuild with all tablespaces in database 
      taken at BK3 without prompting
  2. Issue a ROLLFORWARD DATABASE command with the TO END OF LOGS option (this assumes all logs have been saved and are accessible): 
       db2 rollforward db mydb to end of logs
  3. Issue a ROLLFORWARD DATABASE command with the STOP option: 
       db2 rollforward db mydb stop

At this point the database is connectable and all table spaces are in NORMAL state.

Example 2
The following rebuilds just SYSCATSPACE and USERSP2 to a point in time (where end of BK3 is less recent than the point in time, which is less recent than end of logs):
  1. Issue a RESTORE DATABASE command with the REBUILD option and specify the table spaces you want to include. 
       db2 restore db mydb rebuild with tablespace (SYSCATSPACE, USERSP2) 
      taken at BK2 without prompting
  2. Issue a ROLLFORWARD DATABASE command with the TO PIT option (this assumes all logs have been saved and are accessible): 
       db2 rollforward db mydb to PIT
  3. Issue a ROLLFORWARD DATABASE command with the STOP option: 
       db2 rollforward db mydb stop

At this point the database is connectable and only SYSCATSPACE and USERSP2 are in NORMAL state. USERSP1 and USERSP3 are in RESTORE_PENDING state.

To restore USERSP1 and USERSP3 at a later time, using normal table space restores (without the REBUILD option):
  1. Issue the RESTORE DATABASE command without the REBUILD option and specify the table space you want to restore. First restore USERSPI: 
       db2 restore db mydb tablespace (USERSP1) taken at BK1 without prompting
  2. Then restore USERSP3: 
       db2 restore db mydb tablespace taken at BK3 without prompting
  3. Issue a ROLLFORWARD DATABASE command with the END OF LOGS option and specify the table spaces to be restored (this assumes all logs have been saved and are accessible): 
       db2 rollforward db mydb to end of logs tablespace (USERSP1, USERSP3)

    The rollforward will replay all logs up to the PIT and then stop for these two table spaces since no work has been done on them since the first rollforward.

  4. Issue a ROLLFORWARD DATABASE command with the STOP option: 
       db2 rollforward db mydb stop
Example 3
The following rebuilds just SYSCATSPACE and USERSP1 to end of logs:
  1. Issue a RESTORE DATABASE command with the REBUILD option: 
       db2 restore db mydb rebuild with all tablespaces in image 
      taken at BK1 without prompting
  2. Issue a ROLLFORWARD DATABASE command with the TO END OF LOGS option (this assumes all logs have been saved and are accessible): 
       db2 rollforward db mydb to end of logs
  3. Issue a ROLLFORWARD DATABASE command with the STOP option: 
       db2 rollforward db mydb stop

At this point the database is connectable and only SYSCATSPACE and USERSP1 are in NORMAL state. USERSP2 and USERSP3 are in RESTORE_PENDING state.

Example 4
In the following example, the backups BK1 and BK2 are no longer in the same location as stated in the history file, but this is not known when the rebuild is issued.
  1. Issue a RESTORE DATABASE command with the REBUILD option , specifying that you want to rebuild the entire database to the most recent point in time: 
       db2 restore db mydb rebuild with all tablespaces in database 
      taken at BK3 without prompting
    At this point, the target image is restored successfully, but an error is returned from the restore utility stating it could not find a required image.
  2. You must now complete the rebuild manually. Since the database is in the rebuild phase this can be done as follows:
    1. Issue a RESTORE DATABASE command and specify the location of the BK1 backup image: 
         db2 restore db mydb tablespace taken at BK1 from location 
      without prompting
    2. Issue a RESTORE DATABASE command and specify the location of the BK2 backup image: 
         db2 restore db mydb tablespace (USERSP2) taken at BK2 from 
      location without prompting
    3. Issue a ROLLFORWARD DATABASE command with the TO END OF LOGS option (this assumes all logs have been saved and are accessible): 
         db2 rollforward db mydb to end of logs
    4. Issue a ROLLFORWARD DATABASE command with the STOP option: 
         db2 rollforward db mydb stop

At this point the database is connectable and all table spaces are in NORMAL state.

Example 5

In this example, table space USERSP3 contains independent data that is needed for generating a specific report, but you do not want the report generation to interfere with the original database. In order to gain access to the data but not affect the original database, you can use REBUILD to generate a new database with just this table space and SYSCATSPACE. SYSCATSPACE is also required so that the database will be connectable after the restore and roll forward operations.

To build a new database with the most recent data in SYSCATSPACE and USERSP3:
  1. Issue a RESTORE DATABASE command with the REBUILD option, and specify that table spaces SYSCATSPACE and USERSP3 are to be restored to a new database, NEWDB: 
       db2 restore db mydb rebuild with tablespace (SYSCATSPACE, USERSP3) 
      taken at BK3 into newdb without prompting
  2. Issue a ROLLFORWARD DATABASE command on NEWDB with the TO END OF LOGS option (this assumes all logs have been saved and are accessible): 
       db2 rollforward db newdb to end of logs
  3. Issue a ROLLFORWARD DATABASE command with the STOP option: 
       db2 rollforward db newdb stop
At this point the new database is connectable and only SYSCATSPACE and USERSP3 are in NORMAL state. USERSP1 and USERSP2 are in RESTORE_PENDING state.
Note: If container paths are an issue between the current database and the new database (for example, if the containers for the original database need to be altered because the file system does not exist or if the containers are already in use by the original database) then you will need to perform a redirected restore. This example assumes the default autostorage database paths are used for the table spaces.

Scenario 2

In the following example, there is a recoverable database called MYDB that has SYSCATSPACE and one thousand user table spaces named Txxxx, where xxxx stands for the table space number (for example, T0001). There is one full database backup image (BK1)

Example 6
The following restores all table spaces except T0999 and T1000:
  1. Issue a RESTORE DATABASE command with the REBUILD option: 
       db2 restore db mydb rebuild with all tablespaces in image except 
      tablespace (T0999, T1000) taken at BK1 without prompting
  2. Issue a ROLLFORWARD DATABASE command with the TO END OF LOGS option (this assumes all logs have been saved and are accessible): 
       db2 rollforward db mydb to end of logs
  3. Issue a ROLLFORWARD DATABASE command with the STOP option: 
       db2 rollforward db mydb stop

At this point the database will be connectable and all table spaces except T0999 and T1000 will be in NORMAL state. T0999 and T1000 will be in RESTORE_PENDING state.

Scenario 3

The examples in this scenario demonstrate how to rebuild a recoverable database using incremental backups. In the following examples, there is a database called MYDB with the following table spaces in it:
  • SYSCATSPACE (system catalogs)
  • USERSP1 (data table space)
  • USERSP2 (user data table space)
  • USERSP3 (user data table space)
The following backups have been taken:
  • FULL1 is a full backup of SYSCATSPACE, USERSP1, USERSP2 and USERSP3
  • DELTA1 is a delta backup of SYSCATSPACE and USERSP1
  • INCR1 is an incremental backup of USERSP2 and USERSP3
  • DELTA2 is a delta backup of SYSCATSPACE, USERSP1, USERSP2 and USERSP3
  • DELTA3 is a delta backup of USERSP2
  • FULL2 is a full backup of USERSP1
Example 7
The following rebuilds just SYSCATSPACE and USERSP2 to the most recent point in time using incremental automatic restore.
  1. Issue a RESTORE DATABASE command with the REBUILD option. The INCREMENTAL AUTO option is optional. The restore utility will detect what the granularity of the image is and use automatic incremental restore if it is required. 
       db2 restore db mydb rebuild with tablespace (SYSCATSPACE, USERSP2) 
      incremental auto taken at DELTA3 without prompting
  2. Issue a ROLLFORWARD DATABASE command with the TO END OF LOGS option (this assumes all logs have been saved and are accessible): 
       db2 rollforward db mydb to end of logs
  3. Issue a ROLLFORWARD DATABASE command with the STOP option: 
       db2 rollforward db mydb stop

At this point the database is connectable and only SYSCATSPACE and USERSP2 are in NORMAL state. USERSP1 and USERSP3 are in RESTORE_PENDING state.

Example 8
The following rebuilds the entire database to the most recent point in time using incremental automatic restore.
  1. Issue a RESTORE DATABASE command with the REBUILD option. The INCREMENTAL AUTO option is optional. The restore utility will detect what the granularity of the image is and use automatic incremental restore if it is required. 
       db2 restore db mydb rebuild with all tablespaces in database 
      incremental auto taken at DELTA3 without prompting
  2. Issue a ROLLFORWARD DATABASE command with the TO END OF LOGS option (this assumes all logs have been saved and are accessible): 
       db2 rollforward db mydb to end of logs
  3. Issue a ROLLFORWARD DATABASE command with the STOP option: 
       db2 rollforward db mydb stop

At this point the database is connectable and all table spaces are in NORMAL state.

Example 9
The following rebuilds the entire database, except for USERSP3, to the most recent point in time.
  1. Issue a RESTORE DATABASE command with the REBUILD option. Although the target image is a non-incremental image, the restore utility will detect that the required rebuild chain includes incremental images and it will automatically restore those images incrementally. 
       db2 restore db mydb rebuild with all tablespaces in database except 
      tablespace (USERSP3) taken at FULL2 without prompting
  2. Issue a ROLLFORWARD DATABASE command with the TO END OF LOGS option (this assumes all logs have been saved and are accessible): 
       db2 rollforward db mydb to end of logs
  3. Issue a ROLLFORWARD DATABASE command with the STOP option: 
       db2 rollforward db mydb stop

Scenario 4

The examples in this scenario demonstrate how to rebuild a recoverable database using backup images that contain log files. In the following examples, there is a database called MYDB with the following table spaces in it:
  • SYSCATSPACE (system catalogs)
  • USERSP1 (user data table space)
  • USERSP2 (user data table space)
Example 10
The following rebuilds the database with just SYSCATSPACE and USERSP2 to the most recent point in time. There is a full online database backup image (BK1), which includes log files.
  1. Issue a RESTORE DATABASE command with the REBUILD option: 
       db2 restore db mydb rebuild with tablespace (SYSCATSPACE, USERSP2) 
      taken at BK1 logtarget /logs without prompting
  2. Issue a ROLLFORWARD DATABASE command with the TO END OF LOGS option (this assumes all logs after the end of BK1 have been saved and are accessible): 
       db2 rollforward db mydb to end of logs overflow log path (/logs)
  3. Issue a ROLLFORWARD DATABASE command with the STOP option: 
       db2 rollforward db mydb stop

At this point the database is connectable and only SYSCATSPACE and USERSP2 are in NORMAL state. USERSP1 is in RESTORE_PENDING state.

Example 11
The following rebuilds the database to the most recent point in time. There are two full online table space backup images that include log files:
  • BK1 is a backup of SYSCATSPACE, using log files 10-45
  • BK2 is a backup of USERSP1 and USERSP2, using log files 64-80
  1. Issue a RESTORE DATABASE command with the REBUILD option: 
       db2 restore db mydb rebuild with all tablespaces in database 
      taken at BK2 logtarget /logs without prompting
    The rollforward operation will start at log file 10, which it will always find in the overflow log path if not in the primary log file path. The log range 46-63, since they are not contained in any backup image, will need to be made available for roll forward.
  2. Issue a ROLLFORWARD DATABASE command with the TO END OF LOGS option, using the overflow log path for log files 64-80: 
       db2 rollforward db mydb to end of logs overflow log path (/logs)
  3. Issue a ROLLFORWARD DATABASE command with the STOP option: 
       db2 rollforward db mydb stop

At this point the database is connectable and all table spaces are in NORMAL state.

Scenario 5

In the following examples, there is a recoverable database called MYDB with the following table spaces in it:
  • SYSCATSPACE (0), SMS system catalog (relative container)
  • USERSP1 (1) DMS user data table space (absolute container /usersp2)
  • USERSP2 (2) DMS user data table space (absolute container /usersp3)
The following backups have been taken:
  • BK1 is a backup of SYSCATSPACE
  • BK2 is a backup of USERSP1 and USERSP2
  • BK3 is a backup of USERSP2
Example 12
The following rebuilds the entire database to the most recent point in time using redirected restore.
  1. Issue a RESTORE DATABASE command with the REBUILD option: 
       db2 restore db mydb rebuild with all tablespaces in database 
      taken at BK3 redirect without prompting
  2. Issue a SET TABLESPACE CONTAINERS command for each table space whose containers you want to redefine. For example: 
       db2 set tablespace containers for 3 using (file '/newusersp1' 10000)
  3.    db2 set tablespace containers for 4 using (file '/newusersp2' 15000)
  4. Issue a RESTORE DATABASE command with the CONTINUE option: 
       db2 restore db mydb continue
  5. Issue a ROLLFORWARD DATABASE command with the TO END OF LOGS option (this assumes all logs have been saved and are accessible): 
       db2 rollforward db mydb to end of logs
  6. Issue a ROLLFORWARD DATABASE command with the STOP option: 
       db2 rollforward db mydb stop

At this point the database is connectable and all table spaces are in NORMAL state.

Scenario 6

In the following examples, there is a database called MYDB with three database partitions:
  • Database partition 1 contains table spaces SYSCATSPACE, USERSP1 and USERSP2, and is the catalog partition
  • Database partition 2 contains table spaces USERSP1 and USERSP3
  • Database partition 3 contains table spaces USERSP1, USERSP2 and USERSP3
The following backups have been taken, where BKxy represents backup number x on partition y:
  • BK11 is a backup of SYSCATSPACE, USERSP1 and USERSP2
  • BK12 is a backup of USERSP2 and USERSP3
  • BK13 is a backup of USERSP1, USERSP2 and USERSP3
  • BK21 is a backup of USERSP1
  • BK22 is a backup of USERSP1
  • BK23 is a backup of USERSP1
  • BK31 is a backup of USERSP2
  • BK33 is a backup of USERSP2
  • BK42 is a backup of USERSP3
  • BK43 is a backup of USERSP3
Example 13
The following rebuilds the entire database to the end of logs.
  1. On database partition 1, issue a RESTORE DATABASE command with the REBUILD option: 
       db2 restore db mydb rebuild with all tablespaces in database 
      taken at BK31 without prompting
  2. On database partition 2, issue a RESTORE DATABASE command with the REBUILD option: 
       db2 restore db mydb rebuild with tablespaces in database taken at 
      BK42 without prompting
  3. On database partition 3, issue a RESTORE DATABASE command with the REBUILD option: 
       db2 restore db mydb rebuild with all tablespaces in database 
      taken at BK43 without prompting
  4. On the catalog partition, issue a ROLLFORWARD DATABASE command with the TO END OF LOGS option (assumes all logs have been saved and are accessible on all database partitions): 
       db2 rollforward db mydb to end of logs
  5. Issue a ROLLFORWARD DATABASE command with the STOP option: 
       db2 rollforward db mydb stop

At this point the database is connectable on all database partitions and all table spaces are in NORMAL state.

Example 14
The following rebuilds SYSCATSPACE, USERSP1 and USERSP2 to the most recent point in time.
  1. On database partition 1, issue a RESTORE DATABASE command with the REBUILD option: 
       db2 restore db mydb rebuild with all tablespaces in database 
      taken at BK31 without prompting
  2. On database partition 2, issue a RESTORE DATABASE command with the REBUILD option: 
       db2 restore db mydb rebuild with all tablespaces in image taken at 
      BK22 without prompting
  3. On database partition 3, issue a RESTORE DATABASE command with the REBUILD option: 
       db2 restore db mydb rebuild with all tablespaces in image taken at 
      BK33 without prompting
    Note: this command omitted USERSP1, which is needed to complete the rebuild operation.
  4. On the catalog partition, issue a ROLLFORWARD DATABASE command with the TO END OF LOGS option: 
       db2 rollforward db mydb to end of logs
  5. Issue a ROLLFORWARD DATABASE command with the STOP option: 
       db2 rollforward db mydb stop
    The rollforward succeeds and the database is connectable on all database partitions. All table spaces are in NORMAL state, except USERSP3, which is in RESTORE PENDING state on all database partitions on which it exists, and USERSP1, which is in RESTORE PENDING state on database partition 3.
    When an attempt is made to access data in USERSP1 on database partition 3, a data access error will occur. To fix this, USERSP1 will need to be recovered:
    1. On database partitions 3, issue a RESTORE DATABASE command, specifying a backup image that contains USERSP1: 
         db2 restore db mydb tablespace taken at BK23 without prompting
    2. On the catalog partition, issue a ROLLFORWARD DATABASE command with the TO END OF LOGS option and the AND STOP option: 
         db2 rollforward db mydb to end of logs on dbpartitionnum (3) and stop

At this point USERSP1 on database partition 3 can have its data accessed since it is in NORMAL state.

Scenario 7

In the following examples, there is a nonrecoverable database called MYDB with the following table spaces:
  • SYSCATSPACE (0), SMS system catalog
  • USERSP1 (1) DMS user data table space
  • USERSP2 (2) DMS user data table space

There is just one backup of the database, BK1:

Example 15

The following demonstrates using rebuild on a nonrecoverable database.

Rebuild the database using only SYSCATSPACE and USERSP1: 
   db2 restore db mydb rebuild with tablespace (SYSCATSPACE, USERSP1) 
      taken at BK1 without prompting

Following the restore, the database is connectable. If you issue the LIST TABLESPACES command or the MON_GET_TABLESPACE table function, you see that the SYSCATSPACE and USERSP1 are in NORMAL state, while USERSP2 is in DELETE_PENDING/OFFLINE state. You can now work with the two table spaces that are in NORMAL state.

If you want to do a database backup, you will first need to drop USERSP2 using the DROP TABLESPACE statement, otherwise, the backup will fail.

To restore USERSP2 at a later time, you need to reissue a database restore from BK1.