DB2 10.5 for Linux, UNIX, and Windows

Implementing shadow tables

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

Before you begin

About this task

Shadow tables are implemented as materialized query tables (MQTs) that are maintained by replication. IBM InfoSphere Change Data Capture for DB2® for LUW (InfoSphere CDC), a component of IBM InfoSphere Data Replication, maintains shadow tables. To implement shadow tables in your 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:

  1. 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.
  2. Configure your DB2 server to implement shadow tables. 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.
  3. Ensure that you meet the installation and configuration requirements for InfoSphere CDC components. For more information, see Installation requirements for IBM InfoSphere Change Data Capture for shadow tables.
  4. InstallIBM InfoSphere Change Data Capture software for shadow tables. For more information, see Installing IBM InfoSphere Change Data Capture for shadow tables.
  5. Configure the InfoSphere CDC software that you installed. For more information, see Configuring IBM InfoSphere Change Data Capture software for shadow tables.
  6. Create the SYSTOOLS.REPL_MQT_LATENCY table. 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.
  7. Create shadow tables in your database. For more information, see Creating shadow tables
  8. Set up replication for shadow tables in one of the following ways:
  9. Replicate data to shadow tables in one of the following ways:
  10. Enable query routing to shadow tables in one of the following ways: For more information about enablement of query routing, see Enablement of query routing to shadow tables.

What to do next

After you implemented shadow tables, ensure that you maximize the performance benefits by tuning their performance. For more information, see Performance tuning for shadow tables.