15.00 - Teradata FastLoad Job Script Example - FastLoad

Teradata FastLoad Reference

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

Teradata FastLoad Job Script Example

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;

Table 20 describes the commands used in this sample script.

 

Table 20: FastLoad Entering Commands  

Command

Description

SESSIONS

Directs Teradata FastLoad to log on to the Teradata 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 Teradata 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 Teradata 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 Teradata 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 Teradata Database and inserts rows into the Teradata FastLoad table. Teradata FastLoad processes the data records by:

1 Packaging them into large data blocks

2 Transferring them to the Teradata Database, where they are distributed to the AMPs

END LOADING

Directs the Teradata 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 Teradata 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.