Wednesday, September 1, 2010

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.

OVERVIEW

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. (In comparison, refer to Teradata MultiLoad)


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 FastLoaduses 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. 


Character Sets


Teradata FastLoad supports Latin, Chinese, Japanese, and Korean character sets for networkattached and channel-attached configurations.Teradata FastLoad also supports UTF16 (workstation only) and UTF8 character sets.



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

No comments:

Post a Comment