SET CURRENT QUERY ACCELERATION WAITFORDATA statement

Invocation for SET CURRENT QUERY ACCELERATION WAITFORDATA

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization for SET CURRENT QUERY ACCELERATION WAITFORDATA

None required.

Description for SET CURRENT QUERY ACCELERATION WAITFORDATA

nnnn.m

Is a DECIMAL(5,1) numeric-constant value that specifies the maximum number of seconds that the accelerator will delay a query while the accelerator waits for the replication of committed Db2 data changes that occurred prior to Db2 running the query.

You can specify a value in the range of 0.0–3600.0 seconds. For example, a value of 20.0 represents 20.0 seconds (or 20000 milliseconds), and a value of 30.5 represents 30.5 seconds (or 30500 milliseconds). The maximum value of 3600.0 means that the query is delayed for 60 minutes.

The default value is 0.0, which means that the query is not delayed and is run immediately on the accelerator. Other WAITFORDATA behavior is not applied to the query.

The wait time value can also be specified as an INTEGER numeric constant value in the range 0–3600 seconds, which Db2 will convert to a DECIMAL (5,1) value.

variable

Is a language host-variable or SQL variable that has a data type of DECIMAL(5,1) or other data type that is assignable to DECIMAL(5,1). Any value that is specified for variable must conform to the specifications for nnnn.m. Additionally, variable cannot be a global variable.

Notes for SET CURRENT QUERY ACCELERATION WAITFORDATA

The precedence order (lowest to highest) for setting the value of the special register is as follows:

• The QUERY_ACCEL_WAITFORDATA subsystem parameter
• The ACCELERATIONWAITFORDATA bind option, if specified
• An explicit SET CURRENT QUERY ACCELERATION WAITFORDATA statement

The WAITFORDATA special register is used only for dynamic queries and applies only when query acceleration behavior is also requested by using the CURRENT QUERY ACCELERATION special register. The WAITFORDATA special register is not applied to accelerated queries that reference only accelerator-only tables (AOTs).

The delay wait time begins when the query reaches the accelerator, not when the query starts running in Db2. For more information about how to determine appropriate WAITFORDATA delay time values for query acceleration with replication in your environment, see the IBM® Db2 Analytics Accelerator for z/OS® documentation for Hybrid Transactional and Analytical Processing (HTAP) and the WAITFORDATA feature.

Setting CURRENT QUERY ACCELERATION WAITFORDATA to a value greater than 0 specifies that Db2 and the accelerator will apply WAITFORDATA delay behavior and restrictions to all dynamic queries to be accelerated afterward. The following behaviors, requirements, and restrictions apply to using WAITFORDATA to delay queries:

• When Db2 sends a query to the accelerator, Db2 also sends the specified WAITFORDATA delay wait time and an internal value that represents the latest committed Db2 data change on the entire Db2 subsystem (for a data sharing environment, across all the Db2 subsystems of the data sharing group) at the time Db2 sends the query to the accelerator. The committed data change might or might not be related to an accelerated table that is referenced in the query, but the internal value that represents this committed change is still used for the query that is accelerated.
• If the specified WAITFORDATA delay wait time expires before the expected committed Db2 data change is replicated to the accelerator, the accelerator will fail the query and issue SQLCODE -904 accompanied by additional message information.

  If the delay wait time expires on the first OPEN for the query in the current Db2 unit of work, the failing query qualifies for failback to Db2 if CURRENT QUERY ACCELERATION has been set to ENABLE WITH FAILBACK.

  This default WAITFORDATA delay wait time expiration behavior can be changed by modifying the accelerator configuration settings for replication. For information about changing the default WAITFORDATA delay wait time expiration behavior, see the WAITFORDATA information in the IBM Db2 Analytics Accelerator for z/OS documentation.

• All accelerated Db2 tables referenced in the query must be subscribed to and enabled for replication to the target accelerator. If all accelerated tables in the query do not meet this requirement, the accelerator will fail the query and issue SQLCODE -904 accompanied by additional message information.

  If this failure occurs on the first OPEN for the query in the current Db2 unit of work, the failing query qualifies for failback to Db2 if CURRENT QUERY ACCELERATION has been set to ENABLE WITH FAILBACK.

  This default WAITFORDATA behavior can be changed by modifying the accelerator configuration settings for replication. For information about changing this default WAITFORDATA behavior requirement, see the WAITFORDATA information in the IBM Db2 Analytics Accelerator for z/OS documentation.

• WAITFORDATA behavior is dependent on the replication process. Therefore, accelerated queries can be directly affected by current replication status, function, and performance, possibly resulting in the accelerated query failing on the accelerator with SQLCODE -904 if replication is not functioning or performing properly.
• In the following situations, Db2 changes that are committed before the query is sent to the accelerator will not be available to the query when it is run on the accelerator, even if WAITFORDATA delay behavior is requested for the query:
  • The query specifies a Db2 accelerated table, but the same Db2 unit of work includes a previous uncommitted Db2 change that will not be available to the query when it is run on the accelerator. In this situation, accelerator WAITFORDATA behavior cannot be achieved for the query, and the query will not be accelerated. The Db2 change might or might not be related to the Db2 accelerated table that is referenced in the query.
  • The query specifies a Db2 accelerated table, but the same Db2 unit of work includes a previous uncommitted accelerator-only table (AOT) change. This uncommitted change resulted in the creation of an accelerator database snapshot isolation (SI) for this unit of work before the query was run; therefore, accelerator WAITFORDATA behavior cannot be achieved for the query, and the query will not be accelerated. This accelerator database SI can prevent committed and replicated Db2 changes, made by either this transaction or by a different transaction, from being available to the accelerated query, even if the Db2 changes are replicated to the accelerator before the query is run there.

  For these situations, Db2 will not accelerate the query but instead will run it only in Db2, if possible. If the QUERY ACCELERATION behavior requested does not allow the query to be run only in Db2 or if the query also references an AOT, then Db2 will fail the query and issue SQLCODE -4742. The default WAITFORDATA behavior for these situations can be changed only by modifying the transaction application with the changes that are described in the associated reason codes in SQLCODE -4742.