Teradata - MultiLoad

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. The target table is not required for MultiLoad.

MultiLoad supports two modes −

  • IMPORT
  • 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. 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.

Limitation

MultiLoad has some limitations.

  • Unique Secondary Index not supported on target table.
  • Referential integrity not supported.
  • Triggers not supported.

How MultiLoad Works

MultiLoad import has five phases −

  • Phase 1 − Preliminary Phase – Performs basic setup activities.

  • Phase 2 − DML Transaction Phase – Verifies the syntax of DML statements and brings them to Teradata system.

  • Phase 3 − Acquisition Phase – Brings the input data into work tables and locks the table.

  • Phase 4 − Application Phase – Applies all DML operations.

  • Phase 5 − Cleanup Phase – Releases the table lock.

The steps involved in a MultiLoad script are −

  • Step 1 − Set up the log table.

  • Step 2 − Log on to Teradata.

  • Step 3 − Specify the Target, Work and Error tables.

  • Step 4 − Define INPUT file layout.

  • Step 5 − Define the DML queries.

  • Step 6 − Name the IMPORT file.

  • Step 7 − Specify the LAYOUT to be used.

  • Step 8 − Initiate the Load.

  • Step 9 − Finish the load and terminate the sessions.

Example

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

101,Mike,James,1980-01-05,2010-03-01,1  
102,Robert,Williams,1983-03-05,2010-09-01,1 
103,Peter,Paul,1983-04-01,2009-02-12,2 
104,Alex,Stuart,1984-11-06,2014-01-01,2 
105,Robert,James,1984-12-01,2015-03-09,3

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

.LOGTABLE tduser.Employee_log;  
.LOGON 192.168.1.102/dbc,dbc; 
   .BEGIN MLOAD TABLES Employee_Stg;  
      .LAYOUT Employee;  
      .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 (
         EmployeeNo,
         FirstName,
         LastName,
         BirthDate,
         JoinedDate,
         DepartmentNo
      )  
      VALUES (
         :in_EmployeeNo,
         :in_FirstName,
         :in_Lastname,
         :in_BirthDate,
         :in_JoinedDate,
         :in_DepartmentNo
      );
      .IMPORT INFILE employee.txt  
      FORMAT VARTEXT ','
      LAYOUT Employee
      APPLY EmpLabel;  
   .END MLOAD;  
LOGOFF;

Executing a MultiLoad Script

Once the input file employee.txt is created and the multiload script is named as EmployeeLoad.ml, then you can run the Multiload script using the following command in UNIX and Windows.

Multiload < EmployeeLoad.ml;
Advertisements