Shadow tables: A Cure for Reporting Headaches on Transaction Systems!

I’m really excited about the DB2 10.5 “Cancun Release”, in particular with the Shadow tables feature. Based on my experiences with customers who have mixed workloads (transaction and analytic) on a transactional system, one of the big performance pain points is when the transaction tables are also used for business intelligence analytics. Reports run on these tables do not run very well and often are a cause of blocking the transactional queries. As a result, care has to be taken in scheduling reports at a time when no transactional workload is taking place. This of course is challenging with the 24×7 requirements for many of today’s databases. The obvious question then, is, why not replicate data from the transaction system to a separate data warehouse or data mart for running analytic queries? Well, there are two primary objections to this from many customers:

1. There is an increased business need to run real-time reporting and analytics directly on the transactional data.
2. Businesses don’t need the hassle of building and administering a separate warehouse for reporting. They would rather keep their infrastructure simple by having reporting and transactions in one DB2 system.

Now, enter the DB2 10.5 “Cancun Release” and these objectives become a reality with the use of Shadow tables. These tables are columnar versions of the row based transactional tables. This means that they can leverage the power of BLU Acceleration which results in fast reporting (https://www.triton.co.uk/db2-10-5-addresses-big-data-challenges-with-blu-acceleration/). The DB2 optimiser automatically routes transactional queries to the row tables, whilst analytic queries are sent to the columnar shadow tables. This results in fast reporting directly on the transactional system without the performance impact to the transactional workload. The need to replicate data into a separate data warehouse or data mart is also eliminated.

Our initial results on using shadow tables for reporting show significant improvement in response times. So, watch this space. Meanwhile, we recommend you prescribe to this new and exciting cure for reporting headaches on your transactional systems.