Troubleshooting
Problem
If a CLI or JDBC application is retrieving a date, time, or timestamp value from a DB2® database server into a string or character-based application data type, the default format that is returned for the date, time, or timestamp value has changed from that of a DB2 Universal Database™ (DB2 UDB) Version 7 client.
Cause
A change was made between DB2 UDB Version 7 and DB2 UDB Version 8 and above clients with respect to the format of a date, time, or timestamp value that is retrieved from a DB2 UDB server to a CLI or JDBC application. In DB2 UDB Version 8+, a CLI keyword called DateTimeStringFormat is used to manipulate the format of both the date and the time or timestamp format. The default value of this keyword is set to JIS.
If an application binds a date, time or timestamp value into a character-based application data type, the DB2 CLI driver will by default change the format to JIS. If the DateTimeStringFormat keyword is explicitly used by an application, then the DB2 CLI driver will use the corresponding format for both the date and time or timestamp values.
In DB2 UDB Version 7 (FixPak 5 or higher) clients, the DateTimeStringFormat keyword does exist, however the change that was made was with when it manipulated the date, time or timestamp value. In DB2 UDB Version 7, this keyword does not work when a date, time, or timestamp value is being bound out to a character based application type. The format of the date, time, or timestamp that is returned is not necessarily the same.
The format of the date is chosen based on the DATETIME bind option that was specified for the package, which contains the plan for the SQL statement being invoked on the server. Since CLI and JDBC applications primarily use dynamic SQL, the format that would be returned would be based on the DATETIME bind option used for the db2cli*.bnd files. On DB2 for z/OS®, the defaults are defined in the DSNTIP4 panel. So if the bind file list db2cli.lst was bound using DATETIME USA, the format that would be returned for dates (if bound out to character-based data types) would be USA.
The format of the time or timestamp in DB2 UDB Version 7 CLI-based applications is predetermined to be in JIS. The only way to change the format for the time or timestamp is by setting the db2cli.ini keyword PATCH2=33, which would set the time or timestamp format to ISO.
The legacy JDBC driver (db2java.zip) uses the CLI driver indirectly. So the DateTimeStringFormat keyword has a direct impact when using DB2 UDB Version 8, DB2 Version 9.1, or DB2 Version 9.5.
The Universal JDBC driver (db2jcc.jar) does not use CLI. The only format that is returned to "string" data types for both date and time or timestamp fields will be JIS. There is currently no way to manipulate that setting with the current release of the driver, since the DateTimeStringFormat keyword does not work with the Universal JDBC driver. A new property may be included in an upcoming release of the Universal JDBC driver to allow the manipulation of a date, time or timestamp when it is bound out to a string in Java™.
The reason why JIS is returned as the default format for both the Universal JDBC driver and the Legacy JDBC driver is that JIS is the desired format based on the JDBC specifications.
Resolving The Problem
If you want to use the same default format for the date, time and timestamp that a DB2 UDB Version 7 client had, then some manipulation is required. One approach could be to explicitly cast your SQL statements so that a date is casted to a CHAR. For example:
SELECT CAST(CURRENT DATE AS CHAR(10)) AS mydate FROM SYSIBM.SYSDUMMY1
The CLI driver will treat "mydate" as a CHAR and not a DATE, which means the DateTimeStringFormat keyword would have no impact on the value. The format returned for this date would be based on the DATETIME setting for the package (like it is for DB2 UDB Version 7 clients). For time or timestamp, no changes are required, since the DateTimeStringFormat would use JIS, which was the default for DB2 UDB Version 7 clients.
Another option would be to bind out to something other than a character-based data type. For example, instead of using "getString()" in JDBC, you could use "getDate()" or "getTime()". The application can then use the built-in calendar methods in Java to manipulate the date, time, or timestamp value into a suitable format.
APAR# IY98367 for DB2 v8, APAR# IZ17029 for DB2 v9.1 and APAR# IZ17030 for DB2 v9.5 (this APAR is also included in v10.1 and v10.5) have been opened to allow the format of a DATE and TIME/TIMESTAMP field to be returned in the same format that was sent by the target server to the DB2 CLI driver. The db2cli.ini keyword PATCH2=77 would need to be used with this APAR to get this added functionality on the DB2 client. PATCH2=77 will effectively give the same functionality that existed with DB2 v7 FP5 and higher in that CLI will not manipulate the Date or Time/Timestamp value that it is being bound into a character based application datatype.
Related Information
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21257701