DB2 10.5 for Linux, UNIX, and Windows

Data loss for timestamp data in JDBC and SQLJ applications

For DB2® for z/OS® Version 10 or later, or DB2 for Linux, UNIX, and Windows Version 9.7 or later, you can specify the precision of the fractional part of a TIMESTAMP column, with a maximum precision of 12 digits. The fractional part of a Java™ timestamp value can have up to 9 digits of precision. Depending on the column definition, data loss can occur when you update a TIMESTAMP(p) column or retrieve data from a TIMESTAMP(p) column.

Data loss for input data

If you use a setTimestamp call to pass a timestamp value to a TIMESTAMP(p) column, the maximum precision of the Java value that is sent to the data source is 9. If you use a setTimestamp call to pass a timestamp value to a TIMESTAMP column at a data source that does not support TIMESTAMP(p), the maximum precision of the Java value that is sent to the data source is 6. For input to a TIMESTAMP(p) column, if the precision of the target column is less than the precision of the input value, the data source truncates the excess digits in the fractional part of the timestamp.

If you use a setString call to pass the input value, it is possible to send a value with a precision of greater than 9 to the data source.

For IBM® Data Server Driver for JDBC and SQLJ version 3.59 or later, no data loss occurs if the TIMESTAMP(p) column is big enough to accommodate the input value. For IBM Data Server Driver for JDBC and SQLJ version 3.58 or earlier, data loss depends on the setting of the deferPrepares property and the sendDataAsIs property:

If sendDataAsIs is set to true, the IBM Data Server Driver for JDBC and SQLJ sends the string to the data source as-is, so the fractional part of the timestamp value can be more than 9 digits. If the value of p in the TIMESTAMP(p) column is greater than or equal to the number of digits in the fractional part of the input data, no data loss occurs.
If sendDataAsIs is set to false, data loss depends on the deferPrepares setting.
If deferPrepares is set to true, the first time that an UPDATE statement is executed, the IBM Data Server Driver for JDBC and SQLJ sends the string to the data source as-is, so the fractional part of the timestamp value can be more than 9 digits. If the value of p in the TIMESTAMP(p) column is greater than or equal to the number of digits in the fractional part of the input data, no data loss occurs.
For subsequent executions of the UPDATE statement, the IBM Data Server Driver for JDBC and SQLJ can determine that the target data type is a TIMESTAMP data type. If the data source supports TIMESTAMP(p) columns, the driver converts the input value to a java.sql.Timestamp value with a maximum precision of 9. If the data source does not support TIMESTAMP(p) columns, the driver converts the input value to a java.sql.Timestamp value with a maximum precision of 6. Data loss occurs if the original value has more precision than the converted java.sql.Timestamp value, or if the java.sql.Timestamp value has more precision than the TIMESTAMP(p) column.
If deferPrepares is set to false, the IBM Data Server Driver for JDBC and SQLJ can determine that the target data type is a TIMESTAMP data type. If the data source supports TIMESTAMP(p) columns, the driver converts the input value to a java.sql.Timestamp value with a maximum precision of 9. If the data source does not support TIMESTAMP(p) columns, the driver converts the input value to a java.sql.Timestamp value with a maximum precision of 6. Data loss occurs if the original value has more precision than the converted java.sql.Timestamp value, or if the java.sql.Timestamp value has more precision than the TIMESTAMP(p) column.

You can lessen data loss for input timestamp values by using a setString call and setting sendDataAsIs to true. However, if you set sendDataAsIs to true, you need to ensure that application data types are compatible with data source data types.

Data loss for output data

When you use a getTimestamp or getString call to retrieve data from a TIMESTAMP(p) column, the IBM Data Server Driver for JDBC and SQLJ converts the value to a java.sql.Timestamp value with a maximum precision of 9. If the source value has a precision of greater than 9, the driver truncates the fractional part of the retrieved value to nine digits. If you do not want truncation to occur, in the SELECT statement that retrieves the TIMESTAMP(p) value, you can cast the TIMESTAMP(p) value to a character data type, such as VARCHAR, and use getString to retrieve the value from the ResultSet.