15.00 - Writing the Teradata FastLoad Job Script - FastLoad

Teradata FastLoad Reference

prodname
FastLoad
vrm_release
15.00
category
Programming Reference
featnum
B035-2411-034K

Writing the Teradata FastLoad Job Script

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 Chapter 3: “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
  • Name
  • 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. (The insert.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.