IBM Support

PI29894: MULTIPLE FIXES FOR QUERY ACCELERATION

A fix is available

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • Multiple fixes for query acceleration, including:
    Incorrect precision of results when a double is cast as
    decimal on an accelerator.                            (d175049)
    IDAAV4R1/K
    

Local fix

  • n/a
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All users of DB2 for z/OS who use            *
    *                 query acceleration.                          *
    ****************************************************************
    * PROBLEM DESCRIPTION: This APAR fixes the following problems  *
    *                      for query acceleration:                 *
    *                      (1) The function TIME() with a string   *
    *                          timestamp constant, host variable   *
    *                          or parameter marker value can       *
    *                          incorrectly return microseconds.    *
    *                      (2) REFRESH TABLE is incorrectly        *
    *                          evaluated for acceleration.         *
    *                      (3) DB2 V11 only: the function          *
    *                          TIMESTAMPDIFF may return -904 when  *
    *                          executed on an accelerator.         *
    *                      (4) DB2 V10 only: An 04E abend          *
    *                          DSNXVTRS:M420 may occur for         *
    *                          a statement with GET ACCEL ARCHIVE  *
    *                          = YES when none of the tables       *
    *                          have any partitions in the PRO      *
    *                          restricted status and subsystem     *
    *                          parameter CACHEDYN=YES.             *
    *                      (5) DB2 V10 and V11: Add support to     *
    *                          honor the FETCH FIRST n ROWS        *
    *                          ONLY clause for an accelerated      *
    *                          dynamic cursor query that contains  *
    *                          a set operator like UNION ALL.      *
    *                      (6) DB2 V10 and V11:An SQL              *
    *                          statement with DECODE and a         *
    *                          result expression of NULL should    *
    *                          not execute on an accelerator.      *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    This APAR fixes the following problems for query acceleration:
    (1) The function TIME() with a string timestamp constant,
        host variable or parameter marker value can incorrectly
        return microseconds.
    
    (2) REFRESH TABLE is incorrectly evaluated for acceleration,
        but is never executed on an accelerator.
        EXPLAIN of a REFRESH TABLE statement when
        QUERY ACCELERATION = ENABLE, ENABLE WITH FAILBACK,
        ELIGBLE, or ALL incorrectly populates PLAN_TABLE and
        DSN_QUERYINFO_TABLE indicating that REFRESH TABLE
        can execute on an accelerator. Executing a REFRESH TABLE
        statement when QUERY ACCELERATION=ALL incorrectly issues
        SQLCODE -4742.
    (3) DB2 V11 only: the function TIMESTAMPDIFF may return -904
        when executed on an accelerator.
    (4) DB2 V10 only: An 04E abend DSNXVTRS:M420 may occur for
        a statement with GET ACCEL ARCHIVE= YES when none of the
        tables have any partitions in the PRO restricted status
        and subsystem parameter CACHEDYN=YES.
    (5) DB2 V10 and V11: Add support to honor the FETCH FIRST n
        ROWS ONLY clause for an accelerated dynamic cursor query
        that contains a set operator like UNION ALL.
    (6) DB2 V10 and V11:An SQL statement with DECODE and a
        result expression of NULL should not execute on an
        accelerator.
    
    For static SQL queries that have been bound for acceleration
    and will use any of functions above, REBIND the package
    for the failing query after applying this PTF.
    
    Keywords:
    IDAAV3R1/K IDAAV4R1/K SQLCODE4742 SQLEXPLAIN SQLTIME
    SQLREFRESHTABLE  SQLINCORROUT SQLINCORR INCORROUT DB2INCORR/K
    SQLCODE904 SQLTIMESTAMPDIFF
    ABEND04E RC00E70005 DSNXVTRS M420 SQLFFNR SQLDECODE
    

Problem conclusion

  • DB2 code is fixed:
    (1) The correct result is returned for TIME().
    (2) EXPLAIN of a REFRESH TABLE statement will not populate
    DSN_QUERYINFO_TABLE. PLAN_TABLE will have a DB2 access path.
    Executing REFRESH TABLE when QUERY ACCELERATION=ALL will now
    execute on DB2.
    (3) The correct result is returned for TIMESTAMPDIFF().
    (4) The statement executes properly.
    (5) The FETCH FIRST n ROWS clause is honored for acceleration.
    (6) The statement no longer executes on an accelerator.
    

Temporary fix

  • *********
    * HIPER *
    *********
    

Comments

APAR Information

  • APAR number

    PI29894

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2014-11-18

  • Closed date

    2015-04-15

  • Last modified date

    2015-05-04

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

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

    UI26764 UI26765

Modules/Macros

  •    DSNXODML DSNXOEX1 DSNXONZC DSNXONZO DSNXONZQ
    DSNXONZS DSNXOOP  DSNXOOS1
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UI26764

       UP15/04/30 P F504 Ž

  • RB10 PSY UI26765

       UP15/04/30 P F504 Ž

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"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
06 May 2020