This Db2 for z/OS News from the Lab blog entry was originally published on 2018-02-19.

By Roy Smith and Eric Radzinski

Due to limitations with existing analytics technology, the standard approach to data analytics has been to mine insights from data that has been copied to a data warehouse or similar type of repository. This approach introduces data latency, which prevents organizations from basing critical business decisions on real-time data and sometimes causes them to make inaccurate decisions. Additionally, because there's no guarantee that the real-time transactional data in the database is fully synchronized with the analytical data in the warehouse, a transactional query that you run on the database can yield different results when you run that same query as an analytical query on the data warehouse.

For many users, it doesn't matter if a particular query runs on the database or on the data warehouse. However, for a certain set of users, it's critical that the results of their analytical queries, which are based on the data in their warehouse, are the same as the results of their real-time transactional queries, which are based on data in the database. One solution to achieving consistent transactional and analytical query results is to provide a perspective in which a single system is used to run both real-time transactional queries and analytical queries, and the analytical queries are accessing real-time data even though that data is in a data warehouse. Gartner refers to this perspective as Hybrid Transactional/Analytical Processing (HTAP). Db2 for z/OS with IBM Db2 Analytics Accelerator for z/OS now provides such an HTAP perspective.

Db2 for z/OS with IBM Db2 Analytics Accelerator for z/OS's newly introduced HTAP solution, currently available as a non-GA Technical Preview, involves Db2 11 for z/OS as the database for the transactional data, IBM Db2 Analytics Accelerator V5.1 (IDAA) as the data warehouse/repository for the analytical data, and IBM InfoSphere Change Data Capture (CDC) for z/OS V10.2.1 to replicate the transactional data from the Db2 database to the Analytics Accelerator. Our HTAP solution opens the door to a valuable new dimension of data analytics by enabling organizations to easily mine their live mainframe transactional data in real time and with no latency. The goal of our HTAP solution is to eliminate a perceived latency between the replication of transactional data to the accelerator and analytical queries that run on the accelerator. Queries that run on an accelerator will see all the changes that were committed in Db2 at the time the query was started. From a user's perspective, their transactional and analytical queries run on a single system where it seems that "real-time" analytical processing on "real-time" data occurs even for queries that run on an accelerator. Replication latency no longer impacts SQL result consistency.

The competitive advantage of our HTAP solution over other HTAP solutions is our Db2 for z/OS and IDAA heterogeneous scale-out, which is crucial for enterprise workloads. Heterogeneous scale-out means that the transactional and analytical workloads do not compete for the same processing resources (for example, as is the case with SAP Hana), which is a major bottleneck once the workloads reach enterprise scale. Db2 for z/OS with IDAA for z/OS is the only HTAP solution in the market that implements heterogeneous scale out.

With the Db2 for z/OS HTAP solution, the ability to analyze real-time transactional data is achieved by delaying an accelerated query until the latest known committed Db2 data changes have been replicated to the accelerator. Only after these committed Db2 changes have been replicated will the accelerator run the query. For accelerated queries, the specified delay time begins when the query reaches the accelerator, not when it starts running in Db2. If the specified delay time expires before the expected data is committed, the accelerator fails the query and issues a -904.

We've put together a short video that does an excellent job of explaining the different ways that an analytic query flows through an HTAP environment.

To enable an HTAP environment, Db2 provides a new CURRENT QUERY ACCELERATION WAITFORDATA special register that specifies the amount of time that the accelerator should wait before it attempts to run an accelerated dynamic query. While the accelerator is waiting, the committed transactional data is being replicated from Db2 to the accelerator. As soon as the transactional data is available on the accelerator, the accelerator runs the query.

At this time, Db2 for z/OS HTAP functionality is available as a Technical Preview in Db2 11 for z/OS for dynamic queries only via a pair of Db2 11 for z/OS APARs (PI83286 and PI83288) that are packaged as a single PTF, UI51280, which contains:

• The new CURRENT QUERY ACCELERATION WAITFORDATA special register that allows you to control how long, if at all, a query is delayed so that committed Db2 changes can be replicated to the accelerator
• The new SET CURRENT QUERY ACCELERATION WAITFORDATA SQL statement that you use to set the new special register
• The new QUERY_ACCEL_WAITFORDATA Db2 for z/OS subsystem parameter

Note that Db2 11 for z/OS HTAP functionality also requires IBM Db2 Analytics Accelerator V5.1 for z/OS APAR PI87839 (and its related APARs) and Change Data Capture (CDC) V10.2.1 for z/OS APAR PI79094. See What's new with IBM Db2 Analytics Accelerator for z/OS V5.1 for detailed information about these required APARs.

For more information about Db2 for z/OS's HTAP capabilities, check out the following resources:

• Making queries wait for incremental updates
• SET CURRENT QUERY ACCELERATION WAITFORDATA

Roy Smith is an Advisory Software Engineer in Db2 for z/OS development at the IBM Silicon Valley Laboratory in San Jose, California. Eric Radzinski is a technical writer for Db2 for z/OS at the IBM Silicon Valley Laboratory in San Jose, California.