IBM Support

PI49422: DB2 11 FOR Z/OS NEW FUNCTION.

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as new function.

Error description

  • DB2 11 for z/OS new function.
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All DB2 11 for z/OS users of QUERY           *
    *                 ACCELERATION for static SQL queries          *
    ****************************************************************
    * PROBLEM DESCRIPTION: This APAR adds a new column,            *
    *                      ACCELERATORSRL, to existing DB2 table   *
    *                      SYSACCEL.SYSACCELERATORS.  This column  *
    *                      is "IBM DB2 Internal Use Only" and is   *
    *                      used by DB2 during BIND or REBIND       *
    *                      PACKAGE time to detect if accelerator   *
    *                      supports the level of function needed   *
    *                      by a static SQL query that references   *
    *                      a user table on that accelerator, even  *
    *                      though the accelerator is not started.  *
    *                                                              *
    *                      Previously, this type of accelerator    *
    *                      function-level evaluation for static    *
    *                      queries could only be done at execution *
    *                      time on the OPEN for the static query,  *
    *                      when the accelerator is "started";      *
    *                      potentially failing the OPEN for        *
    *                      the accelerated query, instead of "not" *
    *                      binding the static query for            *
    *                      acceleration in the first place.        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    This APAR adds a new column, ACCELERATORSRL, to existing DB2
    table SYSACCEL.SYSACCELERATORS in the DB2 Accelerator database
    DSNACCEL.
                                                                   .
    ACCELERATORSRL is an "IBM DB2 Internal Use Only" FOR BIT DATA
    type column that is used by DB2 during BIND/REBIND PACKAGE time
    to detect if an accelerator supports the level of function
    needed by a static SQL query that references a user table on
    that accelerator, even though the accelerator is not started at
    the time of the bind.
                                                                   .
    Previously, this type of accelerator function-level evaluation
    for static query acceleration could only be done at execution-
    time on the SQL OPEN for the accelerated static query, when
    the accelerator is "started". That is the time when DB2 could
    acquire detailed function-level information about the accel-
    erator. Consequently, even though the static query was bound for
    acceleration, the OPEN for the accelerated static query could
    fail if the target accelerator didn't support the function-level
    required for the query.  Ideally, the preferred method would be
    to detect this accelerator function-level for a static query
    during the BIND/REBIND PACKAGE, so that the static query is not
    even bound for offload if the target accelerator does not
    support the function-level required for the static query.
                                                                   .
    Now, with this APAR and the new SYSACCELERATORS column
    ACCELERATORSRL, DB2 can detect the accelerator function-level
    for a static query during the BIND/REBIND PACKAGE.
    As a result, instead of binding a static query for acceleration
    and potentially failing it at execution-time on the OPEN,
    DB2 will now *not* bind the static query for acceleration during
    BIND/REBIND PACKAGE if the target accelerator cannot support
    the function-level required by the query, even though the user
    table is defined on that accelerator.
                                                                   .
    In order for DB2 to perform this accelerator function-level
    checking during a BIND/REBIND PACKAGE, the accelerator must have
    been re-STARTed at least once 'after' applying this APAR/PTF and
    following the instructions below for adding the new column
    ACCELERATORSRL.
                                                                   .
    Note that *not* adding the new column does not cause a problem
    or prevent your static SQL from being bound for acceleration --
    your application just won't benefit from the accelerator
    function-level checking during BIND/REBIND PACKAGE time.
    Instead, DB2 will perform that checking at execution-time during
    the OPEN for the accelerated static query as before.
                                                                   .
    ----------------------------------------------------------------
    This APAR/PTF modifies the existing CREATE TABLE statement for
    "SYSACCEL"."SYSACCELERATORS", in the job DSNTIJAS, by adding
    a line for column ACCELERATORSRL after the line for column
    LOCATION:
     , "ACCELERATORSRL"   CHAR(64) FOR BIT DATA DEFAULT NULL
                                                                   .
    ACCELERATORSRL is now the third and new last column of
    table SYSACCEL.SYSACCELERATORS.
                                                                   .
    Please see the ++HOLD of this APAR/PTF for more important
    information about the modification to job DSNTIJAS.
                                                                   .
    ----------------------------------------------------------------
    If you are already using QUERY ACCELERATION and have previously
    created the table SYSACCEL.SYSACCELERATORS, after applying this
    APAR/PTF process the following ALTER SQL statement using SPUFI
    or similar tool to update DB2 table SYSACCEL.SYSACCELERATORS
    with the new column ACCELERATORSRL :
       ALTER TABLE "SYSACCEL"."SYSACCELERATORS"
             ADD COLUMN ACCELERATORSRL
               CHAR(64) FOR BIT DATA DEFAULT NULL;
                                                                   .
    This ALTER operation will place the DSNACCEL.SYSACCEL table
    space, where the SYSACCEL.SYSACCELERATORS table resides, in
    AREO* state (i.e., REORG-pending state). Perform REORG utility
    on the DSNACCEL.SYSACCEL table space to remove it from REORG-
    pending state.
                                                                   .
    (If ALTER TABLE ADD COLUMN for SYSACCELERATORS fails with
    SQLCODE -904 RC00C900E3 for table space DSNDB06.SYSALTER,
    then you must perform REORG utility on the DSNDB06.SYSALTER
    table space and afterwards retry the ALTER TABLE ADD COLUMN
    for SYSACCELERATORS. This SQLCODE904 RC00C900E3 for table
    space SYSALTER is not due to any code in this APAR/PTF, but
    can occur on DB2 V11 for an ALTER TABLE of a 'user-created'
    table if the table space SYSALTER was created or cloned as
    a result of a DSN1COPY performed at some previous point in
    time from a pre-V11 subsystem. Please see APAR PI55114
    for additional information about this specific SQLCODE904.)
                                                                   .
    After applying the PTF and issuing the above ALTER ADD COLUMN,
    STOP (if applicable) then re-START the accelerator in order for
    DB2 to validate the new column and populate it with the accel-
    erator function-level information that is used during BIND/
    REBIND PACKAGE for static SQL that you want to accelerate.
                                                                   .
    For DB2 Data Sharing configurations, you do NOT need to apply
    this PTF to all DB2 members of the Data Sharing group before
    adding the new column to SYSACCELERATORS. Those DB2 members that
    do not have this PTF applied will simply ignore the new column
    and will not benefit from the new support provided.
                                                                   .
    Note that if you have applications that SELECT from the DB2
    table SYSACCEL.SYSACCELERATORS, after adding the new column such
    applications may be affected. Please examine those applications
    to determine if they are affected by the addition of the new
    column.
                                                                   .
    

Problem conclusion

Temporary fix

  • AI49422
    

Comments

  • DB2 acceleration support for static SQL queries has been
    modified as previously described.
                                                                   .
    Additional search keywords: IDAAV4R1/K
    ×**** PE16/07/07 FIX IN ERROR. SEE APAR PI61661  FOR DESCRIPTION
    

APAR Information

  • APAR number

    PI49422

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    B10

  • Status

    CLOSED UR1

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2015-09-25

  • Closed date

    2016-01-13

  • Last modified date

    2016-08-02

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

    UI34442

Modules/Macros

  •    DSNTBAB2 DSNTBAS2 DSNTBCM  DSNTBCM5 DSNTBFR2
    DSNTBSPL DSNTIJAS DSNXISB6 DSNXONZA DSNXONZB DSNX8CTG DSNX8EKG
    DSNX8IN2 DSNX8STA
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RB10 PSY UI34442

       UP16/01/29 P F601

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.



Document information

More support for: DB2 for z/OS

Software version: B10

Reference #: PI49422

Modified date: 02 August 2016


Translate this page: