Writing the Teradata MultiLoad Job Script

Teradata MultiLoad Reference

brand
Teradata Tools and Utilities
prodname
MultiLoad
vrm_release
16.10
category
Programming Reference
featnum
B035-2409-057K

Create and save a Teradata MultiLoad job script file named insert.mload that loads the six-record insert.data file into the Employee table:

.logtable inslogtable;
.logon tdpid/username,password;

CREATE TABLE employee (
    EmpNo SMALLINT FORMAT ‘9(5)’ BETWEEN 10001 AND 32001 NOT NULL,
    Name VARCHAR(12),
    DeptNo SMALLINT FORMAT ‘999’ BETWEEN 100 AND 900 ,
    PhoneNo SMALLINT FORMAT ‘9999’ BETWEEN 1000 AND 9999,
    JobTitle VARCHAR(12),
    Salary DECIMAL(8,2) FORMAT ‘ZZZ,ZZ9.99’ BETWEEN 1.00 AND 999000.00 ,
    YrsExp BYTEINT FORMAT ‘Z9’ BETWEEN -99 AND 99 ,
    DOB DATE FORMAT ‘MMMbDDbYYYY’,
    Sex CHAR(1) UPPERCASE,
    Race CHAR(1) UPPERCASE,
    MStat CHAR(1) UPPERCASE,
    EdLev BYTEINT FORMAT ‘Z9’ BETWEEN 0 AND 22,
    HCap BYTEINT FORMAT ‘Z9’ BETWEEN -99 AND 99 )
    UNIQUE PRIMARY INDEX( EmpNo )
    INDEX( Name );

.begin import mload tables employee;
.layout inslayout;
    .field EmpNo 2 char(9);
    .field Name 12 char(12);
    .field DeptNo 25 char(3);
    .field PhoneNo 29 char(4);
    .field JobTitle 34 char(12);
    .field Salary 47 char(9);
    .field YrsExp 57 char(2);
    .field DOB 60 char(11);
    .field Sex 72 char(1);
    .field Race 74 char(1);
    .field MStat 76 char(1);
    .field EdLev 78 char(2);
    .field HCap 81 char(2);
.dml label insdml;
insert into employee.*;

.import infile insert.input
     format text
     layout inslayout
     apply insdml;
.end mload;
.logoff;

Comments

  1. For syntax and descriptions of the following Teradata MultiLoad utility commands used in the preceding example, see Teradata MultiLoad Commands.
    • BEGIN MLOAD
    • BEGIN MLOAD
    • DML LABEL
    • END MLOAD
    • FIELD
    • IMPORT
    • LAYOUT
    • LOGOFF
    • LOGON
    • LOGTABLE
  2. The CREATE TABLE statement creates a new table on Teradata Database:
    • Named employee
    • Containing the following columns:

      - DeptNo

      - DOB

      - EdLev

      - EmpNo

      - HCap

      - JobTitle

      - MStat

      - Name

      - PhoneNo

      - Race

      - Salary

      - Sex

      - YrsExp

    • Indexed by:

      - EmpNo (UNIQUE PRIMARY)

      - Name

      For a description of the CREATE TABLE statement, see SQL Data Definition Language.

  3. The LAYOUT command and the series of FIELD commands specify each field of the data records that are sent to Teradata Database. (The insert.input data file created in Creating the Source Data File.)

    The specified layout name, inslayout, is referenced in the subsequent Teradata MultiLoad IMPORT command.

    Each FIELD command specifies the name, starting position, and data type description for each field in the input data records. In making the field declarations, note that the 1-character and multi-character delimiter fields are optional. They are not required in the example.

  4. The DML LABEL command introduces the INSERT statement.
  5. The IMPORT command starts the import task, using the insert.input file and the insdml insert statement.