DB2 10.5 for Linux, UNIX, and Windows

Shadow tables in HADR environments

The use of shadow tables is supported for high availability disaster recovery (HADR) environments. However, there are a number of considerations for ensuring that latency-based routing is available in the event of a failover.

For an HADR setup, you install and configure InfoSphere® CDC on both a primary server and on the standby server. The InfoSphere CDC instance is active on the primary server and passive on the standby. HADR replicates data from the primary to the standby, including both source and shadow tables.

With InfoSphere CDC, there are two types of metadata that are used to synchronize the source and target tables. The operational metadata, which is information such as the instance signature and bookmarks, is stored in the database and is replicated by HADR to the standby servers. The configuration metadata, which is information such as subscriptions and mapped tables, is stored in the cdc-installation-dir, so it is not replicated by HADR. Therefore, after you implement shadow tables in an HADR environment, any configuration changes that are made to the shadow tables on the primary are not reflected on the standby server.

You propagate configuration metadata changes by employing a scheduled InfoSphere CDC metadata backup-and-copy over the standby servers by using the dmbackupmd command. You might also want to apply those changes to the standby servers immediately after they take place. For example, after any applying DDL statements that change the source row-organized table, the target shadow table, or both.

After a failover or role switch occurs and the HADR primary role has moved to a host where InfoSphere CDC was previously passive, you must manually start InfoSphere CDC on the new primary node. If the configuration metadata is not synchronized, you have to reapply any changes that occurred on the old primary server.

Figure 1 shows an example of an HADR environment that uses shadow tables and shared log archive.

Figure 1. HADR environment with shadow tables

HADR environment with a InfoSphere CDC instance on a primary and standby server

Requirements

Use the same InfoSphere CDC version on all servers: Using the same InfoSphere CDC version on all the HADR servers helps prevent any potential incompatibilities with metadata between different versions. You can use a rolling update strategy to upgrade the InfoSphere CDC version. Unlike the standard HADR rolling update procedure, you upgrade InfoSphere CDC on the primary host first. For more information about the actual upgrade procedure, see Upgrading InfoSphere CDC for DB2 for LUW. After confirming that you can start mirroring successfully, stop on the primary, perform a role switch, upgrade InfoSphere CDC on the new primary, and start to verify that you can start mirroring successfully. With both InfoSphere CDC instances upgraded, you can then perform another role switch to return to your original topology.
Use a shared log archive: As part of this initial setup, you should configure a shared log archive. When using HADR with shadow tables, a shared archive is even more essential because you need to ensure that the InfoSphere CDC log capture process has access to all of the DB2 log files (including the ones that are not yet scraped) and the likelihood that InfoSphere CDC invokes log retrieve after failover is very high. For more information, see the following topic in the Related concepts section: "Log archiving configuration for DB2 high availability disaster recovery (HADR)".

Best practices

Keep a change log

It is also advisable to keep a change log of any DDL changes which affect the InfoSphere CDC configuration metadata. If there is an outage on the primary and you must fail over to the standby database, the configuration changes might not have been propagated to the standby yet, so you will need to manually redo those configuration changes. A change log makes reapplying those changes much easier.

If you are not able maintain such a change log, there several options to ensure that all of your shadow tables can be utilized after a failover event.

If you already know the list of row-based tables and the replicated shadow tables, compare that list with the InfoSphere CDC table-mappings to figure determine if there are any of the following tables:
- shadow tables that do not have a corresponding table mapping
- tables that are parked
You can then re-create any missing shadow table mappings.
Similarly, you can examine the row-based and shadow table definitions to identify if there are any changes (such as a new column added to the base row-table) and use the procedure described in the "Performing table operations on source tables that can interrupt InfoSphere CDC replication" topic to make the require InfoSphere CDC metadata changes.

If you already have a script, for example one that uses the MON_GET_TABLE table function to monitor if shadow tables are used, you can use it to identify which shadow table are not being routed to after the failover to the new primary completes. You can compare the InfoSphere CDC table mappings to that list of shadow tables which are consistently not having queries routed to them, and identify which mappings are missing.

If you are not sure which tables are shadow tables, you can query the PROPERTY field in the SYSCAT.TABLES system catalog view. For example, the following query returns the table name and the table schema name for all the shadow tables in the database:

select tabname, tabschema from syscat.tables where substr(property,23,1)='Y'

Similar to an earlier suggestion, you can compare this information with the existing InfoSphere CDC table-mapping listing, and create any missing InfoSphere CDC table mappings (after failover to the new primary) or use it to handle any other CDC table metadata changes.

Use a separate server to run IBM® InfoSphere CDC Access Server

From an operational point of view, it simplifies things if you use a separate access server to manage the datastores when shadow tables are deployed in a HADR configuration. With this topology, you can use the same server to monitor the InfoSphere CDC instance on all HADR nodes (that is, on whatever physical node is currently the primary and is running the active InfoSphere CDC instance). Moreover, this server is not affected by a physical failure of a monitored node because the monitoring server is not collocated.

Restrictions

Routing to shadow tables is not available on HADR standby servers.