IBM Support

PI51923: SQLCODE -4742 RC=11 CAN OCCUR FOR A QUERY ON AN AOT CONTAINING COLUMNS WITH AN INVALID TIMESTAMP ARGUMENT

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • SQLCODE -4742 RC=11 can occur for a query on an AOT (accelerator
    only table) containing TIMESTAMP columns with an invalid
    argument.                                           (d178199)
    

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:   *
    *                      (1) DB2 incorrectly allows creating     *
    *                      an accelerator-only table containing a  *
    *                      column with a timestamp data type and   *
    *                      a precision other than 6 or 12.         *
    *                      (2) When query acceleration=ALL, the    *
    *                      query references only tables with the   *
    *                      SYSIBM schema, and all of the           *
    *                      referenced tables are enabled on the    *
    *                      accelerator, DB2 incorrectly routes     *
    *                      the query to DB2 instead of the         *
    *                      accelerator.                            *
    *                      (3) (V10 only) An INSERT or UPDATE to   *
    *                      an accelerator-only table for a decimal *
    *                      value with a greater precision or       *
    *                      scale than the target table decimal     *
    *                      precision or scale could be rounded     *
    *                      instead of truncated.                   *
    *                      (4) An ABEND04E RC00E70005              *
    *                      DSNXOMSF:P170 can occur for a statement *
    *                      that references a sourced function      *
    *                      with the accelerator-only function      *
    *                      MEDIAN() as the source.                 *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    This APAR fixes the following issues:
    (1) DB2 incorrectly allows creating an accelerator-only table
    containing a column with a timestamp data type and a precision
    other than 6 or 12. DB2 subsequently blocks any SELECT, INSERT,
    UPDATE or DELETE on the unsupported timestamp data type column
    with -4742 reason code 11.
    (2) When query acceleration=ALL, the query references only
    tables with the SYSIBM schema, and all of the referenced tables
    are enabled on the accelerator, DB2 incorrectly routes the
    query to DB2 instead of the accelerator.
    (3) (V10 only) An INSERT or UPDATE to an accelerator-only
    table for a decimal value with a greater precision or scale
    than the target table decimal precision or scale could be
    rounded instead of truncated.
    (4) An ABEND04E RC00E70005 DSNXOMSF:P170 can occur for a
    statement that references a sourced function with the
    accelerator-only function MEDIAN() as the source.
    A sourced function with MEDIAN() as the source is not
    eligble for accleration and should be dropped.
    
    Keywords:
    IDAAV4R1/K IDAAV5R1/K ABEND04E RC00E70005 SQLMEDIAN
    SQLCODE4742 SQLINSERT SQLUPDATE
    

Problem conclusion

  • (1) DB2 code is fixed such that CREATE TABLE for an accelerator
    only table that contains a column with a timestamp data type
    with a precision other than 6 or 12 fails with SQLCODE=-4747,
    SQLSTATE = 429CB, TOKEN=unsupported data type TIMESTAMP(n) where
    n is the unsupported timestamp precision.
    (2) DB2 code is fixed to properly route the query to DB2 or an
    accelerator.
    (3) DB2 code is fixed to truncate the value for an INSERT or
    UPDATE to an accelerator-only table for a decimal data type.
    (4) DB2 code is fixed to block creating a sourced function with
    MEDIAN() as the source with SQLCODE -4742.
    

Temporary fix

Comments

APAR Information

  • APAR number

    PI51923

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2015-11-04

  • Closed date

    2016-01-25

  • Last modified date

    2016-03-02

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

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

    UI34727 UI34728

Modules/Macros

  •    DSNXISB6 DSNXONZC DSNXONZO DSNXORFN
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UI34727

       UP16/02/09 P F602

  • RB10 PSY UI34728

       UP16/02/09 P F602

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: A10

Reference #: PI51923

Modified date: 02 March 2016


Translate this page: