Table of Contents

Search

  1. Preface
  2. Understanding Pipeline Partitioning
  3. Partition Points
  4. Partition Types
  5. Pushdown Optimization
  6. Pushdown Optimization and Transformations
  7. Real-time Processing
  8. Commit Points
  9. Row Error Logging
  10. Workflow Recovery
  11. Stopping and Aborting
  12. Concurrent Workflows
  13. Grid Processing
  14. Load Balancer
  15. Workflow Variables
  16. Parameters and Variables in Sessions
  17. Parameter Files
  18. FastExport
  19. External Loading
  20. FTP
  21. Session Caches
  22. Incremental Aggregation
  23. Session Log Interface
  24. Understanding Buffer Memory
  25. High Precision Data

Advanced Workflow Guide

Advanced Workflow Guide

Configuring Teradata MultiLoad External Loader Attributes

Configuring Teradata MultiLoad External Loader Attributes

Use the following rules and guidelines when you work with the MultiLoad external loader:
  • You can perform insert, update, delete, and upsert operations on targets. You can also use data driven mode to perform insert, update, or delete operations based on an Update Strategy or Custom transformation.
  • For a session with multiple partitions, use the round-robin partition type to route data to a single target file.
  • If you invoke a greater number of sessions than the maximum number of concurrent sessions the database allows, the session may hang. You can set the minimum value for Tenacity and Sleep to ensure that sessions fail rather than hang.
To configure attributes for the Teradata MultiLoad external loader, click Connections > Loader, select the Type, and click Edit.
The following table shows the attributes that you configure for the Teradata MultiLoad external loader:
Attribute
Default Value
Description
TDPID
n/a
Teradata database ID.
Database Name
n/a
Optional database name. If you do not specify a database name, the Integration Service uses the target table database name defined in the mapping.
Date Format
n/a
Date format. The date format in the connection object must match the date format you define in the target definition. The Integration Service supports the following date formats:
  • DD/MM/YYYY
  • MM/DD/YYYY
  • YYYY/DD/MM
  • YYYY/MM/DD
Error Limit
0
Total number of rejected records that MultiLoad can write to the MultiLoad error tables. Uniqueness violations do not count as rejected records.
An error limit of 0 means that there is no limit on the number of rejected records.
Checkpoint
10,000
Interval between checkpoints. You can set the interval to the following values:
  • 60 or more. MultiLoad performs a checkpoint operation after it processes each multiple of that number of records.
  • 1–59. MultiLoad performs a checkpoint operation at the specified interval, in minutes.
  • 0. MultiLoad does not perform any checkpoint operation during the import task.
Tenacity
10,000
Amount of time, in hours, MultiLoad tries to log in to the required sessions. If a login fails, MultiLoad delays for the number of minutes specified in the Sleep attribute, and then retries the login. MultiLoad keeps trying until the login succeeds or the number of hours specified in the Tenacity attribute elapses.
Load Mode
Upsert
Mode to generate SQL commands: Insert, Delete, Update, Upsert, or Data Driven.
When you select Data Driven loading, the Integration Service follows instructions in an Update Strategy or Custom transformation to determine how to flag rows for insert, delete, or update. The Integration Service writes a column in the target file or named pipe to indicate the update strategy. The control file uses these values to determine how to load data to the target. The Integration Service uses the following values to indicate the update strategy:
0 - Insert
1 - Update
2 - Delete
Drop Error Tables
Enabled
Drops the MultiLoad error tables before beginning the next session. Select this option to drop the tables, or clear it to keep them.
External Loader Executable
mload
Name and optional file path of the Teradata external loader executable. If the external loader executable directory is not in the system path, you must enter the full path.
Max Sessions
1
Maximum number of MultiLoad sessions per MultiLoad job. Max Sessions must be between 1 and 32,767.
Running multiple MultiLoad sessions causes the client and database to use more resources. Therefore, setting this value to a small number may improve performance.
Sleep
6
Number of minutes MultiLoad waits before retrying a login. MultiLoad tries until the login succeeds or the number of hours specified in the Tenacity attribute elapses.
Sleep must be greater than 0. If you specify 0, MultiLoad issues an error message and uses the default value, 6 minutes.
Is Staged
Disabled
Method of loading data. Select Is Staged to load data to a flat file staging area before loading to the database. Otherwise, the data is loaded to the database using a named pipe.
Error Database
n/a
Error database name. Use this attribute to override the default error database name. If you do not specify a database name, the Integration Service uses the target table database.
Work Table Database
n/a
Work table database name. Use this attribute to override the default work table database name. If you do not specify a database name, the Integration Service uses the target table database.
Log Table Database
n/a
Log table database name. Use this attribute to override the default log table database name. If you do not specify a database name, the Integration Service uses the target table database.
User Variables
n/a
User-defined variable used in the default control file.
The following table shows the attributes that you configure when you override the Teradata MultiLoad external loader connection object in the session properties:
Attribute
Default Value
Description
Error Table 1
n/a
Table name for the first error table. Use this attribute to override the default error table name. If you do not specify an error table name, the Integration Service uses ET_<target_table_name>.
Error Table 2
n/a
Table name for the second error table. Use this attribute to override the default error table name. If you do not specify an error table name, the Integration Service uses UV_<target_table_name>.
Work Table
n/a
Work table name overrides the default work table name. If you do not specify a work table name, the Integration Service uses WT_<target_table_name>.
Log Table
n/a
Log table name overrides the default log table name. If you do not specify a log table name, the Integration Service uses ML_<target_table_name>.
Control File Content Override
n/a
Control file text. Use this attribute to override the control file the Integration Service uses when it loads to Teradata.


Updated November 14, 2019