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 PI83286 is one of two APARs that * * together provide the new Accelerator * * function "Hybrid Transactional / * * Analytical Processing" (HTAP) for the * * existing Db2 feature QUERY ACCELERATION * * The companion APAR is PI83288 and these * * two APARs are packaged as one PTF. * * * * APAR PI83286 provides the following * * HTAP support: * * - New DB2 for z/OS subsystem parameter * * QUERY_ACCEL_WAITFORDATA which * * provides an initial default value * * for new CURRENT QUERY ACCELERATION * * WAITFORDATA special register * * - New system-level statistics fields * * in IFCID 2 Accelerator statistics * * section Q8ST * * This support is described in the * * following APAR text. * **************************************************************** * RECOMMENDATION: * **************************************************************** I. New Db2 for z/OS subsystem parameter QUERY_ACCEL_WAITFORDATA . PI83286 adds in DSN6SPRM a new Db2 for z/OS subsystem parameter, QUERY_ACCEL_WAITFORDATA, that is used to provide an initial default value for new Db2 for z/OS CURRENT QUERY ACCELERATION WAITFORDATA special register. . This new special register provides support for "Hybrid Transactional/Analytical Processing" (HTAP) for dynamic accelerated queries by specifying the maximum wait time in seconds for the Accelerator to delay a query. The query is delayed for the specified wait time so that any Db2 for z/OS data changes that occurred prior to Db2 running the query can be replicated to the Accelerator. See companion APAR PI83288 text for more details about new CURRENT QUERY ACCELERATION WAITFORDATA special register and the additional support support requirements for HTAP. . The specification for the new subsystem parameter in DSN6SPRM is: QUERY_ACCEL_WAITFORDATA=nnnn.m where nnnn.m is a DECIMAL(5,1) numeric-constant value that ranges from 0.0 to a max of 3600.0, representing seconds. For example, 20.0 represents 20.0 seconds (or 20000 milli- seconds), and 30.5 represents 30.5 seconds (or 30500 milli- seconds). The value can also be specified as an integer numeric constant value, nnnn, ranging from 0 to 3600 seconds. . When a non-zero value is specified for QUERY_ACCEL_WAITFORDATA, Db2 for z/OS and the accelerator will also apply other WAITFORDATA delay behavior, restrictions, and requirements to ALL dynamic queries to be accelerated. These behaviors, restrictions, and requirements can validly stop or even fail the acceleration of some queries that formerly were accelerated successfully. If you specify a non-zero WAITFORDATA delay on the new Db2 subsystem parameter, ALL dynamic queries on the Db2 subsystem for which acceleration is requested will be affected. . **************************************************************** **************************************************************** * Therefore, IBM does NOT recommend using the subsystem * * parameter QUERY_ACCEL_WAITFORDATA to provide an initial * * "non-zero" default value for CURRENT QUERY ACCELERATION * * WAITFORDATA special register unless you fully understand and * * can accept the possible impact of WAITFORDATA behavior, * * requirements, and restrictions on ALL dynamic queries to be * * accelerated on the Db2 subsystem. See companion APAR PI38288* * text for details of the possible impact. * **************************************************************** **************************************************************** . The Db2 subsystem parameter QUERY_ACCEL_WAITFORDATA can also be set on Db2 Installation Panel DSNTIP82, where other existing query acceleration-related Db2 subsystem parameters are located: . Enter CURRENT QUERY ACCELERATION WAITFORDATA special register option: 5 WAIT FOR DATA ===> 0 0.0 - 3600.0 . ------------------------------------------------------------- . If you have already installed or migrated to this version of Db2, you need to take the following actions after applying this PTF: (1) Update customized copies of Db2 installation CLIST members (2) Update your customized copy of job DSNTIJUZ (3) Update private copies of the DSNTIDxx CLIST input member (4) Update the package for DSNTXAZP (5) Copy updated Db2 installation panels to alternate libraries . If you maintain z/OSMF workflows for installing or migrating to Db2 11, you need to perform these additional actions after applying this PTF: (6) Regenerate z/OSMF artifacts . Detailed guidance for these actions follows: . ---------------------------------------------------------------- (1) Update customized copies of Db2 installation CLIST members ==> This action is required for all customers . This PTF modifies CLIST members DSNTINST and DSNTINMF in the SDSNCLST target library only. You need to redo any record format changes and reapply any tailoring you have done to your copies of these CLISTs. You may also want to move DSNTINST to the prefix.NEW.SDSNCLST data set, where the CLISTs processed by job DSNTIJVC reside. . ---------------------------------------------------------------- (2) Update your customized copy of job DSNTIJUZ ==> This action is required for all customers This PTF modifies Db2 installation job DSNTIJUZ in the SDSNSAMP target library. After applying this PTF, you need to update your customized copy of this job as follows: . * Add the keyword parameter QUERY_ACCEL_WAITFORDATA=<x>, where <x> is a decimal(5,1) between 0.0 and 3600.0 (inclusive), to the invocation of the DSN6SPRM macro. <x> can also be specified as an integer between 0 and 3600 (inclusive). Make sure to add a continuation character in column 72 if needed. If you omit adding QUERY_ACCEL_WAITFORDATA here, the value will be set to the default of 0.0 when you assemble the DSNZPxxx module. . * Run the first two steps of the DSNTIJUZ job you modified. . * After the job completes, you must either use the -SET SYSPARM command or stop and restart Db2 for the change to take effect. . ---------------------------------------------------------------- (3) Update private copies of the DSNTIDxx CLIST input member ==> This action is required for all customers . This PTF adds an entry for QUERY_ACCEL_WAITFORDATA to the CLIST default input member, DSNTIDXA, in the SDSNSAMP target library. You need to add this entry to all private copies of your CLIST output DSNTIDxx member. In each such copy, add the following line: . QUERY_ACCEL_WAITFORDATA CHAR M NONE NONE <n> . Change <n> to the value you specified for QUERY_ACCEL_WAITFORDATA in step (2), above. . ---------------------------------------------------------------- (4) Update the package for DSNTXAZP ==> This action is required for all customers who use DSNTXAZP to update Db2 CLIST input (DSNTIDXX) members: . Submit a job that contains the following BIND statement: . BIND PACKAGE(DSNTXAZP) MEMBER(DSNTXAZP) - ACTION(ADD) ENCODING(EBCDIC) - LIBRARY('prefix.SDSNDBRM') . where <prefix> is the high-level qualifier for the Db2 subsystem target libraries. . ---------------------------------------------------------------- (5) Copy updated Db2 installation panels to alternate libraries ==> This action is required for all customers who maintain copies of the Db2 installation panels outside of SMP/E: . This PTF modifies Db2 installation panels DSNTIL82 and DSNTIP82 in the prefix.SDSNSPFP target library. If you keep the Db2 installation panels in a different library then after applying this PTF, you need to copy the updated DSNTIL82 and DSNTIP82 panels to that library. . ---------------------------------------------------------------- (6) Regenerate z/OSMF artifacts ==> This action is required for all customers who maintain z/OSMF workflows for installing or migrating to Db2 11: . This PTF adds a new Db2 subsystem parameter, QUERY_ACCEL_WAITFORDATA, with a default setting of 0. If you want to use a different setting or if you want QUERY_ACCEL_WAITFORDATA to appear as a variable in your existing z/OSMF workflows for Db2 11, you need to regenerate those workflows using the Db2 11 installation CLIST. For each workflow: - Before proceeding, create a backup of the prefix.NEW.SDSNxxxx libraries. - Run the CLIST as you did to create the workflow definition file and the workflow input variable file. - After CLIST processing completes, use ISPF option 3.12 or a similar file comparison tool to compare the old and new libraries. - If a new workflow instance needs to be created, create it with the new workflow definition file, the new workflow input variable file and the new JCL templates. - Do not execute the workflow instances created with the old workflow definition file, the old workflow input variable file, and the old JCL templates if a non-default setting of QUERY_ACCEL_WAITFORDATA is needed. . . ============================================================== II. New Accelerator statistics fields in IFCID 2 section Q8ST . APAR PI83286 also adds the following new accelerator WAITFORDATA statistics fields to the accelerator statistics section Q8ST in the IFCID 2 system-level statistics record : . Q8STTDPS Fixed(63) Total number of successful query requests that have been run with WAITFORDATA delay protocol sent by this Db2 subsystem to the accelerator. . Q8STEDPS Fixed(63) Total number of query requests that have been run with WAITFORDATA delay protocol sent by this Db2 subsystem to accelerator and have expired because the total wait time was higher than the specified WAITFORDATA value. . Q8STTDPA Fixed(63) Total number of successful query requests that have been run with WAITFORDATA delay protocol sent by all Db2 subsystems to the accelerator. . Q8STEDPA Fixed(63) Total number of query requests that have been run with WAITFORDATA delay protocol sent by all Db2 subsystems to accelerator and have expired because the total wait time was higher than the specified WAITFORDATA value. . Q8STVLCS Fixed(63) Replication velocity of this Db2 subsystem, measured in Db2 log seconds applied per second. Q8STVLCS value is in microseconds and must be converted to seconds for external usage. This value can be negative if replication for accelerator is stopped or replication information for the accelerator is not yet available. . These new Q8ST statistics fields will also be added to the Q8ST section of the DSNWMSGS flat file via separate APAR PI82192 . . The values of the new statistics fields Q8STTDPA, Q8STEDPA, and Q8STVLCS are also added to the accelerator information displayed by the DISPLAY ACCEL (XXX) DETAIL command. The Q8STVLCS value is converted to seconds for this external displayed value. In addition, the value of existing Q8ST statistic Q8STCRL (current replication latency for this Db2 system) is also added to this displayed output. . -XXXXDIS ACCEL(*) DETAIL DSNX810I -XXXX DSNX8CMD DISPLAY ACCEL FOLLOWS - DSNX830I -XXXX DSNX8CDA 480 ACCELERATOR MEMB STATUS REQUESTS ACTV QUED MAXQ ------------------------ ---- -------- -------- ---- ---- ---- accel-name XXXX STARTED 0 0 0 0 : CURRENT REPLICATION LATENCY FOR THIS Db2 SYSTEM = nnnnn MS NUMBER OF SUCCESSFUL QUERY REQUESTS WITH DELAY PROTOCOL FOR ALL Db2 SYSTEMS = nnnnn NUMBER OF EXPIRED QUERY REQUESTS WITH DELAY PROTOCOL FOR ALL Db2 SYSTEMS = nnnnn REPLICATION VELOCITY = n.nnnnnn Db2 LOG SECONDS APPLIED PER SECOND ---------------------------------------------------------------- New statement-level WAITFORDATA statistics fields in IFCID 0316 for dynamic queries will be supported & available in later APAR. ----------------------------------------------------------------
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 IDAAV7R1/K ----------------------------------------------------------------
APAR Information
APAR number
PI83286
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:
Modules/Macros
DSN@XAZP DSNDQWPZ DSNDQ8ST DSNDSPRM DSNTIDXA DSNTIDXB DSNTIJUZ DSNTIL82 DSNTINMF DSNTINST DSNTIP82 DSNTIVIA DSNTIVIE DSNTIVIN DSNTIVMD DSNTIVMS DSNTIWIA DSNTIWIE DSNTIWIN DSNTIWMD DSNTIWMS DSNTXAZP DSNWZIFB DSN6SPRM
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