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

Obtain the fix for this APAR.

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 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.