Teradata MultiLoad

Teradata MultiLoad or MLoad is a command-driven load utility for fast, high volume data maintenance on multiple tables or views in Teradata large databases.

MultiLoad is a command-driven utility that can do fast, high-volume maintenance on multiple tables and views of a Teradata Relational Database Management System (RDBMS).

Using a single MultiLoad job, we can do several different imports and delete tasks on RDBMS tables and views:

• Each MultiLoad import task can do multiple data insert, update, and delete functions on up to five different tables or views.
• Each MultiLoad delete task can remove large numbers of rows from a single table.

We can use MultiLoad to import data from:

• Disk or tape files on a channel-attached client system.
• Input files on a network-attached workstation.
• Individual input module (INMOD) programs write to select, validate, and preprocess input data.
• Access modules.
• Any device is providing correctly formatted source data.

The table or view in the RDBMS receiving the data can be any existing table or view for which we have access privileges for the maintenance tasks we want.

MultiLoad Modes

Teradata has the two modes of the MultiLoad, such as:

1. MultiLoad Import

• Each MultiLoad import task can perform multiple INSERT, UPDATE, DELETE, and UPSERT operation on five target tables in parallel.
• It can also import data from the network-attached system or mainframe attached system using a custom access module.

2. MultiLoad DELETE

• Each MultiLoad delete can remove large volumes of data from a single table.
• The MultiLoad DELETE is used to perform global (all amps) delete operation on just one table.
• The DELETE's main feature is that it bypasses the transient journal (TJ) and can be restarted if it fails before finishing.
• We can't use the primary index in the MultiLoad DELETE operation as primary index access is AMP specific, but MultiLoad DELETE is built for global delete.

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. The first error table stores conversion errors, whereas the second error table stores duplicate records.
• Log Table: Maintains the results from each phase of MultiLoad for restart purposes.
• Worktable: MultiLoad script creates one work table per target table. The work table is used to keep DML tasks and input data.

MultiLoad Features

Teradata MultiLoad has the following features, such as:

• High-performance maintenance operations apply updates to multiple tables in a single pass.
• Best for over 1-2% of rows changed.

Data Conversion Capabilities

If an input data field with character data type is targeted for a column with date data type specification, Teradata MultiLoad can change the input data specification to date before inserting it into the target table.

Following are the conversions that support by the Teradata MultiLoad:

• Numeric-to-numeric
• Character-to-numeric
• Character-to-date
• Date-to-character

Teradata MultiLoad Limitations

Teradata MultiLoad has the following limitations, such as:

• No Unique Secondary Index: MultiLoad does not support unique secondary index (USI) the same as FastLoad, as in the case of USI subtable may create in different AMP where the actual row resides.
  But unlike FastLoad, it supports non-unique secondary index (NUSI) as, in this case, suitable will be created in the same AMP. In MultiLoad, each AMP works in parallel and independently. That's why it supports NUSI but not USI.
• No Referential Integrity: Referential Integrity (RI) on the target table not supported by the Teradata MultiLoad. RI requires too much system checking to maintain the referential constraints to a different table.
• Triggers are not allowed:Triggers are involved with more than one table. MultiLoad deals with only one target table. ALTER the Triggers to the DISABLED status before using MultiLoad.
• No concatenation of the input files: It could impact the restart process if the data were concatenated in different sequences.
• No Aggregate, exponential operator, or arithmetic functions:If we need data conversions, then we using an INMOD to prepare the data before loading it.
• MultiLoad does not support SELECT, Foreign key references, Hash Indexes, Join Indexes, NOPI tables.

How MultiLoad Works

Teradata MultiLoad import has five phases:

• Phase 1 (Preliminary Phase): It performs basic setup activities.
• Phase 2 (DML Transaction Phase): It verifies the syntax of DML statements and brings them to the Teradata system.
• Phase 3 (Acquisition Phase): It brings the input data into work tables and locks it.
• Phase 4 (Application Phase): It applies all DML operations.
• Phase 5 (Cleanup Phase): It releases the table lock.

The following steps involved in a MultiLoad script:

Step 1: Set up the log table.

Step 2: Log on to Teradata.

Step 3: Then, specify the Target, Work, and Error tables.

Step 4: Define the INPUT file layout.

Step 5: Also, define the DML queries.

Step 6: Name the IMPORT file.

Step 7: And specify the LAYOUT to be used.

Step 8: Initiate the Load.

Step 9: And last, Finish the load and terminate the sessions.

Example

Create a text file with the following records and name the file as emp.txt.

The following example is a MultiLoad script that reads records from the employee table and loads into the Emp_Stg table.

Executing a MultiLoad Script

Once the input file emp.txt is created, and the MultiLoad script is named as EmpLoad.ml, then we can run the MultiLoad script using the following command in UNIX and Windows.