DB2 10.5 for Linux, UNIX, and Windows

DB2 administration tasks in shadow table environments

The use of shadow tables requires that you follow certain guidelines when you do regular administration tasks such as backing up, restoring, reorganizing, and redesigning databases.

Review and follow the guidelines in the following sections:

Starting and stopping DB2 and IBM InfoSphere Change Data Capture processes
Table space backup and restore for shadow tables
Shadow table statistics
Table and index reorganization
Table operations that interrupt InfoSphere CDC replication
Unsupported table operations on source tables of shadow tables

Starting and stopping DB2 and IBM® InfoSphere Change Data Capture processes

For DB2® environments that use shadow tables, start the processes in the following sequence so that replication works without any additional intervention:

Start the DB2 instance. For more information, see Starting instances (Linux, UNIX).
Start the InfoSphere® CDC Access Server processes by entering the following commands:
```
cd /access-server-installation-dir/bin/ 
nohup ./dmaccessserver &
```

Start the InfoSphere CDC instance by entering the following commands:

cd /cdc-installation-dir/bin
nohup ./dmts64 -I cdc-instance-name &

To stop the processes, follow this sequence:

Stop the InfoSphere CDC instance by entering the following commands:
```
cd /cdc-installation-dir/bin
./dmshutdown -I cdc-instance-name
```
Stop the InfoSphere CDC Access Server processes by entering the following commands:
```
cd /access-server-installation-dir/bin/
./dmshutdownserver
```
Stop the DB2 instance. For more information, see Stopping instances (Linux, UNIX).

Table space backup and restore for shadow tables

When you design a backup strategy for shadow tables, consider all the database objects that are involved with shadow table operation to ensure the smooth running of the database after a restore. Because source tables, shadow tables, and InfoSphere CDC metadata can be in different table spaces, you must back up and restore relevant table spaces together.

Table spaces that are associated with shadow tables

The table spaces that are associated with shadow tables are the table spaces that contain the following tables:

Source table and shadow table pairs. It is common practice to place a source table and a shadow table in separate table spaces. For backup and restore, you must back up and restore together the table spaces for source-shadow table pairs. Restoring only the table space that contains a source table to a previous point in time can place the shadow table in a drop-pending state. Restoring only the table space that contains the shadow table can place the shadow-table in an integrity-pending state.
InfoSphere CDC metadata tables. These tables are created in the default user table space when you create the InfoSphere CDC instance. You should move these tables into a specific table space during the instance setup. For more information about moving these tables, see Setting up the InfoSphere CDC instance for shadow tables. You must back up these tables together for recovery purposes. Recovering these tables is required only in the case of table space corruption.

You can use the following query to identify all table spaces that are associated with shadow tables:

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;

Backup strategies

You must back up together all the table spaces that are associated with a source-shadow table pair to the same backup image. You can also back up in one image the table spaces for two or more of source-shadow table pairs. What is important is that the table space for a source table and its corresponding shadow table are in the same backup image. For example, if the source tables are in the BASE table space and the shadow tables are in SHADOW table space, back up these two table spaces as follows:

BACKUP DATABASE db2-database-name TABLESPACE (BASE, SHADOW) ONLINE

Optionally, you can back up the table space that contains the InfoSphere CDC metadata tables to the same backup image. For example, if the InfoSphere CDC metadata tables are in the REPL table space, back up the table spaces that contain the source, shadow, and InfoSphere CDC metadata tables to one backup image as follows:

BACKUP DATABASE db2-database-name TABLESPACE (BASE, SHADOW,REPL) ONLINE

To back up table spaces that contain shadow tables, follow the steps in Backing up table spaces in shadow table environments.

Restore and rollforward strategies

You can recover table spaces for individual source-shadow table pairs independently of other source-shadow table pairs. After a rollforward, flag the source table for refresh before you start mirroring.

It is normal practice to restore only table spaces that contain source-shadow table pairs and to exclude the table space that includes the InfoSphere CDC metadata tables. Recovery of the table space that contains the InfoSphere CDC metadata tables is required only in cases of table space corruption, such as due to a disk failure. If you recover the table space that contains the InfoSphere CDC metadata tables, you must flag all source tables for refresh before you start mirroring.

To prevent inaccurate latency information from being communicated to the DB2 database through the SYSTOOLS.REPL_MQT_LATENCY table, reset to zero the columns in this table after you end replication because of a restore.

To restore and rollforward table spaces that contain shadow tables, follow the steps in Restoring table spaces in shadow table environments.

InfoSphere CDC replication configuration data

InfoSphere CDC for DB2 for LUW stores replication configuration data for the InfoSphere CDC instance in a metadata database. This database is stored in files in the cdc-installation-dir and is different from the InfoSphere CDC metadata tables, which are related to replication and are stored in the DB2 database. Back up the replication configuration of your InfoSphere CDC instance after you do the initial setup of the subscription for shadow tables and after you update this subscription by adding or dropping a table mapping for a shadow table. To back up the replication configuration, use the dmbackupmd command.

