IBM Support

PI83286: THIS APAR PROVIDES NEW FUNCTION FOR DB2 FOR Z/OS V11

A fix is available

Subscribe

You can track all active APARs for this component.

 

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":"BU050","label":"BU NOT IDENTIFIED"},"Product":{"code":"SSEPEK","label":"DB2 for z\/OS"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.0","Edition":""},{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SG19M","label":"APARs - z\/OS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.0","Edition":""}]

Document Information

Modified date:
12 March 2019