PI04839: SQLCODE181 WHEN BOUNDARY TIMESTAMP WITH TIMEZONE IS USED AS INPUT HOST VARIABLE.

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• DB2DDF DCR DM1804
  DB2 returns -181 when boundary timestamp with timezone is used
  as input host variable.
  ***************************************************************
  Additional Symptoms and Keywords:
   SQLCODE -181 SQLCODE181 SQL181N SQL0181N SQL00181N
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All Distributed Data Facility (DDF) users    *
  *                 accessing DB2 10 for z/OS in new function    *
  *                 mode from a Java IBM Data Server Client      *
  *                 Driver where the client application uses     *
  *                 TIMESTAMP WITH TIMEZONE data type as input   *
  *                 and TIMESTAMP data type as target.           *
  ****************************************************************
  * PROBLEM DESCRIPTION: DB2 V10 issues an SQLCODE181 when a     *
  *                      client application passing in a         *
  *                      boundary TIMESTAMP WITH TIMEZONE input  *
  *                      value (e.g. 9999-12-31-23.59.59.000000  *
  *                      -8:00) to a TIMESTAMP target data type. *
  *                      DB2 does an intermediate conversion of  *
  *                      the TIMESTAMP WITH TIMEZONE value into  *
  *                      the TIMESTAMP value in UTC format (i.e. *
  *                      10000-01-01-07.59.59.000000) when       *
  *                      processing.  This causes the value to   *
  *                      go beyond the maximum TIMESTAMP value   *
  *                      that DB2 supports.                      *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  When the client application uses PreparedStatement.setTimestamp
  method to pass in a boundary TIMESTAMP input value to a
  TIMESTAMP target against DB2 10 NFM server, Java IBM Data Server
  Client driver appends the time zone information from the local
  time zone.  During server processing, the value gets converted
  in UTC format which may cause it to go beyond the maximum or
  minimum TIMESTAMP value DB2 supports.
  .
  One solution is provided by the Java IBM Data Server Driver.
  See APARs PM96293, PM96295 and IC99579.  Refer to those APARs
  for details.
  

Problem conclusion

• To prevent applications from getting SQLCODE181, DB2 server
  provides a new option, IGNORE_TZ, for the DDF_COMPATIBILITY
  ZPARM.  The new ZPARM option allows DB2 server to ignore
  TIMEZONE portion of the value in the TIMESTAMP WTIH TIMEZONE
  input to a TIMESTAMP target during the intermediate conversion
  processing.  When IGNORE_TZ option is turned on for the
  DDF_COMPATIBILITY ZPARM, then SQLCODE181 will be prevented.
  
  The ZPARM and trace details:
  
  1.  This PTF adds a new option called IGNORE_TZ to the
  DSN6FAC.DDF_COMPATIBILITY subsystem parameter.  It can be
  specified by users using a supported Java IBM Data Server driver
  to request the pre-V10 behavior for binding TIMESTAMP input host
  variable into TIMESTAMP target.
  
   - IGNORE_TZ:    Use the pre-V10 server
                   compatibility behavior which is
                   to ignore TIME ZONE information in
                   the boundary error checking for
                   TIMESTAMP WITH TIME ZONE input
                   variables during server host
                   variable bind-in processing when
                   target data type is TIMESTAMP.
  
                   This option provides such compatibility
                   only for all Java IBM Data Server
                   drivers level.
  
                   This option is deprecated in DB2 10.
  
  If the IGNORE_TZ option is specified on any member of a data
  sharing environment, it is recommended to specify it on all
  members.
  
  IGNORE_TZ options can be specified individually or together.
  Examples:
    DDF_COMPATIBILITY=
    DDF_COMPATIBILITY=DISABLE_IMPCAST_JV
    DDF_COMPATIBILITY=IGNORE_TZ
    DDF_COMPATIBILITY=(SP_PARMS_NJV,IGNORE_TZ)
    DDF_COMPATIBILITY=(DISABLE_IMPCAST_NJV,SP_PARMS_JV,IGNORE_TZ)
  
  The default for DDF_COMPATIBILITY remains null (as shown in the
  first example above) meaning that DB2 10 DDF behavior is
  maintained with all clients.
  
  2.  This PTF also introduced a new value for field QW0366FN in
  IFCID 0366 to help detect whether an application may encounter
  SQLCODE181 due to a boundary value of the TIMESTAMP WITH
  TIMEZONE input value when it is passed into DB2 10 Server.
  
  DB2 10 Server will produce the IFCID 0366 trace records with
  the new value when all of the following conditions are met:
  (1). IFCID 0366 is enabled on DB2 10 for z/OS server
  (2). IGNORE_TZ is set for DDF_COMPATIBILITY zparm
  (3). The application is using a supported Java IBM Data Server
       Driver
  
  This is the new value introduced for QW0366FN field:
  
  QW0366FN = 9
  
    A QW0366FN 9 record indicates a data type conversion from
    a TIMESTAMP WITH TIME ZONE input to a TIMESTAMP data
    during input host variable bind-in process on server when
    DDF_COMPATIBILITY zparm is set to IGNORE_TZ to ignore the
    time zone information sent by Java IBM Data Server Driver.
  
  When examining collected IFCID 0366 trace records, if any record
  with a QW0366FN value of 9 then take a closer look at the input
  TIMESTAMP string and act accordingly if a boundary TIMESTAMP
  value (e.g. 0001-01-01-00.00.00.000000 or
  9999-12-31-23.59.59.000000) is used.  The TIMEZONE information
  appended by the driver may cause the converted intermediate
  TIMESTAMP value to underflow or overflow respectively.
  
  3.  This PTF modifies the installation CLIST in both DB2 10 and
  DB2 11 so that it can manage additional settings for the
  DSN6FAC.DDF_COMPATIBILITY parameter. In particular, the CLIST
  input (DSNTIDxx) member now stores DDF_COMPATIBILITY parameter
  in two consecutive entries, DDF_COMPATIBILITY and
  DDF_COMPATIBILITY1. For example:
  
  DDF_COMPATIBILITY LSTR  M  <x>
  DDF_COMPATIBILITY1 LSTR  M  <y>
  
  See the ++HOLD actions for guidance on adapting your private
  copies of the DSNTIDxx member.
  

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:

  UI17042 UI17043

Modules/Macros

• DSN@XAZP DSNDQWPZ DSNDQW05 DSNLXRSS DSNTIDXA DSNTIDXB DSNTINST
  DSNTXAZP DSNWZIFA DSNXRIHD DSNXRIHS DSN6FAC
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UI17042

     UP14/04/30 P F404

• RB10 PSY UI17043

     UP14/04/30 P F404

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.