In this article, we will learn about MULTILOAD UTILITY in Teradata
MultiLoad can load multiple tables at a time and it can also perform different types of tasks such as INSERT, DELETE, UPDATE and UPSERT. It can load up to 5 tables at a time and perform up to 20 DML operations in a script.
A single Teradata MultiLoad job performs a number of different import and delete tasks on database tables and views:
- Each Teradata MultiLoad import task can do multiple data insert, update, and delete functions on up to five different tables or views.
- Each Teradata MultiLoad delete task can remove large numbers of rows from a single table
Use Teradata MultiLoad to import data from:
- Disk or tape files (using a custom Access Module) on a mainframe-attached client system
- Input files on a network-attached workstation
- Special input module (INMOD) programs written to select, validate, and preprocess input data
- Access modules
- Any device providing properly formatted source data
Each Multiload job contains 5 phases.
- Parses and validates all of the Teradata MultiLoad commands and Teradata SQL statements in a Teradata MultiLoad job
- Establishes sessions and process control with Teradata Database
- Submits special Teradata SQL requests to Teradata Database
- Creates and protects temporary work tables and error tables in Teradata Database
DML Transaction :
Submits the DML statements specifying the insert, update, and delete tasks to Teradata Database
- Imports data from the specified input data source
- Evaluates each record according to specified application conditions
- Loads the selected records into the worktables in Teradata Database (There is no acquisition phase activity for a Teradata MultiLoad delete task.)
- Acquires locks on the specified target tables and views in Teradata Database
- For an import task, inserts the data from the temporary work tables into the target tables or views in Teradata Database
- For a delete task, deletes the specified rows from the target table in Teradata Database
- Updates the error tables associated with each Teradata MultiLoad task
- Forces an automatic restart/rebuild if an AMP went offline and came back online during the application phase
- Releases all locks on the target tables and views
- Drops the temporary work tables and all empty error tables from Teradata Database
- Reports the transaction statistics associated with the import and delete tasks
MultiLoad requires a work table, a log table and two error tables in addition to the target table.
Log Table − Used to maintain the checkpoints taken during load which will be used for restart.
Error Tables − These tables are inserted during load when an error occurs. First error table stores conversion errors whereas second error table stores duplicate records.
Log Table − Maintains the results from each phase of MultiLoad for restart purpose.
Work table − MultiLoad script creates one work table per target table. Work table is used to keep DML tasks and the input data.
How to invoke multiload job :
multiload < multiloadjobscriptfile
How it works :
Teradata MultiLoad processes a series of commands and Teradata SQL statements which are usually entered as a batch mode job script. The Teradata MultiLoad commands perform session control and data handling of the data transfers. The Teradata SQL statements do the actual maintenance functions on the database tables and views.
Multiload supports checkpoints which is the main thing if job gets aborted to re-launch the job from failure point instead of from begining.
Teradata MultiLoad supports the use of checkpoints. Checkpoints are entries posted to a restart log table at regular intervals during the Teradata MultiLoad data transfer operation. If processing stops while a Teradata MultiLoad job is running, restart the job at the most recent checkpoint.
For example, assume 1,000,000 records are loading in a table and have specified checkpoints every 50,000 records. Then Teradata MultiLoad pauses and posts an entry to the restart log table whenever multiples of 50,000 records are successfully sent to Teradata Database.
If the job stops after record 60,000 has been loaded, restart the job at the record immediately following the last checkpoint—record 50,001.
Sample Mload Script :
.BEGIN MLOAD TABLES Employee_Stg;
.FIELD in_EmployeeNo * VARCHAR(10);
.FIELD in_FirstName * VARCHAR(30);
.FIELD in_LastName * VARCHAR(30);
.FIELD in_BirthDate * VARCHAR(10);
.FIELD in_JoinedDate * VARCHAR(10);
.FIELD in_DepartmentNo * VARCHAR(02);
.DML LABEL EmpLabel;
INSERT INTO Employee_Stg (
.IMPORT INFILE employee.txt
FORMAT VARTEXT ','
- Unique Secondary Index not supported on target table.
- Referential integrity not supported.
- Triggers not supported.