IBM Support

PM92838: SQLCODE181 IS ENCOUNTERED WHEN INSERTING TIMESTAMP WITH TIMEZONE VALUE INTO DATE DATA TYPE IN DB2 V10 NFM

A fix is available

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • DB2DDF DCR DM1782
    Existing JCC application encounters SQLCODE181 when migrating to
    DB2 V10 NFM where the target column is defined as DATE and
    the application uses setTimestamp() api call to insert the
    value. The JCC driver will insert a value of
    TIMESTAMP with TIMEZONE into the column defined as DATE and
    SQLCODE181 is thrown.
    
    Client error:
    com.ibm.db2.jcc.am.SqlDataException: DB2 SQL Error:
    SQLCODE=-181, SQLSTATE=22007,
    
    ADDITIONAL SYMPTOMS/KEY WORDS
    =============================
    DB2 V10 NFM
    setTimeStamp
    JCC driver
    TIMESTAMP
    TIMEZONE
    DDFCOMPAT
    DSN6FAC
    

Local fix

  • deferPrepares=false
    change target column to match
    change application not to use setTimestamp()
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All Distributed Data Facility (DDF) users    *
    *                 accessing DB2 10 for z/OS in new function    *
    *                 mode and DB2 11 for z/OS from a Java IBM     *
    *                 Data Server Client Driver where the client   *
    *                 application has input host variables that    *
    *                 undergo implicit casting and also has        *
    *                 timestamp or timestamp with timezone as      *
    *                 input and date or time SQL type as target.   *
    ****************************************************************
    * PROBLEM DESCRIPTION: A client application using a Java IBM   *
    *                      Data Server Driver accesses a DB2 10    *
    *                      for z/OS server in New Function Mode    *
    *                      (NFM) or a DB2 11 for z/OS server,      *
    *                      passing in a timestamp or timestamp     *
    *                      with time zone input value where the    *
    *                      target is a date or time SQL type.      *
    *                      Prior to the occurrence of timestamp or *
    *                      timestamp with timezone input variable, *
    *                      there is an incompatible input host     *
    *                      variable bind-in (e.g. from numeric     *
    *                      data type to string target or vice      *
    *                      versa).  The statement correctly fails  *
    *                      with SQLCODE -181, due to the invalid   *
    *                      string representation of a datetime     *
    *                      value in timestamp or timestamp with    *
    *                      timezone input, which cannot be         *
    *                      implicitly cast to a date or time       *
    *                      target.  However, this application      *
    *                      successfully executed when accessing a  *
    *                      DB2 server at version 9 and below or    *
    *                      when accessing DB2 10 Conversion Mode   *
    *                      (CM).                                   *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    The remote statement being processed by DB2 contains
    incompatible input host variable bind-in prior to the occurrence
    of timestamp or timestamp with timezone input variable.  The
    statement executed correctly against DB2 9 and below and against
    DB2 10 CM because those levels do not provide implicit cast
    support.  As a result, the incompatible input host variable was
    correctly flagged as being in error and the statement failed
    with SQLCODE -301.  The Java IBM Data Server Client driver
    provides a property called RetryOnError that allows for the
    statement to be retried with corrected user data when SQLCODE
    -301 is received.  The client driver not only corrects the
    incompatible input host variable to match with the target data
    type, but also correctly passes date or time value to date or
    time target instead of timestamp.  With implicit cast support in
    DB2 10 NFM and after, this corrective action is not being
    performed by the client driver since the statement fails with
    SQLCODE -181 instead.  The client driver does not have support
    to redrive the statement for SQLCODE -181.
    .
    One solution is for the application to correct the invalid
    inputs.
    

Problem conclusion

  • To help customers address the issue for such client application
    mentioned above, the DB2 server is providing a new option,
    DISABLE_IMPCAST_JV, for the DDF_COMPATIBILITY ZPARM.  The new
    ZPARM option allows you to disable implicit casting for such
    client applications running in V10 NFM and in V11 with
    application compatibility equal to V10R1 while you implement
    corrective actions through changes to your application.
    
    You can use the new DISABLE_IMPCAST_JV option to restore the
    server bind-in processing behavior without implicit cast when
    DB2 is running in either:
    
    - V10 NFM (or moving to V10 NFM); or
    - V11 (any mode) with application level equal to V10R1
    
    In these environments, if you have an application with a
    statement that fails with SQLCODE -181 due to invalid datetime
    string in timestamp or timestamp with timezone input to date or
    time target, and prior to this input the statement also contains
    incompatible input host variable, then if you specify the
    DDF_COMPATIBILITY ZPARM with the new DISABLE_IMPCAST_JV option,
    as described below, that statement will now successfully execute
    as it did when it was executed in DB2 V9 and below or in DB2 10
    CM.
    
    The ZPARM details:
    
    This PTF adds a new option, DISABLE_IMPCAST_JV, to the DB2
    subsystem parameter DSN6FAC.DDF_COMPATIBILITY.  You can use the
    DDF_COMPATIBILITY parameter to enable DB2 pre-V10 DDF
    compatibility with a Java IBM Data Server driver.  The new
    DDF_COMPATIBILITY option is:
    
    - DISABLE_IMPCAST_JV: Use the pre-V10 server
                    compatibility behavior which is
                    not to implicitly cast input host
                    variables during server host
                    variable bind-in processing.
    
                    This option provides such
                    compatibility for all Java IBM
                    Data Server driver level.
    
                    This option is deprecated in DB2 10.
    
    If the DISABLE_IMPCAST_NJV option is specified on any member of
    a data sharing environment, it is recommended to specify it on
    all members.
    
    DDF_COMPATIBILITY options can be specified individually or
    together.  Examples:
      DDF_COMPATIBILITY=
      DDF_COMPATIBILITY=DISABLE_IMPCAST_JV
      DDF_COMPATIBILITY=(SP_PARMS_NJV,DISABLE_IMPCAST_JV)
      DDF_COMPATIBILITY=(DISABLE_IMPCAST_NJV,DISABLE_IMPCAST_JV)
    
    The default for DDF_COMPATIBILITY remains null (as shown in the
    first example above) meaning that DB2 10 or DB2 11 DDF behavior
    is maintained with all clients.
    
    Please note,
    
    (1). APAR PM86374 has already provide a similar fix to disable
         implicit cast for DB2 10 server host variable bind in path
         when the request comes from a non-Java IBM Data Server
         Client Driver. Please see closing text of PM86374 for more
         details.
    
    (2). This apar also provide a solution on DB2 11 for z/OS
         to disable implicit cast for server host variable bind in
         path when the request comes from a Java IBM Data Server
         Client Driver and its application compatibility is equal
         to V10R1. Please see closing text of PM94719 for more
         details.
    

Temporary fix

Comments

APAR Information

  • APAR number

    PM92838

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-07-11

  • Closed date

    2013-11-26

  • Last modified date

    2015-02-25

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

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

    UI12986 UI12987

Modules/Macros

  • DSNDQWPZ DSNLXRSS DSNWZIFA DSNWZIFB DSN6FAC
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UI12986

       UP13/12/17 P F312

  • RB10 PSY UI12987

       UP13/12/17 P F312

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:
25 February 2015