Teradata FastLoad Job Script Example | Teradata FastLoad - 17.10 - Teradata FastLoad Job Script Example - FastLoad

Teradata® FastLoad Reference

Product
FastLoad
Release Number
17.10
Published
June 2021
Last Update
2021-07-01
Content Type
Programming Reference
Publication ID
B035-2411-061K
Language
English (United States)

The following example Teradata FastLoad job script provides an overview of a typical Teradata FastLoad operation:

SESSIONS 4;
RECORD 100 THRU 100000;
ERRLIMIT 25;
LOGON tdpid/userid,password
DROP TABLE FastTable;
DROP TABLE Error1;
DROP TABLE Error2;
CREATE TABLE FastTable, NO FALLBACK
   ( ID INTEGER, UFACTOR INTEGER, MISC CHAR(42))
   PRIMARY INDEX(ID);
DEFINE ID (INTEGER), UFACTOR (INTEGER), MISC (CHAR(42))
   FILE=FileName;
SHOW;
BEGIN LOADING FastTable ERRORFILES Error1,Error2
   CHECKPOINT 10000;
INSERT INTO FastTable (ID, UFACTOR, MISC) VALUES
   (:ID, :MISC);
END LOADING;
LOGOFF;

The following table describes the commands used in this sample script.

FastLoad Entering Commands  
Command Description
SESSIONS Directs Teradata FastLoad to log on to the database for up to four sessions.
RECORD Directs Teradata FastLoad to begin reading data at record 100 in the input data source and stop reading records at record 100,000.
ERRLIMIT Directs Teradata FastLoad to stop processing when 25 errors occur.
LOGON Logs the specified user on to the database for up to four sessions, as specified in the SESSIONS command.
DROP TABLE Makes sure that the Teradata FastLoad table and the two error tables do not already exist on the database.

Teradata FastLoad will not run if the two error tables exist from a prior job. And, though the Teradata FastLoad table can be an existing table, it must be empty. Thus, instead of deleting an existing Teradata FastLoad table, use the DELETE statement to remove all the rows.

CREATE TABLE Creates the Teradata FastLoad table on the database.
DEFINE Defines the data fields in each record and identifies the input data source. This command corresponds to a Teradata SQL USING clause.
SHOW Displays the active definitions for the input data source and the field names that were specified in the previous DEFINE command. This command allows the exact definitions in effect during the Teradata FastLoad operation to be verified.
BEGIN LOADING Begins the loading phase of the Teradata FastLoad job. This command specifies the name of the Teradata FastLoad table and the two error tables, and, in this example, specifies that a checkpoint be taken every 10,000 records.
INSERT Sends input data records to the database and inserts rows into the Teradata FastLoad table. Teradata FastLoad processes the data records by performing the following tasks:
  1. Packaging them into large data blocks
  2. Transferring them to the database, where they are distributed to the AMPs
END LOADING Directs the database to redistribute (hash) the rows of data on the AMPs and store them in the Teradata FastLoad table.
Upon successful completion, Teradata FastLoad returns a status message that displays the total number of:
  • Records read
  • Records skipped
  • Records sent to the database
  • Records inserted as rows in the Teradata FastLoad table
  • Error records in the two error tables
  • Duplicate rows
LOGOFF Logs off all Teradata FastLoad sessions, terminates Teradata FastLoad, and presents the system command prompt.