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). All DML statements are executed optimally, and changed data blocks are written only once. 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 Buldload 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, the target tables do not have to be empty with the Teradata Multiload.
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. Each data block is read and written precisely once. Any existing NUSI is maintained afterward. As changes are applied as one transaction, no transient journal is needed.
What happens in the Cleanup Phase?
Multiload drops all work tables, error tables, and the log table; locks are being 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 hast 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?
As no exclusive locks are used, dirty reads (with access locks) are possible during the load with Multiload.