Multi Load Utility

Mload is a command driven utility which is used to Insert/update/Delete Operations.

Following tables are created in Mload :

Error Tables : Requires 2 error tables per target table , one for Constraint violations and other for UPI violations .


Work Tables : It holds the DML tasks that are  requested and the input data which is ready to APPLY to AMPS.

Log Table: keeps a record of results from each phase of the load so that multi-load knows proper point from which to restart.


There are five phases in Multi-load , Please find the below 5 steps ...

Preliminary Phase :
First Checks SQL commands or Multiload commands are valid or not .
Establishes multiload sessions with Teradata.
Creates Error Tables, Work Tables and log table
DML Phase :
All the SQL DML statements are sent to database.
PE will  parses the DML and releases a step-by-step plan to execute the request.
This execution plan will be communicated to each AMP and will be stored in appropriate work tables for each target table.
during the Acquisition phase the actual input data is stored in the worktable so that it can  be applied the Application Phase.

Acquisition Phase :
MultiLoad will now acquire the data in large, unsorted 64K blocks from the host and will send it to the AMPs (round Robin). 
The AMP hashes every row on the primary index and sends it to the BYNET to the proper AMP where it will  be used ultimately. But the row will not get inserted into its target table, just yet.
The AMP puts all of the hashed rows it has got from the other AMPs into the worktables where it assembles them into the SQL.
Now the utility will place a load lock on each target table for preparation for the Application Phase.
Application Phase :

In this phase loading of  the data from Worktable to Actual table will be done .
The main use of this phase is to write or APPLY, the specified changes to both  target tables and NUSI subtables.
End Phase – Basic Cleanup :

If the last error code is zero (0), all of the job steps have been completed successfully (i.e., all has certainly ended well). 
All  the empty error tables, the worktables and the log table are dropped. All locks  both Teradata and MultiLoad are released.
MultiLoad session is logged off.

Limitations of Multi-Load:

    * MultiLoad Utility will notsupport SELECT statement.
    * Concatenation of multiple input data files is not allowed.
    * MultiLoad will not support Arithmetic functions i.e. ABS, LOG etc. in Mload Script.
    * MultiLoad will not support Exponentiation and Aggregator Operators i.e. AVG, SUM        etc...  in Mload Script.
    * MultiLoad will notsupport USIs (Unique Secondary Indexes), Referential Integrity,              Join Indexes, Hash Indexes and Triggers.
    * Import task requires the use of PI (Primary Index).