IBM Support

PI20688: QUERY CONTAINING VARCHAR FUNCTION WITH A FOR BIT DATA COLUMN AND OCTETS MAY GET INCORROUT: QUERY WITH TIMESTAMP PRECISION=9 FAILS

A fix is available

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • A query containing a VARCHAR function with a FOR BIT DATA column
    and OCTETS may get incorrect output.
    A query with timestamp precision=9 fails when offloaded to the
    accelerator.                                           (d170927)
    

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 issues    *
    *                      for query acceleration.                 *
    *                                                              *
    *                      (1) (DB2 10 and DB2 11) A query with    *
    *                          the function VARCHAR with a FOR BIT *
    *                          DATA column and OCTETS may return   *
    *                          incorrect output.                   *
    *                      (2) (DB2 10 and DB2 11) An OLAP         *
    *                          specification using the aggregate   *
    *                          function AVG on a DECIMAL datatype  *
    *                          may return incorrect output.        *
    *                      (3) (DB2 10) An INSERT with subselect   *
    *                          statement may be incorrectly        *
    *                          evaluted when subsystem parameter   *
    *                          ACCELMODEL has a value of YES.      *
    *                      (4) (DB2 10 and DB2 11) A complex       *
    *                          query with an ORDER BY clause may   *
    *                          return output in the incorrect      *
    *                          order.                              *
    *                      (5) (DB2 10 and DB2 11) A query with    *
    *                          '-' or '+' on a constant or         *
    *                          a view reference resolves to a      *
    *                          constant may issue SQLCODE -904.    *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    This APAR fixes the following issues for query acceleration:
    (1) (DB2 10 and DB2 11) A query with the function VARCHAR with
        a FOR BIT DATA column and OCTETS may return incorrect
        output.
    (2) (DB2 10 and DB2 11) An OLAP specification using the
        aggregate function AVG on a DECIMAL datatype may return
        incorrect output.
    (3) (DB2 10) An INSERT with subselect statement may be
        incorrectly evaluted when subsystem parameter ACCELMODEL
        has a value of YES. EXPLAIN will show that
        DSN_STATEMNT_TABLE.TOTAL_COST will have a value that is less
        than 5000 and DSN_STATEMNT_TABLE.REASON will incorrectly
        contain 'ACCELMODEL ELIGIBLE'. Both QWAC_ACCEL_ELIG_CP
        and QWAC_ACCEL_ELIG_CP will be incorrectly incremented.
    (4) (DB2 10 and DB2 11) A complex query with an ORDER BY clause
        may return results in the incorrect order.
    (5) (DB2 10 and DB2 11) A query with '-' or '+' on a constant or
        a view reference resolves to a constant may issue SQLCODE
        -904 "Invalid SQL statement".
    
    For static SQL queries that have been bound for acceleration
    and will use any of the new functions above, REBIND the
    package for the failing query after applying this PTF.
    
    Keywords:
    IDAAV3R1/K IDAAV4R1/K SQLINCORR SQLINCORROUT INCORROUT
    DB2INCORR/K SQLVARCHAR SQLOLAP SQLAVG ACCELMODEL INSERT
    ORDERBY SQLCODE904
    

Problem conclusion

  • DB2 code is fixed for expected results and behavior.
    

Temporary fix

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

Comments

APAR Information

  • APAR number

    PI20688

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2014-06-24

  • Closed date

    2014-07-31

  • Last modified date

    2014-09-03

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

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

    UI20152 UI20151

Modules/Macros

  • DSNXOIN  DSNXONZC DSNXONZS
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UI20151

       UP14/08/15 P F408

  • RB10 PSY UI20152

       UP14/08/15 P F408

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":"10.1","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":"10.1","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
03 September 2014