MultiLoad IMPORT has five phases.
Phase 1: Preliminary Phase
- Parses and validates all Teradata MultiLoad commands and Teradata SQL statements.
- Establishes MultiLoad sessions with the Teradata database. The default is the number of available AMPs. For a small system the general thumb rule is: number of AMPs + two. These two extra control sessions are for handling the SQL and logging. In case of larger system with hundreds of AMPs, the SESSIONS option is available to lower the default.
- Creates all support tables, i.e. log table(s), error tables, work table(s).
- Apply utility Lock to the target tables.
Phase 2: DML Transaction phase
- All the DML statements are parsed and send to the appropriate worktable for each target table. Later, during the acquisition phase data will also be stored in the worktable so it may be applied in the application phase.
Phase 3: Acquisition Phase
- MultiLoad will now start importing unsorted data from the source in the form of 64K data blocks and send it to the AMPs.
- Teradata does not care about which AMP receives the data blocks. As soon as AMP receives data block, it will start examining each row and send it to the proper AMP using hash algorithm. In this moment, data will store into the worktables of the destination AMP.
- There will no Acquisition phase for MultiLoad DELETE operation.
Phase4: Application Phase
- Acquires load locks to the target tables and views in the Teradata database.
- Each block in the worktable is read once and applies the DML statement like INSERT, UPDATE or DELETE and write the block of data to the actual target table.
- A checkpoint will be created after each successful write of data block which will help to RESTART the process from the fail point.
- Any error will be written to the proper error table.
Phase 5: Clean up Phase
- 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