A fix is available
APAR status
Closed as new function.
Error description
This apar provides new function for DB2 for z/OS V11
Local fix
N/A
Problem summary
**************************************************************** * USERS AFFECTED: All Db2 11 for z/OS users of QUERY * * ACCELERATION who want Hybrid Transactional / * * Analytical Processing ( HTAP ) * **************************************************************** * PROBLEM DESCRIPTION: APAR PI83288 is the main of two APARs * * that provide the new accelerator * * function "Hybrid Transactional / * * Analytical Processing" (HTAP) for * * existing feature Db2 QUERY ACCELERATION * * The companion APAR is PI83286 . * **************************************************************** * RECOMMENDATION: * **************************************************************** Certain users of accelerated queries want to retrieve the most recently committed data and changes from accelerator with very low latency, as a result of the recently committed 'real-time' changes to the data on Db2 for z/OS. These users want their query results from an accelerator to be the same or as close as possible to the 'real-time' query results from Db2 for z/OS. This requirement can be for both transactional and analytical queries, and gives an external view or perspective of having a single system for both transactional and analytical queries, where it seems that 'real-time' analytical processing on 'real- time' data occurs even when using an accelerator for executing queries. This perspective can be most effectively described as "Hybrid Transactional/Analytical Processing" (HTAP). . With the two APARs PI83288 and PI83286, Db2 11 for z/OS provides HTAP for QUERY ACCELERATION. This Db2 for z/OS support also requires IBM Db2 Analytics Accelerator V5 for z/OS APAR PI87839 (et al.) and Change Data Capture ( CDC ) for z/OS APAR PI79094 . Together, these three products provide HTAP support by way of a "delay protocol" that is used by the accelerator to delay running a query on an accelerator for a user-specified amount of time until the most recently committed Db2 data changes, current as of the time of the query's OPEN, have been replicated to the accelerator. . This new accelerator "delay protocol" introduces a new Db2 for z/OS special register, CURRENT QUERY ACCELERATION WAITFORDATA , that is set by the following SQL statement: . SET CURRENT QUERY ACCELERATION WAITFORDATA . Invocation ---------- This SET statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared. . Authorization ---------- None required. . Syntax ------ . SET CURRENT QUERY ACCELERATION WAITFORDATA = +-- nnnn.m ---+ | | +-- variable --+ . Description ----------- nnnn.m A DECIMAL(5,1) numeric-constant value that specifies the maximum wait time in seconds for the accelerator to delay a query, waiting for replication to the accelerator of committed Db2 for z/OS data changes that occurred prior to Db2 running the query. Additionally, Db2 and the accelerator will apply other WAITFORDATA behavior and restrictions to all queries for which acceleration is requested. Details about these behaviors and restrictions are documented below. . The decimal constant value ranges from 0.0 to a max of 3600.0, representing seconds. For example, 20.0 represents 20.0 seconds (or 20000 milliseconds), and 30.5 represents 30.5 seconds (or 30500 milliseconds). . The default value is 0.0, which means that the query is not delayed. It is run immediately on the accelerator, and other WAITFORDATA behavior is not applied to the query. . For ease of use, the wait time value can also be specified as an integer numeric-constant value, nnnn, ranging from 0 to 3600 seconds, which Db2 will implicitly convert to a DECIMAL(5,1) value. For example, 20 represents 20.0 seconds . variable A language host-variable or SQL variable of type DECIMAL(5,1) or other type that is assignable to DECIMAL(5,1). The value of 'variable' must meet the criteria described above where it represents seconds ranging from 0.0 to 3600. . 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 separate IBM Db2 Analytics accelerator documentation for HTAP and the WAITFORDATA feature. . The WAITFORDATA special register is used for dynamic queries, and applies only when query acceleration behavior is also requested using the related special register CURRENT QUERY ACCELERATION (see the SET CURRENT QUERY ACCELERATION statement). The WAITFORDATA special register is not applied to accelerated queries that 'only' reference accelerator-only tables (AOTs). . Setting CURRENT QUERY ACCELERATION WAITFORDATA to a value greater than 0 specifies that Db2 and the accelerator should apply WAITFORDATA delay behavior and restrictions to ALL dynamic queries to be accelerated afterwards. The following behaviors, requirements, and restrictions apply to using WAITFORDATA to delay queries: . - When Db2 sends a query to the accelerator, Db2 also sends with the query the specified WAITFORDATA delay wait time and a Db2 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 Db2 change might or might not be related to an accelerated table that is referenced in the query, but the Db2 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 existing SQLCODE -904 with new message text token indi- cating the WAITFORDATA time expired for the query. . If this timeout expiration failure occurs on the first OPEN for the query in the current Db2 unit of work, the failure qualifies for 'failback to Db2' when the query acceleration behavior ENABLE WITH FAILBACK is used. . For information about changing this default WAITFORDATA time- expiration failure behavior on the accelerator configuration, see separate IBM Db2 Analytics accelerator documentation for HTAP and the WAITFORDATA feature. . - WAITFORDATA behavior requires that all accelerated Db2 tables referenced in the query are also subscribed to and enabled for replication to the target accelerator. If all accelerated tables in the query do not meet this restriction, then the accelerator will fail the query and issue existing SQLCODE -904 with new message text token indicating that all tables in the query must be replicated. . If this failure occurs on the first OPEN for the query in the current Db2 unit of work, the failure qualifies for 'failback to Db2' when the query acceleration behavior ENABLE WITH FAILBACK is used. . For information about changing this default WAITFORDATA behavior restriction on the accelerator configuration, see separate IBM Db2 Analytics Accelerator documentation for HTAP and the WAITFORDATA feature. . - 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. . - There are certain scenarios where 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. These scenarios are as follows: . - The query specifies a Db2 accelerated table but the same Db2 unit of work (UoW) includes a previous uncommitted Db2 change that will not be available to the query when it is run on the accelerator, so accelerator WAITFORDATA behavior cannot be achieved for the query. 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 (UoW) includes a previous uncommitted accelerator-only table ( AOT ) change, which created an accelerator database "snapshot isolation" (SI) for this UoW before the query is run, so accelerator WAITFORDATA behavior cannot be achieved for the query. 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 scenarios under WAITFORDATA delay behavior, 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 existing SQLCODE -4742 with one of the new applicable reason codes, which are described below. This WAITFORDATA default behavior for these scenarios can be changed only by modifications to the transaction application, which are described later below in the Programmer Response for the new -4742 reason codes. . Notes: The new Db2 subsystem parameter QUERY_ACCEL_WAITFORDATA is used to provide an initial default value for new special register CURRENT QUERY ACCELERATION WAITFORDATA. The Db2 support for setting the new Db2 subsystem parameter is in companion APAR PI83286. See PI83286 APAR text for more information about specifying the QUERY_ACCEL_WAITFORDATA subsystem parameter and to read an important warning message from IBM about using this new subsystem parameter. ================================================================ . SQLCODE changes and updates --------------------------- I.The following new reason codes are for existing SQLCODE -4742: . 30 Accelerator WAITFORDATA behavior is requested but cannot be achieved for this query, so the query will not be accelerated. The query specifies a Db2 accelerated table but the same Db2 unit of Wwrk (UoW) includes a previous uncommitted Db2 change that will not be available to the query when run on the accelerator. The Db2 change might or might not be related to the Db2 accelerated table that is referenced in the query. . Programmer response: Either commit the previous Db2 change in the Db2 UoW before running the query to be accelerated, or do not specify an accelerator WAITFORDATA delay for this UoW. . . 31 Accelerator WAITFORDATA behavior is requested but cannot be achieved for this query that specifies both an accelerator-only Ttble (AOT) and a Db2 accelerated table, so the query will not be accelerated and cannot run in Db2. The same Db2 unit of work (UoW) includes a previous uncommitted Db2 change that will not be available to the query when run on the accelerator. The change might or might not be related to the Db2 accelerated table that is referenced in the query. . Programmer response: Either commit the previous Db2 change in the Db2 UoW before running the query to be accelerated, or do not specify an accelerator WAITFORDATA delay for this UoW. . . 32 Accelerator WAITFORDATA behavior is requested but cannot be achieved for this query. so the query will not be accelerated. The query specifies a Db2 accelerated table but the same Db2 unit of work (UoW) includes a previous uncommitted accelerator-only table (AOT) change, which created an accelerator database "snapshot isolation" (SI) for this UoW before the query is run. This accelerator database SI may 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. . Programmer response: Either commit the previous AOT change in the Db2 UoW before running the query to be accelerated, or do not specify an accelerator WAITFORDATA delay for this UoW. . Reason codes 30, 31, and 32 can also appear in the REASON_CODE column of the DSN_QUERYINFO_TABLE table, when EXPLAIN output is requested for queries that Db2 determines cannot be accelerated. . . II.New SQLCODE +904 is introduced for WAITFORDATA behavior. . +904 A RESOURCE IS UNAVAILABLE BUT PROCESSING CONTINUES. REASON reason-code, TYPE OF RESOURCE resource-type, AND RESOURCE NAME resource-name . Explanation: . A resource was unavailable, but the processing of the SQL statement continues. . reason-code The Db2 reason code value that indicates the reason for the failure. For explanations of reason codes, see Db2 reason codes and the "Notes" section below. . resource-type The type of the resource that the message identifies. For explanation of resource-types see Resource Types and and the "Notes" section below. . resource-name The name of the resource that the message identifies. For more explanation, see the "Notes" section below. . Notes: Db2 for z/OS does not issue SQLCODE +904. resource-type = 00001080 and reason-code = 00E7000E identify IBM Db2 Analytics accelerator as the resource that returns SQLCODE +904, and resource-name is message text from the accelerator, instead of showing a Db2 resource name. . IBM Db2 Analytics accelerator can return +904 on an SQL OPEN for certain accelerated queries when special register CURRENT QUERY ACCELERATION WAITFORDATA is used to specify a delay wait time for the query. See separate IBM Db2 Analytics accelerator documentation for information about SQLCODE +904 returned for an accelerated query. . System action: Processing of the SQL statement continues. . Programmer response: No programmer response is needed. . SQLSTATE: 01687 ================================================================ The accelerator statistics section Q8ST of IFCID 2 is updated with new accelerator statistics for HTAP and WAITFORDATA. These Q8ST changes are provided in companion APAR PI83286. See APAR PI83286 text for a description of these changes. ----------------------------------------------------------------
Problem conclusion
Temporary fix
Comments
Db2 code was changed to provide the new function Hybrid Transactional / Analytical Processing (HTAP) for Db2 dynamic accelerated queries. Additional search keywords: IDAAV5R1/K SQLCODE904 SQLCODE4742 IDAAV7R1/K ----------------------------------------------------------------
APAR Information
APAR number
PI83288
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
B10
Status
CLOSED UR1
PE
NoPE
HIPER
NoHIPER
Special Attention
YesSpecatt / New Function / Xsystem
Submitted date
2017-06-19
Closed date
2017-10-19
Last modified date
2019-03-12
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UI51280
Modules/Macros
DSNFCDIR DSNFXDIR DSNHAPLY DSNHPTAB DSNHSM2V DSNHSM7 DSNHSYMH DSNLXCMT DSNLXENV DSNLXHCS DSNLXNEW DSNLXRAX DSNLXRCN DSNLXREL DSNLXROP DSNLXRSQ DSNLXRSS DSNLXRWA DSNLXSR DSNLZF00 DSNLZMON DSNLZOSR DSNTIAM DSNTIA1 DSNUGSIT DSNWZDG2 DSNXEBR DSNXECW DSNXECWA DSNXECWU DSNXEDP DSNXEDSC DSNXEDS1 DSNXENR DSNXEPM DSNXERT DSNXERT2 DSNXESSR DSNXEUFP DSNXGRM1 DSNXGRTM DSNXODML DSNXODTR DSNXOD5 DSNXOEXC DSNXOEX1 DSNXOFF DSNXOIN DSNXONZA DSNXONZB DSNXONZC DSNXONZO DSNXOOS1 DSNXOOS2 DSNXOPRU DSNXOSL DSNXOSPR DSNXOST DSNXOTF DSNXOV0 DSNXOV1 DSNXOYDB DSNXOYP1 DSNXRRSP DSNXRSPG DSNX8CDA DSNX8EKG DSNX8SMF
Fix information
Fixed component name
DB2 OS/390 & Z/
Fixed component ID
5740XYR00
Applicable component levels
RB10 PSY UI51280
UP17/11/03 P F711
Fix is available
Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG19M","label":"APARs - z\/OS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.0","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
12 March 2019