Tuesday, June 2, 2009

Teradata Client

Teradata Client
Teradata BTEQ client software, the Teradata ODBC driver, and any other Teradata client software must be installed on the machine where the Integration Service process runs

Mappings/Transformations
Source - Use ODBC. ODBC is a"native" interface for Teradata; it is not a layer on top of CLI
Lookup & Stored Procedure - Use ODBC
Target - Use ODBC, FASTLOAD, MLOAD, TPUMP

You can use only fixed-width files when you load to Teradata through loaders.
If you specify delimited files, the Informatica Server fails the session

In the control file all columns are defined as CHAR.
During runtime these loaders convert the input data to CHAR and then convert it back to original datatypes

When calling a pre- or post-load stored procedure without any argument, use a dummy value for the argument in the call text properties.
If you do not configure the call text property, the mapping is invalid.

Setting by ODBC
1) Set the TERADATA_HOME, ODBCHOME, and PATH environment variables.
2) Set the shared library environment variable
3) Edit the existing odbc.ini file or copy the odbc.ini file to the home directory and edit it

To run the loader utilities, make sure the local host file on the machine where the Teradata client is installed
Teradata data source in the odbc.ini file: DateTimeFormat=AAA
NoScan=Yes

Session Modes

The default value of SessionMode is Teradata , which is case insensitive.
Change the SessionMode parameter to 'ANSI' in the ODBC DSN in the .odbc.ini file. (SessionMode = ANSI)

When you use ANSI session mode, Teradata does not roll back the transaction when it encounters a row error.
If you choose Teradata session mode, Teradata rolls back the transaction when it encounters a row error.
So if you have a session with a Teradata target as well as a lookup you will want to create a separate .odbc.ini entry(DSN) for the target to avoid this issue.

A Stored Procedure that was compiled in one mode cannot be run in the other mode. You will get Invalid session mode for procedure error.

ANSI mode:
A transaction initiation is always implicit.
A transaction is opened by the first SQL statement executed in a session or by the first statement executed following the close of a transaction.
A transaction is initiated when no transaction is currently active, and a SQL statement is executed.
The COMMIT [WORK] or ROLLBACK [WORK] statements close a transaction.
MULTISET is the default in the CREATE TABLE statement in ANSI mode.

Teradata mode:
Transactions can be implicit or explicit.
Statement failures roll back the entire transaction. An error result rolls back the entire transaction.
Control of character truncation causes no error.
The default in character comparison is NOT CASESPECIFIC.
SET is the default in the CREATE TABLE statement


TIMESTAMP -
To laod timestamp with fractional secs, write the column as varchar2 and in the format 'YYYY-MM-DD HH24:MM:SS.SSSSSS'

You may also encounter an error writing a PowerCenter date/time port to a Teradata timestamp (0) field, or a Teradata timestamp with no fractional seconds.
To resolve this issue - change the column in Teradata to a Timestamp(n) wheren>0
or edit the control file generated-change the length of the field that corresponds to the Timestamp (0) field from CHAR (26) to CHAR (19)

1 comment: