Shadow tables

A shadow table is a column-organized copy of a row-organized table that includes all columns or a subset of columns. Shadow tables are implemented as materialized query tables (MQTs) that are maintained by replication.

Using shadow tables, you can get the performance benefits of BLU Acceleration for analytic queries in an online transaction processing (OLTP) environment. Analytical queries against row-organized tables are automatically routed to shadow tables if the replication latency falls within a user-defined limit.

BLU Acceleration enhances the performance of complex queries through a column-organized architecture. By combining this enhanced performance for complex queries with the efficiency of row-organized tables for OLTP queries, you can use shadow tables to capitalize on the best of both worlds.

Shadow tables are maintained by IBM® InfoSphere® Change Data Capture for DB2® for Linux, UNIX, and Windows (InfoSphere CDC), a component of the InfoSphere Data Replication product. InfoSphere CDC asynchronously replicates DML statements that are applied on the source table to the shadow table. By default, all applications access the source tables. Queries are automatically routed to the source table (row-organized) or the shadow table (column-organized copy of the source table) by using a latency-based algorithm that prevents applications from accessing the shadow table when the latency is beyond the user-defined limit.

Shadow tables improve analytic query performance in OLTP environments without having to add indexes for this purpose. Typically, OLTP environments and OLAP environments are maintained separately. A regular ETL operation moves data from the OLTP environment to the OLAP environment. Using shadow tables, you can combine these two environments into a single environment for both OLTP and OLAP query performance. You can use shadow tables in any scenario where you want to keep the source table row-organized while you take advantage of the performance enhancements that BLU Acceleration provides.