DB2 10.5 for Linux, UNIX, and Windows

Restoring table spaces in shadow table environments

Restore a table space after a problem such as a media, storage, or application failure. You must restore together the table spaces that contain the source table and shadow table.

Before you begin

To restore to an existing database, ensure that you have one of the following authorities:

SYSADM
SYSCTRL
SYSMAINT

To restore to a new database, ensure that you have one of the following authorities:

SYSADM
SYSCTRL

About this task

Restoring the table space that contains the source table in isolation can place the shadow table in drop pending state. If you restore the table space that contains the shadow table in isolation, the shadow table is placed in integrity pending state.

Procedure

To restore table spaces in a shadow table environment:

Stop InfoSphere® CDC replication. The following command stops replication for a subscription that is named SHADOW in a InfoSphere CDC instance that is named cdcinst1:
```
dmendreplication -I cdcinst1 -s SHADOW
```
Reset the values in the SYSTOOLS.REPL_MQT_LATENCY table by issuing the following command:
```
UPDATE SYSTOOLS.REPL_MQT_LATENCY SET (COMMIT_POINT,DELAY_OFFSET) = (0,0)
```
Restore the full set of table spaces that contain the tables and indexes that are associated with one or more source-shadow tables pairs by issuing the RESTORE DATABASE command. The following example shows the RESTORE DATABASE command for a source table named TRADE in the SOURCE table space and a shadow table named TRADE_SHADOW in the SHADOW table space:
```
RESTORE DATABASE dbname TABLESPACE (SOURCE, SHADOW) ONLINE
```
After the restore is complete, the table spaces are in the rollforward pending state.
Take the table spaces out of rollforward pending state by issuing the ROLLFORWARD DATABASE command. The following command takes the table spaces out of the rollforward pending state without replaying recent changes from the log files:
```
ROLLFORWARD DATABASE dbname TO END OF BACKUP AND STOP 
  TABLESPACE (SOURCE, SHADOW) ONLINE
```
The preceding example is the ROLLFORWARD DATABASE command used for a source table named TRADE in the SOURCE table space and a shadow table named TRADE_SHADOW in the SHADOW table space. You can also roll forward to a specific point in time or to the end of logs. Unless you roll forward to the end of logs, the restored table spaces are placed in backup pending state and are not accessible until you create a new backup image.
If you did not perform a rollforward to end of logs, remove the table spaces from the backup pending state by issuing the BACKUP DATABASE command as follows: The following example shows the BACKUP DATABASE command for a source table named TRADE in the SOURCE table space and a shadow table named TRADE_SHADOW in the SHADOW table space:
```
BACKUP DATABASE dbname TABLESPACE (SOURCE, SHADOW) ONLINE
```
If necessary, restore the InfoSphere CDC replication configuration to a point in time that is as close as possible to that of the restored table spaces. You do not have to do this step if you restored the table spaces to the end of logs or if there were no recent changes to the subscription status or if no table mappings became invalid by restoring table space. In the following example, the command copies the files that are associated with a restore point b2 into the instance directory for an instance called cdcinst1:
```
cp cdc-installation-dir/instance/cdcinst1/conf/backup/b2/* 
     cdc-installation-dir/instance/cdcinst1/conf/
```
When you restore the InfoSphere CDC replication configuration, the current instance configuration is overwritten by the version that was captured by the dmbackupmd backup command.
Flag the newly restored source tables for refresh. In the following example, the command flags for refresh the source table DTW.TRADE in a subscription that is named SHADOW for a InfoSphere CDC instance that is named cdcinst1:
```
dmflagforrefresh -I cdcinst1 -s SHADOW -t DTW.TRADE
```
Restart InfoSphere CDC replication by issuing the dmstartmirror command. The following example shows how to restart a subscription that is named SHADOW in a InfoSphere CDC instance that is named cdcinst1:
```
dmstartmirror -I cdcinst1 -s SHADOW
```