ODI Knowledge Modules Generate Incorrect Lengths for Teradata TIMESTAMP Datatype Columns (Doc ID 849974.1)

Symptoms

The Oracle Data Integrator (ODI) "LKM SQL to Teradata (TTU)" Knowledge Module generates a datatype of:

• TIMESTAMP(3) -- in the "Create work table" step, but a
• TIMESTAMP(26) -- in the "Create view on work table" step which is incorrect.

The Teradata view is created from the LKM "Create View on work table" step, as follows:

create view <DATABASE_SCHEMA>.C$_0<TABLE2>
as select
cast(cast(C1_COLUMN as VARCHAR(46)) as timestamp(26)) C1_COLUMN,
...
from <DATABASE_SCHEMA>.C$_0<TABLE1>

The above code fails and following message is shown:

5401 : HY000 : com.teradata.jdbc.jdbc_4.util.JDBCException: [Teradata Database] [TeraJDBC13.00.00.06]
[Error 5401] [SQLState HY000] Number of fractional digits out of range.

For example, this may happen when:

• Loading Microsoft SQLServer DATETIME field...
• Into Teradata TIMESTAMP(3) FORMAT 'YYYYMMDDHHMISS.S(3)' NOT NULL target...

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.