IBM Support

PI83288: 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 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":"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