Teradata Utilities

Teradata Utilities Teradata offers utilities that support the management and maintenance of a data warehouse on a Teradata database.

Some Teradata utilities include:

•  FastLoad
•  FastExport
•  TPump
•  MultiLoad

FastLoad is a parallel-load utility that is designed to move large volumes of data from data sources on channel-attached and network-attached clients to empty tables in a Teradata database.

FastLoad typically provides better performance than a standard application program that loads data to an empty database because data allocation, data conversion, data movement, and data loading run automatically and in parallel.

Features:

•   Performs the initial table load.
•   Supports delimited or fixed-width flat file input.
•   Processes large data volumes. Constraints:
•   The target table must be empty before the load.
•   No read or write access is allowed on the target table while the load occurs.
•   Each job can load only one table.

Example:

FastLoad utility is used to load data into empty tables. Since it does not use transient journals, data can be loaded quickly. It doesn't load duplicate rows even if the target table is a MULTISET table.

Limitation

Target table should not have secondary index, join index and foreign key reference.

How FastLoad Works

FastLoad is executed in two phases.

Phase 1

·        The Parsing engines read the records from the input file and sends a block to each AMP.

·        Each AMP stores the blocks of records.

·        Then AMPs hash each record and redistribute them to the correct AMP.

·        At the end of Phase 1, each AMP has its rows but they are not in row hash sequence.

Phase 2

·        Phase 2 starts when FastLoad receives the END LOADING statement.

·        Each AMP sorts the records on row hash and writes them to the disk.

·        Locks on the target table is released and the error tables are dropped.

Example

Create a text file with the following records and name the file as employee.txt.

101,Mike,James,1980-01-05,2010-03-01,1  

102,Robert,Williams,1983-03-05,2010-09-01,1 

103,Peter,Paul,1983-04-01,2009-02-12,2 

104,Alex,Stuart,1984-11-06,2014-01-01,2 

105,Robert,James,1984-12-01,2015-03-09,3

Following is a sample FastLoad script to load the above file into Employee_Stg table.

LOGON 192.168.1.102/dbc,dbc;  

   DATABASE tduser;  

   BEGIN LOADING tduser.Employee_Stg  

      ERRORFILES Employee_ET, Employee_UV  

      CHECKPOINT 10;  

      SET RECORD VARTEXT ",";  

      DEFINE in_EmployeeNo (VARCHAR(10)), 

         in_FirstName (VARCHAR(30)), 

         in_LastName (VARCHAR(30)), 

         in_BirthDate (VARCHAR(10)), 

         in_JoinedDate (VARCHAR(10)), 

         in_DepartmentNo (VARCHAR(02)), 

         FILE = employee.txt;

      INSERT INTO Employee_Stg (

         EmployeeNo,

         FirstName,

         LastName,

         BirthDate,

         JoinedDate, 

         DepartmentNo

      ) 

      VALUES (  

         :in_EmployeeNo, 

         :in_FirstName, 

         :in_LastName, 

         :in_BirthDate (FORMAT 'YYYY-MM-DD'), 

         :in_JoinedDate (FORMAT 'YYYY-MM-DD'),

         :in_DepartmentNo

      ); 

   END LOADING;  

LOGOFF;

Executing a FastLoad Script

Once the input file employee.txt is created and the FastLoad script is named as EmployeeLoad.fl, you can run the FastLoad script using the following command in UNIX and Windows.

FastLoad < EmployeeLoad.fl;

Once the above command is executed, the FastLoad script will run and produce the log. In the log, you can see the number of records processed by FastLoad and status code.

**** 03:19:14 END LOADING COMPLETE 

   Total Records Read              =  5 

   Total Error Table 1             =  0  ---- Table has been dropped 

   Total Error Table 2             =  0  ---- Table has been dropped 

   Total Inserts Applied           =  5 

   Total Duplicate Rows            =  0 

   Start:   Fri Jan  8 03:19:13 2016 

   End  :   Fri Jan  8 03:19:14 2016 

**** 03:19:14 Application Phase statistics: 

              Elapsed time: 00:00:01 (in hh:mm:ss) 

0008  LOGOFF; 

**** 03:19:15 Logging off all sessions

FastLoad Terms

Following is the list of common terms used in FastLoad script.

·        LOGON − Logs into Teradata and initiates one or more sessions.

·        DATABASE − Sets the default database.

·        BEGIN LOADING − Identifies the table to be loaded.

·        ERRORFILES − Identifies the 2 error tables that needs to be created/updated.

·        CHECKPOINT − Defines when to take checkpoint.

·        SET RECORD − Specifies if the input file format is formatted, binary, text or unformatted.

·        DEFINE − Defines the input file layout.

·        FILE − Specifies the input file name and path.

·        INSERT − Inserts the records from the input file into the target table.

·        END LOADING − Initiates phase 2 of the FastLoad. Distributes the records into the target table.

·        LOGOFF − Ends all sessions and terminates FastLoad