Writing the Teradata FastLoad Job Script - FastLoad

Teradata® FastLoad Reference

Product
FastLoad
Release Number
16.20
Published
October 2018
Language
English (United States)
Last Update
2019-02-14
dita:mapPath
ybx1527114222321.ditamap
dita:ditavalPath
ft:empty
dita:id
B035-2411
lifecycle
previous
Product Category
Teradata Tools and Utilities

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

sessions 2;
errlimit 25;
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 ) ;
set record unformatted;
define
       delim0(char(1)),
       EmpNo(char(9)), delim1(char(1)),
       Name(char(12)), delim2(char(1)),
       DeptNo(char(3)), delim3(char(1)),
       PhoneNo(char(4)), delim4(char(1)),
       JobTitle(char(12)), delim5(char(1)),
       Salary(char(9)), delim6(char(1)),
       YrsExp(char(2)), delim7(char(1)),
       DOB(char(11)), delim8(char(1)),
       Sex(char(1)), delim9(char(1)),
       Race(char(1)), delim10(char(1)),
       MStat(char(1)), delim11(char(1)),
       EdLev(char(2)), delim12(char(1)),
       HCap(char(2)), delim13(char(1)),
       newlinechar(char(1))
file=insert.input;
show;
begin loading employee errorfiles error_1, error_2;
insert into employee (
       :EmpNo,
       :Name,
       :DeptNo,
       :PhoneNo,
       :JobTitle,
       :Salary,
       :YrsExp,
       :DOB,
       :Sex,
       :Race,
       :MStat,
       :EdLev,
       :HCap
);
end loading;
logoff;

Comments

  1. For syntax and descriptions of the following commands, see Teradata FastLoad Commands:
    • SESSIONS
    • ERRLIMIT
    • LOGON
    • SET RECORD
    • DEFINE
    • SHOW
    • BEGIN LOADING
    • INSERT
    • END LOADING
    • LOGOFF
  2. The CREATE TABLE statement creates a new table on the Teradata Database:
    • Named employee
    • With 13 columns:

      – EmpNo

      – DeptNo

      – PhoneNo

      – JobTitle

      – Salary

      – YrsExp

      – DOB

      – Sex

      – Race

      – MStat

      – EdLev

      – HCap

    • Indexed by EmpNo (UNIQUE PRIMARY)

      For syntax and a complete description of the Teradata SQL CREATE TABLE statement, see SQL Data Definition Language (B035-1144) and SQL Data Manipulation Language (B035-1146).

  3. The DEFINE command specifies each field of the data records that will be sent to the Teradata Database. (Theinsert.input data file was created in the Creating the Source Data File subsection.)

    Each field definition provides the name and data type description for each field in the input data records. In making the field declarations, note that the one character delimiter fields are optional. They do not need to be used in the example.

  4. The BEGIN LOADING command specifies the error files and starts the Teradata FastLoad job, using the insert.input file and the Employee table.