Introduction to Teradata Multiload and Fastload

What is the Teradata Multiload?
Teradata Multiload is a utility that supports loading data (INSERT) and other DML statements (UPDATE, DELETE, UPSERT). MultiLoad groups changes for efficient bulk processing; actual I/O depends on the workload, indexes and recovery activity. Furthermore, Teradata can process several tables at once.
What are the Limitations of the Teradata Multiload?
The target tables must not have a unique secondary index (USI); forced referential integrity is impossible, but we can use soft referential integrity. Furthermore, the target table must not have a join or hash index. Triggers are also not allowed (but this will be rare in a data warehouse). Nevertheless, NUSI is permitted.
A difference must be considered when using a MultiLoad instead of a FastLoad: the target tables must have a primary index; NOPI tables are not allowed. We can not load column partitioned tables with the MultiLoad.
What Tasks Can the Teradata Multiload Perform?
There are two main tasks available. IMPORT tasks allow you to combine DML statements to multiple target tables simultaneously, but consider that the Primary Index column(s) updates are not allowed.
DELETE tasks allow the non-transactional deletion of rows from a table.
What are the Advantages of using a Multiload?
Like the Fastload, the Multiload is a Bulkload variant that does not need the Transient Journal. Data is efficiently brought to the AMPs in blocks, and the parallelism of the Shared Nothing Architecture is fully exploited. Unlike the Fastload, MultiLoad permits both empty and populated target tables.
What are the individual phases of Teradata Multiload?
– The Preliminary Phase does the initialization
– The DML phase moves the DML steps to the AMPS
– The Acquisition phase sends the data to the AMPS and sorts it
– The Application phase applies the input data to the tables
– The end phase, does the cleanup
What happens in the Preliminary Phase?
The preliminary phase is responsible for syntax checking, locking tables, connecting the sessions, creating work tables, error tables, and the restart log.
What happens in the DML Phase?
Teradata stores the DML steps in work tables and links DML statements with the related records in the DML phase.
What happens in the Acquisition Phase?
Data is moved in blocks “round-robin” to the AMPs, bypassing any processing by the Parsing Engine, making it very efficient. Each receiving AMP inspects the PI, calculates the ROWHASH, and forwards the rows to the target AMP’s work table(s). Next, the rows are sorted by ROWHASH by each AMP.
What happens in the Application Phase?
The work table changes are individually applied to the target table(s) by the AMPs. Grouping changes can reduce repeated block access; one physical read and write per block is not a universal guarantee. Any existing NUSI is maintained afterward. The utility uses its bulk-load recovery protocol rather than ordinary per-row transient journaling; this is not an ordinary transactional atomicity guarantee across every phase.
What happens in the Cleanup Phase?
Successful cleanup releases utility resources; error and restart tables may remain depending on errors and job state. Inspect the utility log before removing recovery artifacts; locks are released, and the dictionary cache for target tables is flushed.
What is the Multiload DELETE Task?
Multiload DELETE is a more efficient way of deleting many rows, as no transient journal is used; less I/Os and disk space are required than a normal DELETE.
The Multiload DELETE is restartable from the point of failure, while a transactional DELETE causes a ROLLBACK and has to restart from the beginning.
The DELETE task is not using work tables, as changes are applied to each record of exactly one target table.
Can the Tables be queried during a Multiload?
Reader compatibility depends on the utility phase and lock state. Check the documented phase restrictions for the installed client/database release; do not assume ACCESS readers remain available throughout application and recovery. No utility recovery run was performed for this review.
MultiLoad does not run in isolation: how the utilities are throttled as a workload of their own and the smaller-volume alternative in BTEQ.
Building or improving a data pipeline?
Discuss ingestion, transformations, orchestration, quality checks and operational handover.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free book