The data load can perform multiple types of DML operations, including INSERT, UPDATE, DELETE and UPSERT on up to five (5) empty or populated target tables at a time.
Limitations of Multiload
Unique Secondary Indexes are not supported on a Target Table:Like FastLoad, MultiLoad does not support Unique Secondary Indexes (USIs). But unlike FastLoad, it does support the use of Non-Unique Secondary Indexes (NUSIs) because the index subtable row is on the same AMP as the data row.
Referential Integrity is not supported: The Referential Integrity defined on a table would take more system checking to prevent referential constraints.
Triggers are not supported at load time: Disable all the Triggers prior to using it.
No concatenation of input files is allowed: It could impact are restart if the files were concatenated in a different sequence or data was deleted between runs.
No Join Indexes: All the join indexes must be dropped before running a MultiLoad and then recreate them after the load is completed
Will not process aggregates, arithmetic functions or exponentiation:If you need data conversions or math, you might be better off using an INMOD to prepare the data prior to loading it.
Multiload requires mainly Four components
Log Table:Log table stores the processing record information during load.This table contains one row for every Multiload running on the system.
Work Table(s): MultiLoad will automatically create one worktable for each target table. Usually in IMPORT mode multiload could have one or more work tables and in DELETE moode you have ony one. The Purpose of work tables are 1) to perform DM tasks 2) APPLYing the input data to the AMPs.
Error Tables: Like Fastload, Multiload also two error tables
The first Error Table (ET). It contains all translation and constraint errors that may occur while the data is being acquired from the source(s)
The second Uniqueness Violation (UV) table that stores rows with duplicate values for Unique Primary Indexes (UPI).
Target table: Target tables can have data. Multiload can load the data where target table alredy loaded.
MultiLoad Has Five IMPORT Phases:
Phase 1: Preliminary Phase : Ita Basic setup phase.Its used for several preliminary set-up activities for a successful data load.
Phase 2: DML Transaction Phase: All the SQL Data Manipulation Language (DML) statements are sent to Teradata database as Multilaod supports multiple DML functions.
Phase 3: Acquisition Phase: Once the setup completes the PE's plan stored on each AMP.Then Locks the table headers and the actual input data will also be stored in the worktable.
Phase 4: Application Phase: In this phase all DML opreations are applied on target tables.
Phase 5: Cleanup Phase: Table locks will be released and all the intermediate work tables will be dropped.
MultiLoad has full RESTART capability in all of its five phases of operation.
Sample Multiload Script:
The script on the following page follows these steps:
- Setting up a Logtable
- Logging onto Teradata
- Identifying the Target, Work and Error tables
- Defining the INPUT flat file
- Defining the DML activities to occur
- Naming the IMPORT file
- Telling MultiLoad to use a particular LAYOUT
- Telling the system to start loading
- Finishing loading and logging off of Teradata
LOGTABLE EMPDB.EMP_TABLE_LOG;
LOGON TDDB/USERNAME,PWD;
.BEGIN IMPORT MLOAD
TABLES EMPDB.EMP_TABLE
WORK TABLES EMPDB.EMP_WT
ERROR TABLE EMPDB.EMP_ET
EMPDB.EMP_UV;
LAYOUT FILECOLDESC1;
.FIELD EMP_NUM * INTEGER ;
.FIELD SALARY * DECIMAL(8,2);
.DML LABLE EMP_UPD;
UPDATE EMPDB.EMP_TABLE
SET SALARY=:SALARY
WHERE EMP_NUM=:EMP_NUM;
.IMPORT INFILE C:\TEMP\MLOAD_FLAT_FILE.txt
LAYOUT FILECOLDESC1
APPLY EMP_UPD;
.END MLOAD;
.LOGOFF;
