A fix is available
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
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