DB2 10.5 for Linux, UNIX, and Windows

Backing up table spaces in shadow table environments

When you back up table spaces for a source-shadow table pair, you must include the full set of table spaces that contain the source table, shadow table, and indexes for those tables.

Before you begin

To perform a backup on table spaces in a shadow table environment, ensure that you have one of the following authorities:

SYSADM
SYSCTRL
SYSMAINT

About this task

ack up table spaces that belong to one or more source-shadow table pairs with a single BACKUP DATABASE command. The table space that contains the InfoSphere® CDC metadata tables can be included in the backup image for recovering from corruption to that table space.

Procedure

To back up table spaces in a shadow table environment:

Identify all table spaces that are associated with shadow tables by running the following query on the system catalog:

WITH TABLELIST as (
   SELECT TABSCHEMA, TABNAME
   FROM SYSCAT.TABLES
   WHERE SUBSTR(PROPERTY,23,1) = 'Y'
      OR TABNAME IN ('TS_AUTH', 'TS_BOOKMARK', 'TS_CONFAUD',
                     'TS_DDLAUD')
   UNION SELECT BSCHEMA as TABSCHEMA, BNAME as TABNAME
         FROM SYSCAT.TABLES, SYSCAT.TABDEP
         WHERE SUBSTR(SYSCAT.TABLES.PROPERTY,23,1) = 'Y'
           AND SYSCAT.TABLES.TABNAME = SYSCAT.TABDEP.TABNAME
           AND SYSCAT.TABLES.TABSCHEMA = SYSCAT.TABLES.TABSCHEMA
)
SELECT substr(SYSCAT.TABLES.TABSCHEMA,1,30) as TABSCHEMA,
       substr(SYSCAT.TABLES.TABNAME,1,30) as TABNAME,
       substr(TBSPACE,1,30) as TBSPACE,
       substr(INDEX_TBSPACE,1,30) as INDEX_TBSPACE,
       substr(LONG_TBSPACE,1,30) as LONG_TBSPACE
FROM SYSCAT.TABLES, TABLELIST
WHERE SYSCAT.TABLES.TABNAME = TABLELIST.TABNAME
  AND SYSCAT.TABLES.TABSCHEMA = TABLELIST.TABSCHEMA;

Back up the InfoSphere CDC replication configuration by issuing the dmbackupmd command as follows:
```
dmbackupmd -I cdcinst1
```
Back up the full set of table spaces that contain the tables and indexes that are associated with one or more source-shadow table pairs by issuing the BACKUP DATABASE command. The following example shows the BACKUP DATABASE command for a single source-shadow table pair where the source table TRADE is in the SOURCE table space and the shadow table TRADE_SHADOW is in the SHADOW table space:
```
BACKUP DATABASE dbname
  TABLESPACE (SOURCE, SHADOW) ONLINE
```
The following example shows the command to use if the InfoSphere CDC metadata tables are in the REPL table space and are to be included in the backup image:
```
BACKUP DATABASE dbname TABLESPACE (SOURCE, SHADOW, REPL) ONLINE
```