Introduction to teradata utilities : fastload

Introduction to teradata utilities : fastload

Teradata FastLoad, also called "FastLoad"   or "FL," is a multi-sessioned parallel load utility for initial table load in bulk mode on a Teradata Database.

Teradata FastLoad processes a series of FastLoad commands and Teradata SQL statements written in a FastLoad job script or interactively entered. The FastLoad commands provide the session control and data handling specifications for the data load operations, and the Teradata SQL statements perform the actual data load functions on the Teradata RDBMS tables and views. However, this command-driven utility feeds only one table per job; thus, to load more than one table, multiple FastLoad jobs must be submitted — one for each table.

It loads data from:

·         Disk or tape files on a channel-attached client system;

·         Input files on a network-attached workstation;

·         Special input module (INMOD) routines that select, validate, and preprocess input data;

·         Any other device providing properly formatted source data.

Operating Modes
Teradata FastLoad runs in the following operating modes:

Interactive mode;  Batch mode.

In interactive mode, Teradata FastLoad uses terminal screen and keyboard as the standard output and input streams.

In batch mode, FastLoad uses > and < to redirect the standard output / input streams.

·         To invoke Teradata FastLoad in interactive mode, enter the following command (for Microsoft Windows):
c:\ncr\bin\fastload

·         To invoke FastLoad in batch mode, use the command below (for Microsoft Windows):
c:\ncr\bin\fastload [options] < infile > outfile
Here, the infile is a Teradata FastLoad job script file which includes all the required FastLoad commands and Teradata SQL statements, whereas the outfile is the FastLoad output stream file.

Data Transfer Capabilities
Teradata FastLoad utilizes TCP/IP Protocol for all data transfer operations on network-attached workstations.
FastLoad transfers data on channel-attached systems as either:

a multi-volume data set or file;

a number of single-volume data sets or files in separate Teradata FastLoad jobs.

Serial FastLoad operations can be restarted by loading the next tape in a series, instead of beginning with the first tape in a set. In either case, FastLoad:

·         transfer data in multiple-session mode (one session per AMP);

·         transfers multiple rows of data within a single message.

Also, in either case, until the Teradata FastLoad job is completed and the data has been loaded into the Teradata FastLoad table:

there is no journaling or fallback data;

the secondary indexes cannot be defined.

 Data Conversion Capabilities
FastLoad data conversion types are:

Numeric-to-numeric (for example integer-to-decimal);

Character-to-numeric;

Character-to-date;

Date-to-character.

Note: Redundant conversions, like integer-to-integer, are legal and necessary to support the zoned decimal format.

Input Data Formats Supported
For network-attached configurations, Teradata FastLoad supports the following data formats:

·         Formatted data;

·         Unformatted data;

·         Binary data;

·         Text data;

·         Variable-length text.

For channel-attached configurations, Teradata FastLoad supports:
Data sets and tape files with the following record format (RECFM) attributes

F (fixed);

FB (fixed block);

V (variable);

VB (variable block);

VBS (variable block, spanned).

 FASTLOAD COMMANDS
The FastLoad commands perform two types of activities:

Session control — Session control commands begin and end Teradata FastLoad sessions and provide online information about a particular Teradata FastLoad operation;

Data handling — Data handling commands establish and define a Teradata FastLoad operation.

1. Teradata FastLoad Commands for Session Control:

COMMAND NAME	FUNCTION
HELP	Lists Teradata FastLoad commands and options
HELP TABLE	Creates a list of field names that you can use with the INSERT statement
LOGOFF / QUIT	Ends Teradata FastLoad sessions and terminates Teradata FastLoad
LOGON	Begins one or more Teradata FastLoad sessions
NOTIFY	Specifies a user exit or action to be performed when certain significant events occur
OS	Enters client operating system commands
SESSIONS	Specifies the number of Teradata FastLoad sessions logged on with a LOGON command and, optionally, the minimum number of sessions required to run the job
SHOW	Shows the current field/file definitions established by DEFINE commands
SHOW SESSIONS	Shows the current level of all Teradata FastLoad software modules
SLEEP	Specifies the number of minutes that Teradata FastLoad pauses before retrying a logon operation
TENACITY	Specifies the number of hours that Teradata FastLoad continues trying to log on when the maximum number of load jobs is already running on the Teradata Database

2. Teradata FastLoad Commands for Data Handling:

COMMAND NAME	FUNCTION
AXSMOD	Specifies the name and initialization string for a shared object file that loads data from a file on network-attached client systems
BEGIN LOADING	Identifies the tables used in the Teradata FastLoad operation and, optionally,  specifies when checkpoints are taken or if the user supplies indicator data
CLEAR / QUIT	Cancels the current DEFINE command specifications
DATEFORM	Specifies the form of the DATE data type specifications for the Teradata FastLoad job
DEFINE	Describes each field of an input data source record and specifies the name of the input data source or INMOD routine
END LOADING	Informs the Teradata Database that all input data has been sent
ERRLIMIT	Limits the number of errors detected during the loading phase of a Teradata FastLoad job Processing stops when the limit is reached.
RECORD	Specifies the number of a record in an input data source at which Teradata FastLoad begins to read data and/or the number of the last record to be read
RUN	Invokes the specified external source as the current source of commands and statements
SET RECORD	Specifies that the input data records are either: Formatted Unformatted Binary Text Variable-length text Note: The SET RECORD command applies only to network-attached systems.
SET SESSION CHARSET	Specifies which character set is in effect during a specific Teradata FastLoad invocation Note: The SET SESSION CHARSET command applies only to networkattached systems.

Teradata SQL Statements in FastLoad

A subset of Teradata SQL statements can be used in Teradata FastLoad to define and manipulate the data stored in the Teradata Database. The Teradata SQL statements supported by Teradata FastLoad are listed below:

TERADATA SQL STATEMENT	FUNCTION
CREATE TABLE	Defines the columns, index, and other qualities of a table
DATABASE	Changes the default database
DELETE	Deletes rows from a table
DROP TABLE	Removes a table and all of its rows from a database
INSERT	Inserts rows into a table
SET QUERY_BANDING...FOR SESSION	Sets query banding at the session level

Credits:

Pankaj Chahar