MultiLoad

Teradata MultiLoad is a command-driven utility for fast, high-volume maintenance on multiple tables and views in a Teradata database.

A single Teradata MultiLoad job can perform different import and delete tasks on database tables and views:

• Each Teradata MultiLoad import task can do multiple data insert, update, and delete functions on up to five different tables or views.

• Each Teradata MultiLoad delete task can remove large numbers of rows from a single table.

The Teradata MultiLoad utility processes a series of commands and Teradata SQL statements which are usually entered as a batch mode job script. The Teradata MultiLoad commands perform session control and data handling of the data transfers. The Teradata SQL statements do the actual maintenance functions on the database tables and views.

MultiLoad Phases

MultiLoad Commands

Example MultiLoad Script

The following code example shows CTP integration in a MultiLoad script:

.LOGTABLE thales.EMP_ENC_log;
.LOGON thales,Ssl12345#;
DATABASE thales;
DROP TABLE EMP_ENC;
DROP TABLE UV_EMP_ENC;
DROP TABLE ET_EMP_ENC;
DROP TABLE WT_EMP_ENC;
CREATE MULTISET TABLE EMP_ENC(
ID INTEGER,
NAME VARCHAR(100),
DEPT VARCHAR(100),
NAME_1 VARBYTE(100)
) UNIQUE PRIMARY INDEX(ID);
.BEGIN IMPORT MLOAD
TABLES EMP_ENC
WORKTABLES WT_EMP_ENC
ERRORTABLES ET_EMP_ENC
UV_EMP_ENC;
.LAYOUT INPUTLAYOUT;
.FIELD in_ID * VARCHAR(50);
.FIELD in_NAME * VARCHAR(100);
.FIELD in_DEPT * VARCHAR(100);
.DML LABEL INSERTS IGNORE DUPLICATE INSERT ROWS;
INSERT INTO EMP_ENC(ID , NAME , DEPT , NAME_1) VALUES (:in_ID,:in_NAME,:in_DEPT,thales.encrypt_
cbc(:in_NAME,'encrypt_ccnum'));
.IMPORT INFILE emp_data.txt
FORMAT VARTEXT ','
LAYOUT INPUTLAYOUT
APPLY INSERTS;
.END MLOAD;
.LOGOFF;

The following shows the contents of the input file emp_data.txt used in the example above:

1000,Emp_1,development
1001,EMP_2,development
1002,EMP_3,automation
1003,EMP_4,automation

Running a MultiLoad Job

Use the following command to invoke Teradata MultiLoad, and run the job script, in this case named insert.mload:

mload < insert.mload

Using UDFs with MLOAD INSERT

encrypt_cbc()

Consider the following encrypt_cbc() UDF declaration:

function encrypt_cbc (inputString varchar(8192) CHARACTER SET UNICODE,\ inputKeyname varchar(256)) returns varbyte(16400);

In this declaration, encrypt_cbc() takes an input parameter of type VARCHAR having max 8192 characters, and returns encrypted data of type VARBYTE.

In the preceding example Example MultiLoad Script, note that in_NAME is set as an input field of type VARCHAR, and is passed to encrypt_cbc(), which satisfies the requirement that inputs match.

The result of the above encrypt_cbc() UDF is stored into the NAME_1 column of type VARBYTE, which also satisfies the requirement that outputs match.

You must pass the VARCHAR string as an input parameter, and store the result of the encrypt_cbc() UDF to a column of type VARBYTE. If these requirements are not satisfied, you can get the following errors:

• UTY0805 RDBMS failure, 9881: Function 'encrypt_cbc' called with an invalid number of type of parameters.

  This indicates that the input field is not of type VARCHAR.

• UTY0805 RDBMS failure, 3532: conversion between BYTE data and other types is illegal.

  This indicates you are storing output to a column that is not of type VARBYTE.

encrypt_fpe() and encrypt_ff1()

Consider the following declarations of encrypt_fpe() and encrypt_ff1() UDFs:

function encrypt_fpe (inputString varchar(8192) CHARACTER SET UNICODE,\ inputKeyname varchar(256)) returns varchar(8192) CHARACTER SET UNICODE

function encrypt_ff1 (inputString varchar(8192) CHARACTER SET UNICODE,\ inputKeyname varchar(256)) returns varchar(8192) CHARACTER SET UNICODE

The inputs and output are all type VARCHAR.

When using UDFs in an INSERT command of a MultiLoad script, you must provide an input parameter of the same type as specified in the function declaration. You must also store output parameters to a column of that specific type.

Verifying Import Tasks

Use the following BTEQ commands to verify the Teradata MultiLoad import task by selecting newly imported data from the above table:

bteq
.LOGON userID,password;
.WIDTH 200
SELECT * FROM EMP_ENC;
.QUIT

Decrypting and Checking Data

Use the following BTEQ commands to decrypt and check the data of the original and decrypted columns:

bteq
.LOGON userID,password;
SELECT NAME_1, thales.decrypt_cbc(NAME_1,'encrypt_ccnum')FROM EMP_ENC;
.quit