Under normal circumstances, you do not have to restore the replication configuration of your InfoSphere CDC instance. However, if you restore shadow tables to a point in time in the past, ensure that you restore from a backup of the replication configuration data that you made at that point in time. Restoring from this backup ensures that the table descriptions that are stored in the replication configuration data match the table information in the DB2 system catalog.

To back up and restore table spaces that contain shadow tables, follow the steps in Backing up table spaces in shadow table environments and Restoring table spaces in shadow table environments.

Shadow table statistics

Keeping table statistics, including shadow table statistics, up-to-date is essential for query performance. To keep your table statistics up-to-date, use one of the following approaches:

Enable automatic statistics collection. For more information, see Configuring your DB2 server for shadow tables.
Manually update your table statistics by using the RUNSTATS command. For more information, see Collecting statistics in shadow table environments.

Table and index reorganization

This activity can increase the efficiency with which the database manager accesses your tables. Use one of the following approaches:

Enable automatic reorganization of tables to reclaim space that was used by extents for stored data. If you use this approach, you do not have to worry when and how to reorganize your data. For more information, see Configuring your DB2 server for shadow tables.
Manually reorganize tables and indexes by using the REORG TABLE command with the RECLAIM EXTENTS option. For more information, see Manually reorganizing tables in shadow table environments.

Table operations that interrupt InfoSphere CDC replication

InfoSphere CDC maintains and depends on metadata that describes source tables, shadow (target) tables, and columns that are being replicated. If the source or target table structures change as a result of a data definition language (DDL) statement being issued, these changes interrupt replication because InfoSphere CDC can no longer read the database log records during mirroring due to the discrepancy with the existing metadata. In addition, any operation that triggers and error can also interrupt InfoSphere CDC replication.

Review the following list of table operations that can interrupt InfoSphere CDC replication and the proper actions that you can perform to minimize this interruption for each operation:

Altering the structure of a source table: Issuing the ALTER TABLE ADD COLUMN statement on a source table interrupts replication. To safely perform these DDL operations without disrupting replication, follow the instructions in Performing table operations on source tables that can interrupt InfoSphere CDC replication.
Altering the logging properties of a source table: Issuing the ALTER TABLE DATA CAPTURE NONE or ALTER TABLE ACTIVATE NOT LOGGED INITIALLY statement on a source table results in InfoSphere CDC not being able to detect any changes to source tables. InfoSphere CDC requires that DATA CAPTURE CHANGES is enabled on all source tables. You should not issue these statements on source tables.
Dropping a source or shadow table: Issuing the DROP TABLE statement on a source or shadow table for which replication is active returns an InfoSphere CDC error and stops replication. Before you drop a source or shadow table, stop replication, delete the table mapping for that table, and restart replication. For more information, see To delete a table mapping.
Updating a unique key: During the InfoSphere CDC apply phase, updating a unique key might cause the uniqueness check on a shadow table to fail. The reason is that the updates from the DB2 logs and the uniqueness checks are applied one row at a time instead of performing deferred uniqueness checking. If the update of unique key fails, you must perform a refresh of the shadow table and restart replication. For more information, see Restarting replication when table operations cause replication to end.
Loading data to a source table: Issuing the LOAD command on a source table when replication is active makes the subscription fail and stops replication. Populating a source table with the LOAD command also stops replication because the data for this operation is not logged and replication to the shadow table is not possible. To load the data to your source table with minimal impact to replication, follow the steps in Performing table operations on source tables that can interrupt InfoSphere CDC replication.
Updating shadow tables by InfoSphere CDC: While replication is active and mirroring is in progress, if InfoSphere CDC fails to update a shadow table because the database manager returns an error, you must perform a refresh of the shadow table and restart replication. For more information, see Restarting replication when table operations cause replication to end.

Unsupported table operations on source tables of shadow tables

The following table operations are unsupported on a source table that has a shadow table:

ALTER TABLE RENAME column-name
ALTER TABLE DROP column-name
ALTER TABLE ALTER COLUMN column-name
RENAME TABLE
ADMIN_MOVE_TABLE
db2convert

If you try to perform any of these operations, an error is returned.

Before you apply any of these operations to a source table, you must end replication and drop the associated shadow table. You can then apply the action to the source table. Next, re-create the shadow table, and then (unless the operation was to issue the RENAME TABLE statement), refresh the InfoSphere CDC table mapping for the shadow table. If the operation was to issue the RENAME TABLE statement, instead of refreshing the table mapping, delete it and re-create it, specifying the new source table name.

To apply any of these operations to a source table, follow the guidelines in Performing unsupported table operations on source tables that have shadow tables.