Troubleshooting
Problem
Extra time stamp in date field read from Oracle
Symptom
After upgrading Oracle JDBC driver (ojdbc6), any date attribute in the Entry object has a TIMESTAMP data type. (example: YYYYmmdd HH:MM:SS.M). Prior to using the updated ojdbc jar the date attribute contained a DATE data type value (example: yyyymmdd)
Cause
Customer moved from Oracle JDBC driver version 9.2 (ojdbc) to version 11.1
Diagnosing The Problem
Found on the Oracle FAQ, the DATE data type has changed.
http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-faq-090281.html#08_01
*** excerpts from the above url ***
Prior to 9.2, DATE mapped to TIMESTAMP
In 9.2, DATE is mapped to Date and TIMESTAMP is mapped to Timestamp.
TIMESTAMP includes nanoseconds
DATE does not
Oracle JDBC 11.1 fixes this problem. Beginning with this release the driver maps SQL DATE columns to java.sql.Timestamp by default.
If for some reason your app is very sensitive to this change and you simply must have the 9i-10g behavior, there is a connection property you can set. Set mapDateToTimestamp to false and the driver will revert to the default 9i-10g behavior and map DATE to Date.
****************
Resolving The Problem
There are a few different methods a customer may use to implement the property in TDI
I. For a specific Oracle JDBC Connector: Use the TDI Config Editor to add 'oracle.jdbc.mapDateToTimestamp:false' to the 'Extra Provider Parameter' field in the Advanced section of the Connector's Connection Tab.
II. To address all Oracle JDBC Connectors in a TDI Server:
a. Add "oracle.jdbc.mapDateToTimestamp=false" to the
<TDI_Solution_Directory>/solution.properties file
OR
b. Add -Doracle.jdbc.mapDateToTimestamp=false to the java command
which starts TDI, found in the <TDI_Install_Directory>/ibmdisrv
For example, the Java Property has been placed before the -cp parameter in the following command:
"$TDI_JAVA_PROGRAM" $TDI_MIXEDMODE_FLAG -Doracle.jdbc.mapDateToTimestamp=false -cp "$TDI_HOME_DIR/IDILoader.jar" "$LOG_4J" com.ibm.di.loader.ServerLauncher "$@" &
Was this topic helpful?
Document Information
Modified date:
22 June 2018
UID
swg21965969