1. Features
   1. Batch Mode utility that runs on host system
   2. Fast Load like technology, T-PUMP like functionality
   3. Support upto 5 populated tables
   4. Tables may contain pre-existing data
      1. It can populate both empty as well as populated tables
   5. It neither supports table with secondary indexes nor referential intergrity
   6. Ability to perform multiple maintenance operations with one pass of input files
   7. Ability to perform conditional maintenance based on 'Apply' condition
   8. Ability to do Insert, Update, delete and upserts
   9. Each affected data blocks written only once
   10. Host and Lan support
   11. Full restart capability with log files,even AMP is down
   12. Programmable error limits
   13. Error reporting via error tables
   14. INMOD support
2. Limitations
   1. No retrieval capability (No select statment)
   2. Concatenation of input files allowed
   3. Host will not process arithmetic, aggregate or exponentiation function
   4. Cannot process table defined with USI, refrential integrity, join index, hash indexes or triggers
      1. However NUSI & soft referential integrity are allowed
   5. Import task require use of Index
3. Types of tasks
   1. Import task
      1. Upto 5 intermix of SQL/DML statements depends on 'APPLY' condition
      2. It is always primary Index operation
      3. Permits non-exclusive access to target tables from other users except during Application Phase.
      4. Phases
         1. Preliminary phase
            1. Basic Setup
            2. Validate all statements
            3. Start all sessions
            4. #AMPS + 2 (handling SQL and loggine + alternate logging
            5. Create work table
            6. Create on each AMP for each target table
            7. Create error table
            8. 2 error tables for each target table
            9. Error table 1; Holds general error
            10. Error table 2: For uniqueness violation
            11. Create restart log
            12. Apply locks to the table
            13. Utility lock on table headers which disallows any DDL to the table
         2. DML phase
            1. Steps
            2. Send prototype DML to the Teradata Database - Store DML steps in work tables
            3. Add a USING modifier to the request - Host data to be filled in from input file
            4. Add a “Match Tag” to the request - Allows link between DML and transaction records
         3. Acquisition phase
            1. Steps
            2. Get the data from host and apply it to appropriate AMP worktables
            3. -Duplicate “input records” record for each successful APPLY.
            4. – Add “Match Tag” information to record.
            5. – Make blocks and send “quickpath” to AMPs.
            6. – Deblock and resend record to “correct” AMP.
            7. Reblock and store in worktable of target table
            8. – Sort the reblocked records in the work tables.
            9. – Sort by hash value and sequence to be applied.
            10. Set up transition to the Application phase
            11. – Upgrade locks on target tables to Write.
            12. – Set table headers for Application phase.
            13. – This is effectively the “point of no return”.
            14. – There is no acquisition phase activity for a DELETE Task.
            15. - Error goes to error tables
         4. Application Phase
            1. Execute MLOAD for each target table as a single multi-statement request
            2. – End of host interaction until end of phase.
            3. – AMPs independently apply changes to target tables.
            4. – Executed as a single transaction without rollback.
            5. – Restartable based on last checkpoint.
            6. – No transient journal needed.
            7. Error goes to application error table(Default name : UV_tablename)
         5. End Phase (Cleanup)
            1. Steps
            2. Execute END MLOAD processing as a series of transactions performed by the host utility
            3. – All locks are released.
            4. – Table headers are restored across all AMPs.
            5. – Dictionary cache of Target Tables is spoiled.
            6. – Statistics are reported.
            7. – Final Error Code is reported.
            8. – Target tables are made available to other users.
            9. – Work Tables are dropped.
            10. – Empty Error Tables are dropped.
            11. – Log Table is dropped (if Error Code = 0).
            12. MLOAD Session Logoff:
            13. – LOGOFF request is sent to each AMP with a session
   2. Delete task
      1. Execute single delete statement on single table
4. Advantages
   1. Minimum use PE
   2. Gets the input data to the AMP as quickly as possible
   3. Uses multiple AMP sessions
   4. Uses the parallelism of the amps to apply the changes
   5. Keeps the BYNET activity low with AMP-Local processing
   6. Avoid transient journaling overhead
   7. Allows checkpoint and restartability even with down AMPs
   8. Prevents lengthy rollback of aborted jobs
   9. Allows for max access to the table during processing
   10. Posts error to the special error tables
   11. Provides extensive error statistics
5. Basic Mload statements
   1. .LOGTABLE
      1. Define restart log table
   2. .LOGON
   3. .BEGIN MLOAD TABLES
      1. Tables which will participate in mload
   4. .LAYOUT
   5. .FIELD
   6. .FILLER
   7. .DML LABEL
   8. .IMPORT INFILE --references the name of the input file. FROM m FOR n --optionally defines starting # and ending # THRU k -- of records to process from input file. FORMAT --options – FASTLOAD BINARY TEXT UNFORMAT VARTEXT ‘,’ LAYOUT --references previously defined LAYOUT.
   9. APPLY references LABEL to be applied and conditions under which to do so.
   10. .END MLOAD
   11. .LOGOFF
6. Sample Code
   1. mload Sample code