IBM Support

WAITFORDATA queries and table types in Db2 Analytics Accelerator

How To


Summary

Db2 Analytics Accelerator together with Db2 for z/OS enable an industry-unique hybrid transactional and analytic processing (HTAP) environment that enables users to use the high-value, sensitive data that originates on IBM Z, in near real-time. This enterprise-grade HTAP approach is a unique architecture that is designed to deliver industry-leading performance for mixed workloads. The analytical and transactional queries are each processed in separate compute resource pools, enabling you to run analytic queries any time, without impacting the performance of your transactional systems.

Objective

The WAITFORDATA option has been designed to add Hybrid Transactional Analytical Processing (HTAP) capabilities to the Accelerator. It allows you to run queries on the Accelerator that return results as if the query ran on Db2 for z/OS mainline, that is, it will synchronize query and replication tasks such that the query is guaranteed to run on committed results as of the time the query arrived in Db2 z/OS and hence compensates for any replication latency that might exist.

Environment

WAITFORDATA can be specified as a:
  • Special register (CURRENT QUERY ACCELERATION WAITFORDATA)
  • ZPARM (QUERY_ACCELERATION_WAITFORDATA)
  • Bind option (ACCELERATIONWAITFORDATA)
That can be used for queries against incrementally updated tables also known as replicated tables. It effects a hold on the query execution until the most recent updates (changes committed in Db2 for z/OS) have been applied to the tables referenced by the query.
However, a query might reference replicated tables as well as tables that have not been enabled for incremental updates (loaded tables that can be referenced by accelerated queries) and accelerator-only tables (AOTs). If WAITFORDATA is set to a value greater than 0.0 and a query accesses loaded tables (non-replicated tables), they fail with SQL code -904.
By default, non-replicated tables and AOTs are excluded from the WAITFORDATA queries because there is a greater probability of data inconsistency. If changes have been made to a Db2 for z/OS source table, a new load process must be started to propagate these changes to the accelerator. Only then will these changes be reflected in accelerated queries.
It is possible to change this behavior by a definition change in the Analytics Accelerator Console and include non-replicated tables and AOTs in queries that reference replicated tables. This is recommended if you know that queries go against a mixed set of tables, and if you keep non-replicated tables consistent by loading them regularly as required by your business.
It is important to know that non-replicated tables (loaded and AOT) can be included in HTAP queries like replicated tables. The workload may access to a set of mixed tables, but it is possible that only a subset of the  tables are incrementally updated and HTAP is required for this workload. In that case, queries should not fail, and keep running.

Steps

Details regarding the inclusion and exclusion of tables can be found by following this link.

https://www.ibm.com/docs/en/daafz/7.5?topic=updates-including-excluding-loaded-tables-from-waitfordata-queries

For instance, If QUERY_ACCEL_WAITFORDATA is set to a value greater than zero as a ZPARM, all the queries, including queries against replicated tables, non-replicated or AOT tables, would be candidates for HTAP. It would not matter if the query went against replicated tables only or against a combination of all table types. The following table describes the query behavior for the different inclusion settings, exclusion settings, and the accessing of mixed table types. Keep the following in mind while reading the table:

- QUERY_ACCEL_WAITFORDATA > 0.0
- T1 is enabled for replication
- T2 is a non-replicated, loaded table
- T3 is an AOT
- Each line represents an individual query execution

For instance:  In line 2 of the table below, the following script fails. T2 is a non-replicated table. The global setting is to  exclude such tables, and WAITFORDATA is set to a value > 0.0.

SET CURRENT QUERY ACCELERATION WAITFORDATA = 10;
SELECT * FROM T2 FOR READ ONLY;

On the contrary, the following script will work for line 8. The global setting is to include this table type. Thus, it does not limit query execution.

SET CURRENT QUERY ACCELERATION WAITFORDATA = 10;
SELECT * FROM T1,T2,T3 FOR READ ONLY;

(I)nclude/(E)xclude Setting

Table

(W)ork/(N)ot Work

Comments

E

T1

W

E

T2

N

If QUERY_ACCEL_WAITFORDATA = 0.0, Query Works

E

T3

N

If QUERY_ACCEL_WAITFORDATA = 0.0, Query Works

E

T1, T2, T3

N

If QUERY_ACCEL_WAITFORDATA = 0.0, Query Works

I

T1

W

I

T2

W

I

T3

W

I

T1, T2, T3

W

Additional Information

Summary

If HTAP is considered for all queries that access an accelerator, this can be achieved by changing QUERY_ACCEL_WAITFORDATA to a value greater than zero in the ZPARM settings and by including the respective table type. It cannot be guaranteed that the data in non-replicated tables is up-to-date, but query execution will continue none the less. If changes have been made to a Db2 for z/OS source table, a new load process must be started to propagate these changes to the accelerator for non-replicated tables. Only then will these Db2 for z/OS changes be reflected by the accelerator data, and WAITFORDATA queries can be executed successfully.

Document Location

Worldwide

[{"Type":"MASTER","Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SS4LQ8","label":"Db2 Analytics Accelerator for z\/OS"},"ARM Category":[{"code":"a8m0z0000000741AAA","label":"Administration"},{"code":"a8m0z0000000741AAA","label":"Administration"},{"code":"a8m0z0000000741AAA","label":"Administration"}],"ARM Case Number":"TS008836835","Platform":[{"code":"PF035","label":"z\/OS"}],"Version":"7.5.0"}]

Document Information

Modified date:
05 April 2022

UID

ibm16567511

Page Feedback