I am writing a PT script which loads from an oracle source to Teradata.
PROBLEM_COL in Oracle is defined as DECIMAL(10,0). In Teradata PROBLEM_COL is defined as Integer.
The load fails, and all rows are inserted into the error table.
Reviewing the logs I believe I see the problem:
Column Name Offset Length Type
PROBLEM_COL 200 4 INTEGER
I understand that Teradata stores Integers as a 4 byte binary values, hence the length of 4.
Whereas in the SELECT * FROM statement, Oracle brings in this:
Column Name Offset Length Type
CAST(PROBLEM_COLASFLOAT(10)) 200 8 BINARY_DOUBLE
I’ve tried casting it as NUMBER(10,0), DECIMAL(10), INTEGER, and many variations where I change the number in the brackets. I can’t get the length to be any shorter than six.
To confirm this is the issue, I picked a set of records where PROBLEM_COL is a 4 digit value. I did a TO_CHAR(SUBSTR(PROBLEM_COL),1,4). And the load completed successfully. There are values in PROBLEM_COL much bigger than 4 digits so I can’t use this method.
How am I supposed to handle this case?
This is a known issue when moving data from non-Teradata databases to Teradata.
In general, the ODBC operator does not perform data conversions, or data type conversions.
The ODBC operator does not know the layout of the destination (target table).
If necessary, I would recommend the user load to a staging table and then perform an INSERT-SELECT.
One thing to consider is to use TO_CHAR (or some equivalent function) to convert the field to character, and code the schema so the column is VARCHAR, and then let Teradata convert from character to integer.
Oracle can be a challenge in other areas.
An INTEGER in Oracle is really stored as a NUMBER (sometimes NUMBER(38,0)), and moving that data to a Teradata INTEGER is challenging.
But since the operator does not perform conversions, and the operator does not know the layout of the target table, it is up to the user to either make sure the target table can handle the data as it arrives from Oracle, or CAST the fields in the SELECT statement to match the target table (and let the ODBC driver do the conversion).
The documentation states that TPT is only certified with the ODBC drivers from DataDirect.
We bundle the drivers with TPT starting in 15.0 (although the user must obtain license files from Teradata). The license files are free.
There is an impending data migration for an application stack within our organisation.
An upgrade from the current Teradata 14.0 to 15.0 is planned in the final state.
On the current 14.0 software platform data is extracted typically from Oracle sources through TPT using DataDirect drivers. If possible we would prefer to get rid of the additional cost and maintenance associated with DataDirect.
While your comment indicates that the drivers are bundled with TPT, would you be able to advise:
a. If there is a standard approach to deploy and test TPT without DataDirect on 15.0 ?
b. Is there additional cost associated with the user license you alluded to?
If this is readily available information do point me to the documents and I could take it from there?
Thanks in advance!