HADR and Shadow tables – a good match, but with some considerations!

When DB2 BLU columnar architecture was introduced in DB2 10.5 GA, it was not possible to create a columnar table in a database which is the primary database in an actively connected HADR pair. This prevented a true DR solution. However, from Fixpack 4 HADR now supports columnar tables. A column-organized table created in an active HADR primary database gets replicated successfully to the standby database.

In DB2 Fixpack 4, shadow tables were introduced. These are actually column-organized MQT tables (populated from the row-organized version using Infosphere Change Data Capture/CDC software). For HADR, a shadow table is treated like any other column-organized table; hence HADR will normally replicate updates to shadow tables from the Primary to the Standby. The concept is simple and easy to use.

However, you have to take into account an extra consideration when deploying shadow tables in an HADR environment. One separate CDC instance is required on the Primary and on each of the Standby servers (if multiple standbys are used). On the Primary server, the CDC instance is active to replicate the changes from the row-organized tables to the shadow tables. Since the shadow tables are maintained by HADR on the Standby, the CDC instance should not be activated on any Standby server.

Now, CDC has a component called Access Server which can be installed on a Linux, UNIX or Windows server and instances/subscriptions are managed through a management console GUI client on the Windows box. All the CDC instances (primary and all the standbys) in an HADR setup are linked to a common CDC Access Server and a common management console. It is recommended to install the CDC Access Sever on a separate system (outside the HADR cluster) so that it is not impacted on failover. Shared log archiving between the Primary and Standby is required to ensure that CDC log capture has access to all of the log files on failover. After a failover or role switch, and the HADR primary role is active on the Standby server, CDC instance needs to be manually started to maintain the shadow tables on the new Primary.

With CDC there are two types of metadata that are used to synchronize the source (row-organized table) and target (shadow) 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 all the standbys.
• The configuration metadata (which is information such as subscriptions and mapped tables) is stored in the CDC home directory which is outside of the database, so it is not replicated by HADR.

After you have set up shadow tables with HADR, any configuration metadata changes on the Primary also need to be reflected on all the Standby servers (CDC provides a dmbackup command to backup the configuration metadata on the Primary to be copied over to the Standby).

Our latest ebook – Shadow Tables; Bringing Analytics to Data is now available to download.

Find out more about Database Availability