Handling Time and Timestamp Data - Aster Client

Teradata Aster® Client Guide

Product
Aster Client
Release Number
7.00
Published
May 2017
Language
English (United States)
Last Update
2018-04-13
dita:mapPath
hki1475000360386.ditamap
dita:ditavalPath
Generic_no_ie_no_tempfilter.ditaval
dita:id
B700-2005
lifecycle
previous
Product Category
Software
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:

  1. Open the Source Analyzer and make these changes:
    1. Double-click the table to open the Edit Tables dialog box.
    2. Click the Columns tab.
    3. For the time field, change its type to varchar and its precision to 15.
    4. For the timestamp field, change the type to varchar and its precision to 26.
    5. Save the repository.
  2. Open the Target Designer and make these changes for the Target field.:
    1. Double-click the table to open the Edit Tables dialog box.
    2. Click the Columns tab.
    3. For the time field, change its type to varchar and its precision to 15.
    4. For the timestamp field, change the type to varchar and its precision to 26.
    5. Save the repository.