A fix is available
APAR status
Closed as new function.
Error description
New Function
Local fix
Problem summary
**************************************************************** * USERS AFFECTED: * * All Db2 12 for z/OS users of bind option * * QUERYACCELERATION for static SQL queries * * who want to use the existing Db2 feature * * ACCELERATION WAITFORDATA to get * * Hybrid Transactional / Analytical * * Processing (HTAP) for static queries * * accelerated to the IBM Db2 Analytics * * Accelerator * **************************************************************** * PROBLEM DESCRIPTION: * * This is a new function APAR/PTF that * * provides Db2 12 for z/OS ACCELERATION * * WAITFORDATA and HTAP support for * * static accelerated queries, and adds * * the following new Db2 12 external * * features: * * 1) a new bind option, * * ACCELERATIONWAITFORDATA * * for BIND and REBIND PACKAGE , and * * 2) a new option, * * ACCELERATION WAITFORDATA * * for CREATE / ALTER PROCEDURE / * * FUNCTION for SQL PL procedures and * * SQL PL scalar functions * * . * * Prior to this APAR/PTF, Db2 * * ACCELERATION WAITFORDATA and HTAP * * support were only provided for dynamic * * accelerated queries. * **************************************************************** * RECOMMENDATION: * * Apply corrective PTF when available * **************************************************************** With this APAR/PTF, Db2 12 for z/OS users of QUERY ACCELERATION for static SQL queries can now use the existing Db2 feature ACCELERATION WAITFORDATA to get Hybrid Transactional / Analytical Processing (HTAP) for static queries accelerated to the IBM Db2 Analytics Accelerator. IBM Db2 Analytics Accelerator 5.1.7 or later or IBM Db2 Analytics Accelerator 7.1.5 or later is also required. . Previously, Db2 V12 only supported HTAP for dynamic accelerated queries. . This new function APAR/PTF introduces the following new Db2 12 for z/OS external features for static accelerated queries: 1) a new bind option, ACCELERATIONWAITFORDATA , for BIND, BIND COPY , and REBIND PACKAGE, and BIND DEPLOY for SQLPL procedure package 2) a new option, ACCELERATION WAITFORDATA , for CREATE / ALTER PROCEDURE / FUNCTION for SQLPL procedures (aka, SQL - native ) and SQLPL scalar functions (aka, compiled SQL scalar) Db2 12 for z/OS Function Level and Catalog Level FL500 must be enabled in order to use these features to get HTAP for static queries accelerated to the IBM Db2 Analytics Accelerator . . I. Bind Option ACCELERATIONWAITFORDATA for BIND and REBIND PACKAGE --------------------------------------------------------------- Acceleration WAITFORDATA (or HTAP) can be requested for static accelerated queries by specifying the new bind option on BIND or REBIND PACKAGE as follows: ACCELERATIONWAITFORDATA(nnnn.m) where, nnnn.m is a DECIMAL(5,1) value that specifies the maximum wait time in seconds for the Accelerator to delay a query, waiting for replication of committed Db2 12 for z/OS data changes. . The decimal value ranges from 0.0 to max of 3600.0 seconds. As examples, 30.5 is 30.5 seconds (or 30500 milliseconds) and 0.5 = 0.5 seconds (or 500 milliseconds). The default value is 0.0, meaning do not delay the query. . For ease of use, the value may also be specified as an integer nnnn (e.g., 120), which Db2 will internally convert to a decimal (5,1) value. . 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 bind option ACCELERATIONWAITFORDATA only applies when the existing bind option QUERYACCELERATION is also used for the Db2 application package to request static query acceleration. Both must be specified in order for the WAITFORDATA delay behavior to be applied to static accelerated queries. . When a non-zero value is specified for bind option ACCELERATIONWAITFORDATA, Db2 for z/OS and the Accelerator will also apply other WAITFORDATA delay behavior, restrictions, and requirements to ALL queries to be accelerated from the application package. These behaviors, restrictions, and requirements can validly stop or even fail the acceleration of some queries that formerly were accelerated successfully. For more details and information, please see the existing Db2 12 for z/OS documentation for the special register CURRENT QUERY ACCELERATION WAITFORDATA. . The default for bind option ACCELERATIONWAITFORDATA is 'not specified'. . The bind option will also be used to initialize the existing special register CURRENT QUERY ACCELERATION WAITFORDATA for dynamic accelerated queries when the Db2 package application is run, if no SET for that special register has been issued before the application is invoked. Therefore, the new precedence order (low to high) for setting the initial value of the WAITFORDATA special register is as follows: (1) DB2 Installation system parameter QUERY_ACCEL_WAITFORDATA (2) Bind option ACCELERATIONWAITFORDATA value if specified (3) explicit SET statement for the WAITFORDATA special register If the bind option is used to initialize the WAITFORDATA special register, then either the QUERYACCELERATION bind option or the QUERY ACCELERATION special register must also be specified or set for the Db2 application package. . . II. Option ACCELERATION WAITFORDATA for CREATE / ALTER PROCEDURE / FUNCTION for SQL Procedure Language (SQL PL) Procedures --------------------------------------------------------------- . Acceleration WAITFORDATA (or HTAP) can be requested for static accelerated queries in SQLPL procedures (aka, SQL - native ) and SQLPL scalar functions (aka, compiled SQL scalar) by specifying the new option on CREATE / ALTER PROCEDURE / FUNCTION for SQLPL objects as follows: ACCELERATION WAITFORDATA nnnn.m where nnnn.m must conform to the value rules previously described under the section I discussion for the new bind option. . The option ACCELERATION WAITFORDATA only applies when the existing option QUERY ACCELERATION is also used or specified for the SQLPL procedure or function. Both must be specified in order for the WAITFORDATA delay behavior to be applied. ============================================================== . A new column is added to the existing Db2 12 for z/OS table table SYSACCEL.SYSACCELERATEDPACKAGES -- ACCELERATION_WAITFORDATA DECIMAL(5,1) This column is used for packages created with either the package bind option or the option for SQLPL procedures. *************************************************************** * New column ACCELERATION_WAITFORDATA must be correctly added * * to table SYSACCEL.SYSACCELERATEDPACKAGES before attempting * * to use the new bind option, else your Bind attempt will * * fail with existing error messages for SYSACCELERATEDPACKAGES* * table. * * * * Please see the ++HOLD ACT for this PTF for more important * * instructions, warnings, and information about adding this * * column to SYSACCEL.SYSACCELERATEDPACKAGES table. * * * * ******* WARNING!! WARNING!! WARNING!! WARNING!! ******* * * If you are using DB2 for z/OS DECP Application Programming * * Default value DECIMAL POINT IS COMMA (or DECIMAL=COMMA ), * * then, * * - the CREATE TABLE "SYSACCEL"."SYSACCELERATEDPACKAGES" * * statement modified in sample job DSNTIJAS, or * * - your own ALTER TABLE ADD COLUMN statement (as described * * in this PTF ++HOLD if the table is already defined) * * for the new column ACCELERATION_WAITFORDATA, must have * * a space following the ',' in the DECIMAL specification * * as follows: * * ACCELERATION_WAITFORDATA DECIMAL(5, 1) * * NOT NULL WITH DEFAULT -1.0 * * * * Otherwise, when DECIMAL=COMMA and a ',' is used as * * a separator in SQL as in this case, the Db2 for z/OS Parser * * will fail the CREATE TABLE or ALTER TABLE ADD COLUMN * * statement with SQLCODE -104 ( SQLCODE104 ) when the ',' is * * not followed by a space. * *************************************************************** . The statement-level WAITFORDATA statistics and monitoring support that is currently provided in Db2 trace record IFCID316 for dynamic accelerated queries, will be provided for static accelerated queries via trace record IFCID401 in a later follow-up APAR/PTF. . The Db2-provided stored procedure SYSPROC.ADMIN_INFO_SQL will be modified in a later follow-up APAR/PTF to support the table SYSACCEL.SYSACCELERATEDPACKAGES table having the new added column ACCELERATIONWAITFORDATA.
Problem conclusion
Temporary fix
Comments
Additional search keywords: IDAAV5R1/K IDAAV7R1/K SQLPROCEDURE ADMIN_INFO_SQL SYSPROC
APAR Information
APAR number
PH14116
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
C10
Status
CLOSED UR1
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2019-07-03
Closed date
2019-07-24
Last modified date
2021-11-05
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UI64401
Modules/Macros
DSNHSYMH DSNECP54 DSNXIDPL DSNHSMS1 DSNXODML DSNLXRCM DSNXICTR DSNTBRSD DSNXERBP DSNXONZO DSNXIATR DSNLXRCS DSNECP19 DSNTBRB2 DSNXONZB DSNHSMP2 DSNUEXDL DSNXOYDB DSNFTDIR DSNXISUB DSNTBCM5 DSNTBTRG DSNTBCM DSNTBCM4 DSNXONZA DSNHSM2V DSNTBCM2 DSNXEAAL DSNHAPLY DSNXERT DSNTBMSG DSNXEPM DSNXISB7 DSNXEBPG DSNHPTAB DSNTIJAS DSNXISB2 DSNHSMF2 DSNXIART DSNTBAS3 DSNXICRT DSNXIVRO DSNHSM5R DSNTBAS2 DSNFCDIR DSNXOYFR DSNXOIN DSNTBSPL DSNXIANR DSNHSMF3 DSNXOYT0 DSNXOYVR DSNTBAP1 DSNECP44 DSNTBFR2 DSNECP45 DSNECP48 DSNXOYP0 DSNTBAP DSNTBAR DSNECP08 DSNXIVNO DSNX8CTG DSNECP00 DSNXOYPC DSNTBBP2 DSNTBAB2 DSNXOYPA
Fix information
Fixed component name
DB2 OS/390 & Z/
Fixed component ID
5740XYR00
Applicable component levels
RC10 PSY UI64401
UP19/08/13 P F908
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.
[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"DB2 for z\/OS"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"12.0"}]
Document Information
Modified date:
06 November 2021