In Aster database, the time datatype has a maximum precision of 6 digits of sub-second precision (microseconds). Data transferred through the Aster-Informatica Connector has a maximum precision of 3 digits of sub-second precision (milliseconds). For example, an Aster database value of 12:29:59.001999 becomes 12:29:59.001 after being transferred via the Aster-Informatica Connector.
The loss of precision results from the way time is represented in Informatica and from the JDBC datatype used in the Connector. Informatica maps the data type time_without_time_zone to ODBC SQL_TIME, which has a maximum of 3 digits of sub-second precision. The Aster-Informatica Connector uses the JDBC Time datatype for export, which also has a maximum of 3 digits of sub-second precision. So the Connector truncates values with greater than millisecond precision.
Use this workaround in the PowerCenter Designer to export or import microsecond time or timestamp values:
-
Open the Source Analyzer and make these changes:
- Double-click the table to open the Edit Tables dialog box.
- Click the Columns tab.
- For the time field, change its type to varchar and its precision to 15.
- For the timestamp field, change the type to varchar and its precision to 26.
- Save the repository.
-
Open the Target Designer and make these changes for the Target field.:
- Double-click the table to open the Edit Tables dialog box.
- Click the Columns tab.
- For the time field, change its type to varchar and its precision to 15.
- For the timestamp field, change the type to varchar and its precision to 26.
- Save the repository.