MultiLoad
From Wikipedia, the free encyclopedia
 |
This article or section is written like an advertisement. Please help rewrite this article from a neutral point of view. Mark blatant advertising which would require a fundamental rewrite in order to become encyclopedic for speedy deletion, using {{db-spam}}. (December 2007) |
|
|
This article lacks information on the notability of the subject matter. Please help improve this article by providing context for a general audience, especially in the lead section. (September 2007) |
MultiLoad is a Teradata utility.
Contents |
[edit] Teradata MultiLoad – Data Maintenance
A parallel load utility, Teradata MultiLoad is used to create and maintain Teradata Databases. MultiLoad optimizes operations that rapidly acquire, process, and apply data to tables in a Teradata Database.
For data maintenance, MultiLoad updates, inserts, upserts, and deletes large volumes of data into empty or populated tables. MultiLoad works at the data block level, providing a faster alternative to insert/select operations that touch a significant portion of the target table. A single MultiLoad job can maintain up to five Teradata tables by extracting large volumes of data, locking the destination tables, then loading data rapidly using block level updates. Like FastLoad, MultiLoad runs on a variety of client platforms, operates in a fail-safe mode, and is fully recoverable.
[edit] Features
- High-performance maintenance operations applies updates to multiple tables in single pass
- Best for over 1-2% of rows changed
[edit] Supported Platforms
- NCR UNIX SVR4 MP-RAS
- IBM z/OS (MVS and USS)
- z/OS VM
- Microsoft Windows 2000, XP, and Server 2003
- Sun Solaris SPARC
- IBM
- HP-UX
[edit] Description
The Teradata MultiLoad utility gives users an efficient way to deal with batch maintenance of large databases. MultiLoad is a command-driven utility used to do fast, high-volume maintenance on multiple tables and views of a Teradata Relational Database Management System (RDBMS).
Using a single MultiLoad job, a user can execute a number of different import 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.
MultiLoad can import data from:
- Disk or tape files on a channel-attached client system
- Input files on a network-attached workstation
- Special input module (INMOD) are programs a user can write to select, validate, and preprocess input data
- Access modules
- Any device providing properly formatted source data
The table or view in the RDBMS receiving the data can be any existing table or view for which the user has access privileges for the desired maintenance tasks.
[edit] A Sample Script
.LOGTABLE ${DX_LOG}.TAB1_LOG;
/*
################################################## ##########################
# Logon String for Teradata
################################################## ##########################
*/
.LOGON ${LOGON};
/*
################################################## ##########################
# MLOAD Tables
################################################## ##########################
# MultiLoad Work Tables
# The data is loaded into worktable before it is sent to the target
# table.
################################################## ##########################
# MultiLoad Error Tables
# The tables with ERR suffix contains error results
# The tables with UV suffix contains duplicate records
################################################## ##########################
# Error Limit at which to stop Multiload
# ERRLIMIT : The number of rejected records before failure
# This excludes the Duplicate records
# CHECKPOINT : Multiload checkpoint after X number of records
# SESSIONS : Maximum sessions should be equal to the number
# of AMPs on the Teradata Server
################################################## ##########################
*/
.BEGIN IMPORT MLOAD TABLES
${DX_TAB}.TAB1
,${DX_DUP}.TAB1
WORKTABLES
${DX_WRK}.TAB1_WT1
,${DX_WRK}.TAB1_WT2
ERRORTABLES
${DX_UTL}.TAB1_ERR1
${DX_UTL}.TAB1_UV1
,${DX_UTL}.TAB1_ERR2
${DX_UTL}.TAB1_UV2
ERRLIMIT 1000
CHECKPOINT 100000
SESSIONS 4
;
/*
################################################## ##########################
# Layout of the File from where the data will be feed to the database
################################################## ##########################
*/
.LAYOUT LAYOUT_NAME INDICATORS;
.FIELD x * INTEGER;
.FIELD y * VARCHAR(20);
.FIELD z * BYTEINT;
.FIELD RECORD_STAT_IND * CHAR(1);
/*
################################################## ##########################
# Business Rule Defined File :
# Telenor DW -Design Document- V1.4 - 04-Aug-2003
################################################## ##########################
# Section X.X.X
# Description :
################################################## ##########################
*/
.DML LABEL NEW_RECORD;
INSERT INTO ${DX_TAB}.TAB1
(
x
,y
,z
)
VALUES
(
:x,
:y,
:z
);
/*
################################################## ##########################
# Business Rule Defined File :
# Telenor DW -Design Document- V1.4 - 04-Aug-2003
################################################## ##########################
# Section X.X.X
# Description :
################################################## ##########################
*/
.DML LABEL DUP_RECORD;
INSERT INTO ${DX_DUP}.TAB1
(
x
,y
,z
)
VALUES
(
:x,
:y,
:z
);
/*
################################################## ##########################
# Business Rule Defined File :
# Telenor DW -Design Document- V1.4 - 04-Aug-2003
################################################## ##########################
# Section X.X.X
# Description :
################################################## ##########################
*/
.DML LABEL UPD_RECORD;
UPDATE ${DX_TAB}.TAB1
SET
x = :x
,y = :y
WHERE
z = :z
;
/*
################################################## ##########################
# Business Rule Applied
# Telenor DW -Design Document- V1.4 - 04-Aug-2003
################################################## ##########################
# Section X.X.X
################################################## ##########################
# RECORD STATUS CODE
# Below is an example - modify according to your script
# I - Insert Record
# U - Record needs to be updated
# D - Records already exist in the target table
################################################## ##########################
# INSERTION , UPDATION & DUPLICATES AS DEFIN
