Comparison of the Teradata loading utilities
The article contains comparison and main features of the data loading tools provided by Teradata. The tutorial illustrates main features of Teradata Multiload , FastLoad and TPump (Parallel Data Pump) and provides sample real-life uses of those tools.
Scroll down for the sample scripts which illustrate different ways to load a sample fixed-length extract into a Teradata database using FastLoad, MultiLoad and Parallel Data Pump (TPump).
Teradata Fast Load
Main use: to load empty tables at high speed.
The target tables must be empty in order to use FastLoad
Supports inserts only - it is not possible to perform updates or deletes in FastLoad
Although Fastload uses multiple sessions to load the data, only one target table can be processed at a time
Teradata Fastload does not support join indexes, foreign key references in target tables and tables with secondary index defined. It is necessary to drop any of the constraints listed before loading and recreate them afterwards.
The maximum number of concurrent Teradata Fastload tasks can be adjusted by a system administrator.
Fastload runs in two operating modes: Interactive and Batch
Duplicate rows will not be loaded
Teradata Multi Load
Main use: Load, update and delete large tables in Teradata in a bulk mode
Efficient in loading very large tables
Multiple tables can be loaded at a time.
Updates data in a database in a block mode (one physical write can update multiple rows)
Uses table-level locks
Resource consumption: loading at the highest possible throughput
Duplicate rows allowed
TERADATA PARALLEL DATA PUMP (TPUMP)
Main use: to load or update a small amount of target table rows
Sends data to a database as a statement which is much slower than using bulk mode
TPump uses row-level hash locks
Resource consumption: loading speed can be adjusted using a built-in resource consumption management utility. The throughput can be turned down in peak periods.
TPump does not support MULTI-SET tables.
