Implementing shadow tables in HADR environments

Implement shadow tables in HADR environments to get the performance benefits of BLU Acceleration for analytic queries in OLTP environments.

Before you begin

Ensure that you are familiar with the software architecture for shadow tables. For more information, see IBM InfoSphere Change Data Capture software architecture and concepts for shadow tables.
Ensure that you are familiar with the concepts related to the IBM® InfoSphere Change Data Capture (InfoSphere CDC) software. For more information, see IBM InfoSphere Change Data Capture software architecture and concepts for shadow tables.
Ensure that you are familiar with the physical design guidelines for shadow tables. For more information, see Physical design for shadow tables.
Ensure that you are familiar with the topology for shadow tables in HADR environments. For more information, see Shadow tables in HADR environments.

About this task

Shadow tables are implemented as materialized query tables (MQTs) that are maintained by replication. IBM InfoSphere Change Data Capture for Db2® (InfoSphere CDC), a component of IBM InfoSphere Data Replication, maintains shadow tables. To implement shadow tables in your HADR environment, you must set up and configure all software components and replication, create the shadow tables, start replication to populate the shadow tables with data, and enable query routing to shadow tables.

Procedure

To implement shadow tables in HADR environments:

Ensure that you meet the Db2 requirements to support shadow tables.
Configuration settings for shadow tables that are deployed in a predominant OLTP workload are important as they differ from the settings for pure analytics workload. For more information, see Db2 server configuration for shadow tables.
Configure your Db2 server to implement shadow tables in the HADR primary and all the standby servers.
Shadow tables require specific parameter settings in the Db2 database where you want to create shadow tables. For more information, see Configuring your Db2 server for shadow tables.
Ensure that you meet the installation and configuration requirements for InfoSphere CDC components.
For more information, see Preparing to install IBM InfoSphere Change Data Capture software.
Install IBM InfoSphere Change Data Capture software.
You must install InfoSphere CDC for Db2 in the HADR primary and all the standby servers. Ensure that you use the same location for the cdc-installation-dir directory on all the servers. On UNIX and Linux® platforms, use a user ID which has same cdc-user ID and group ID values as the HADR primary server on the standby servers. For more information, see Installing IBM InfoSphere Change Data Capture for shadow tables.
Configure the InfoSphere CDC software for HADR environments.
For more information, see Configuring InfoSphere CDC with shadow tables in HADR environments.
Create the SYSTOOLS.REPL_MQT_LATENCY table only in the HADR primary.
This table is used to share latency information between InfoSphere CDC and the Db2 database manager. For more information, see Creating the SYSTOOLS.REPL_MQT_LATENCY table.
Create shadow tables in your database only in the HADR primary.
For more information, see Creating shadow tables
Set up replication for shadow tables in one of the following ways:
- Setting up replication for shadow tables with IBM InfoSphere CDC Management Console
- Setting up replication for shadow tables with CHCCLP
Replicate data to shadow tables in one of the following ways:
- Replicating data to shadow tables with IBM InfoSphere CDC Management Console
- Replicating data to shadow tables with CHCCLP
If you are setting up a new HADR environment, perform the following actions:
1. Stop replication on the HADR primary server using the following system command:
```
dmendreplication -I cdc-instance-name -c -s cdc_subscription_name 
```
2. Stop the InfoSphere CDC instance on the HADR primary server using the following system command:
```
dmshutdown -I cdc-instance-name 
```
3. Initialize HADR.
  For more information about initializing HADR, see Initializing high availability disaster recovery (HADR).
Copy the entire cdc-installation-dir/instance directory from the HADR primary server to all standby servers. This directory contains all the configuration metadata from the InfoSphere CDC instance on the primary.
You can copy this directory by creating a tar or compressed archive out of the instance folder, secure copying (using the scp command) the archive to the standby servers, and extracting the archive into the standbys' cdc-installation-dir/instance directories.
Enable query routing to shadow tables only in the HADR primary in one of the following ways:
- If you want automatically enable query routing for individual applications, customize the connect procedure for shadow tables. Update the connect_proc database configuration parameter in all servers. For more information, see Customizing the connect procedure for shadow tables.
- If you want to manually enable query routing after making a database connection, manually enable shadow table support and query routing. For more information, see Manually enabling shadow table support and query routing.
For more information about enablement of query routing, see Enablement of query routing to shadow tables.

What to do next

Verify that your setup for shadow tables and InfoSphere CDC in an HADR environment is functional and that the standby is able to take over for the primary in the event of an outage by following the procedure described in Switching HADR roles with shadow tables. After you are satisfied that the shadow tables are functional on the new primary, perform another role switch to revert to the original topology.

As well, after you implement shadow tables, ensure that you maximize the performance benefits by tuning their performance. For more information, see Performance tuning for shadow tables.