What is the Teradata Multiload?
Teradata Multiload is a utility that supports not only the loading of data (INSERT) but also other DML statements ( UPDATE, DELETE, UPSERT). All DML statements are executed in an optimized way, and changed data blocks are written only once. Furthermore, several tables can be processed at once.
What are the Limitations of the Teradata Multiload?
The target tables must not have a unique secondary index (USI); also forced referential integrity is not possible, but soft referential integrity can be used. Furthermore, the target table must not have a join index or hash index. Triggers are also not allowed (but this will be rather rare in a data warehouse). Nevertheless, NUSI is allowed.
A difference that has to be considered when using a MultiLoad instead of a FastLoad: the target tables must have a primary index; NOPI tables are not allowed. Column partitioned tables can not be loaded with the MultiLoad.
What Tasks can the Teradata Multiload perform?
There are two main tasks available. IMPORT tasks allow you to apply a combination of DML statements to multiple target tables simultaneously, but consider that updates of the Primary Index column(s) 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 of tables, connection of the sessions, and the creation of work tables, error tables, and the restart log.
What happens in the DML Phase?
In the DML phase, Teradata stores the DML steps in work tables and links DML statements with the related records.
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 being sorted by ROWHASH by each AMP.
What happens in the Application Phase?
The work table changes are applied to the target table(s) individually by the AMPs. Each data block is read and written exactly once. Any existing NUSI is maintained afterward. As changes are applied as one single 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 a huge amount of rows, as no transient journal is used; less I/Os and disk space are required than with 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.