September 5, 2014 Leave a comment
Bet you have been hearing a lot about shadow tables in DB2 “Cancun Release” lately. Umm… do shadow and Cancun remind you of On the beach by Cliff Richards and the Shadows? Seriously, DB2 shadow tables can make you dance to a rock ‘n’ roll on the beach because you will be trouble free with real-time insights into your operations and of course, lots of free time.
What is a shadow table?
Shadow tables have been around since the beginning of modern computing – primarily for improving performance. So what does the DB2 shadow table offer? The best of both OLTP and OLAP worlds! You can now run your analytic reports directly in OLTP environment with better performance.
Typically organizations have separate OLTP and OLAP environments – either due to resource constraints or to ensure the best OLTP performance. The front-end OLTP is characterized by very small, but high volume transactions. Indexes are created to improve performance. In contrast, the back-end OLAP has long-running complex transactions that are relatively small in number. Indexes are created, but they may be different from OLTP indexes. Of course, an ETL operation must transfer data from OLTP database to OLAP data mart/warehouse at time intervals that may vary from minutes to days.
DB2 can help you simplify your infrastructure and operations with shadow tables. Shadow table is a column organized copy of a row-organized table within the OLTP environment, and may include all or a subset of columns. Because the table is column organized, you get the benefit of enhanced performance that BLU Acceleration provides for analytic queries.
How do shadow table work?
Shadow table is implemented as a materialized query table (MQT) that is maintained by replication. IBM InfoSphere Change Data Capture for DB2, available in advanced editions, maintains shadow tables through automatic and incremental synchronization of row-organized tables.
While all applications can access the row-organized table by default, DB2 optimizer will perform the latency-based routing to determine whether a query needs to be routed to shadow tables or the row-organized source.
A truly flexible and trouble-free OLTP world
Shadow tables offer the incredible speed you have come to expect from BLU Acceleration while the source tables remain row-organized to best suit OLTP operations. In fact, with shadow tables, the performance of analytical queries can improve by 10x or more, with equal or greater transactional performance*.
With instant insight into “as it happens” data for all your questions and all the free time you’ll have with no more indexing/tuning, what’s not to like? Try DB2 today
* Based on internal IBM testing of sample transactional and analytic workloads by replacing 4 secondary analytical indexes in the transactional environment with BLU Shadow Tables. Performance improvement figures are cumulative of all queries in the workload. Individual results will vary depending on individual workloads, configurations and conditions.