You can use a Teradata object as a single target in a mapping or mapping task.
You can insert, update, upsert, and delete data from Teradata targets. When you configure the advanced target properties, you configure properties specific to Teradata.
System Operators
Teradata PT API uses the Load, Update, and Stream system operators to write data to Teradata.
Specify the type of system operator your require in the target properties. When you run the task, the Secure Agent uses Teradata PT API to connect to Teradata to load, update, or stream data by using the Teradata PT API Load, Update, or Stream system operators.
Load System Operator
The Load system operator loads a large volume of data at high speed into an empty Teradata table. You can use the Load operator to load initial data to Teradata tables as it inserts data into individual rows of a target table.
Before you load data to Teradata tables, you must ensure that the target table is empty.
Update System Operator
You can use the Update system operator to perform insert, update, upsert, and delete operations against Teradata database tables. You can load data to empty or existing Teradata tables.
You can perform update, upsert, and delete operations after you define a primary key in the target table. The Secure Agent does not retain the primary key information of a Teradata table during metadata import. You must therefore select the target columns in the Update Columns field in the target properties. The target columns serve as a key when you run an update, upsert, or delete operation.
Stream System Operator
You can use the Stream system operator to perform high-speed parallel inserts to empty or existing Teradata tables without locking target tables.
You can perform update, upsert, and delete operations after you define a primary key in the target table. The Secure Agent does not retain the primary key information of a Teradata table during metadata import. You must therefore select the target columns in the Update Columns field in the target properties. The target columns serve as a key when you run an update, upsert, or delete operation.
You can maintain current and accurate data for immediate decision making. The Stream system operator uses macros to modify tables. The Stream system operator creates macros before Teradata PT API begins loading data and removes them from the database after Teradata PT API loads all rows to the target. If you do not specify a macro database, Teradata PT API stores the macros in the log database.
Error Limit for the Stream Operator
Teradata PT API terminates the Stream system operator job after it reaches the maximum number of records that can be stored in the error table.
The error limit is approximate because the Stream operator sends multiple rows of data at a time to the Teradata table. By the time Teradata PT API processes the message that indicates that the error limit has been exceeded, it might have loaded more records into the error table than the number specified in the error limit.
By default, the error limit value is unlimited. The error limit applies to each instance of the Stream system operator.
Row-level Processing
When you write data to a Teradata target, you can configure how Teradata PT API treats duplicate rows, missing rows, and extra rows.
Duplicate Rows
You can configure how Teradata PT API handles duplicate rows when it tries to insert or upsert rows in the target table.
You can select one of the following values:
•None. If Teradata PT API receives a row marked for insert or upsert that causes a duplicate row in the target table, Teradata PT API does not write the row to the error table and does not mark it as an error row in the session log.
•For Insert. If Teradata PT API receives a row marked for insert that exists in the target table, Teradata PT API writes the row to the error table and marks it as an error row in the session log.
•For Update. If Teradata PT API receives a row marked for update that causes a duplicate row in the target table, Teradata PT API writes the row to the error table and marks it as an error row in the session log.
•Both. If Teradata PT API receives a row marked for insert or upsert that causes a duplicate row in the target table, Teradata PT API writes the row to the error table and marks it as an error row in the session log.
Missing Rows
You can configure how Teradata PT API handles rows that do not exist in the target table when it tries to update or delete rows.
You can select one of the following values:
•None. If Teradata PT API receives a row marked for update or delete but it is missing in the target table, Teradata PT API does not write the row to the error table and does not mark it as an error row in the session log.
•For Update. If Teradata PT API receives a row marked for update but it is missing in the target table, Teradata PT API writes the row to the error table and marks it as an error row in the session log.
•For Delete. If Teradata PT API receives a row marked for delete but it is missing in the target table, Teradata PT API writes the row to the error table and marks it as an error row in the session log.
•Both. If Teradata PT API receives a row marked for update or delete but it is missing in the target table, Teradata PT API writes the row to the error table and marks it as an error row in the session log.
Extra Rows
You can configure how Teradata PT API marks error rows when it tries to update or delete multiple target table rows.
You can select one of the following values:
•None. If Teradata PT API receives a row marked for update or delete that affects multiple rows in the target table, Teradata PT API does not write the row to the error table and does not mark it as an error row in the session log.
•For Update. If Teradata PT API receives a row marked for update that affects multiple rows in the target table, Teradata PT API writes the row to the error table and marks it as an error row in the session log.
•For Delete. If Teradata PT API receives a row marked for delete that affects multiple rows in the target table, Teradata PT API writes the row to the error table and marks it as an error row in the session log.
•Both. If Teradata PT API receives a row marked for update or delete that affects multiple rows in the target table, Teradata PT API writes the row to the error table and marks it as an error row in the session log.
This attribute is available only for the Stream system operator.
Working with Log, Error, and Work Tables
When you run a session that extracts data from or loads data to Teradata using Teradata PT API, Teradata PT API creates the following tables:
•Log Tables. Stores Teradata PT API restart and log information. Teradata PT API creates one log table for each partition.
•Error Tables. Logs Teradata errors and rejected data when a session runs. Teradata PT API creates two error tables for each partition.
•Work Tables. Stores¾ data when you run a session that uses the Update system operator. Teradata PT API creates one work table for each partition.
Log Tables
Enter a log table name when you configure a task to load to Teradata. You can also choose to create the log table in a log database, a working database, or under the default database. Choose where you want to create the log table when you configure a task to load data to Teradata.
The following table describes the advanced target properties that allow you to specify the log table information:
Property
Description
Log Database
Name of the database that stores the log tables. If you do not enter a log database name in the task properties or a database name in the connection object, Teradata PT API stores the log tables under the user.
Log Table Name
Name of the log table.
If you do not specify a log table name, the Secure Agent uses the name <log_database>.INFA_LT_<number>. The exact table name appears in the session log.
When a task fails, see the log table for more information. Before you run the task again, drop the log table or enter a different table name in the advanced target properties.
Error Tables
Teradata writes rejected data to error tables ErrorTable1 and ErrorTable2.
ErrorTable1 contains data rejected for the following reasons:
•Data conversion errors
•Constraint violations
•Access Module Processor configuration changes
ErrorTable2 contains data rejected for the following reasons:
•Unique primary index constraint violations
•Load driver job acquisition phase errors
You can enter a name for each error table when you configure a task to load data to Teradata. You can also choose to create the error tables in an error database, a working database, or under the default database. Choose where you want to create the error tables when you configure a task to load data to Teradata.
The following table describes the advanced target properties that allow you to specify error table names:
Property
Description
Error Database
Name of the database that stores the error tables. If you do not enter an error database name in the advanced target properties or a database name in the connection object, Teradata PT API stores the error tables under the user.
Error Table Name1
Name of the first error table.
If you do not specify a name for the first error table, the Secure Agent uses the name <error_database>.INFA_ET1_<number>. The exact table name appears in the session log.
Error Table Name2
Name of the second error table.
If you do not specify a name for the second error table, the Secure Agent uses the name <error_database>.INFA_ET2_<number>. The exact table name appears in the session log.
When a task fails, see the error tables for more information about the errors. Before you run the task again, drop the error tables or enter different table names in the advanced target properties.
Work Tables
The Update system operator uses DML statements for staging data. It creates work tables before Teradata PT API begins loading data and removes them from the database after Teradata PT API loads all rows to the target.
Enter a work table name when you configure a task to load data to Teradata. You can also choose to create the work table in the target database. Choose where you want to create the work table when you configure a task to load data to Teradata.
The following table describes the advanced target properties that allow you to specify work table information:
Property
Description
Work Table Database
Name of the database that stores the work tables created by Teradata PT API when you select the Update system operator. If you do not specify a work table database, Teradata PT API stores the work tables in the target database.
Work Table Name
Name of the work tables when you select the Update system operator.
The Teradata database creates one work table for each target table. If you do not specify a work table name, the Secure Agent uses the name <work_table_database>.INFA<number>_WT. The exact table name appears in the session log.
Drop Log, Error, and Work Tables
If you configure a task to use a Teradata target connection, and enable the Drop Log/Error/Work Tables Teradata target advanced property, the Secure Agent drops existing log, error, and work tables for a session when the session starts.