Comparison of The Teradata Loading Utilities

Comparison of the Teradata loading utilities
The article contains comparison and main features of the data loading tools provided by Teradata.
The tutorial illustrates main features of Teradata Multiload , FastLoad and TPump (Parallel Data
Pump) and provides sample real-life uses of those tools.
Scroll down for the sample scripts which illustrate different ways to load a sample fixed-length
extract into a Teradata database using FastLoad, MultiLoad and Parallel Data Pump (TPump).
Teradata Fast Load

 Main use: to load empty tables at high speed.
 The target tables must be empty in order to use FastLoad
 Supports inserts only - it is not possible to perform updates or deletes in FastLoad
 Although Fastload uses multiple sessions to load the data, only one target table can be
processed at a time
 Teradata Fastload does not support join indexes, foreign key references in target
tables and tables with secondary index defined. It is necessary to drop any of the
constraints listed before loading and recreate them afterwards.
 The maximum number of concurrent Teradata Fastload tasks can be adjusted by a
system administrator.
 Fastload runs in two operating modes: Interactive and Batch
 Duplicate rows will not be loaded
Teradata Multi Load

 Main use: Load, update and delete large tables in Teradata in a bulk mode
 Efficient in loading very large tables
 Multiple tables can be loaded at a time.
 Updates data in a database in a block mode (one physical write can update multiple
rows)
 Uses table-level locks
 Resource consumption: loading at the highest possible throughput
 Duplicate rows allowed
TERADATA PARALLEL DATA PUMP (TPUMP)

 Main use: to load or update a small amount of target table rows
 Sends data to a database as a statement which is much slower than using bulk mode
 TPump uses row-level hash locks
 Resource consumption: loading speed can be adjusted using a built-in resource
consumption management utility. The throughput can be turned down in peak periods.
 TPump does not support MULTI-SET tables.
Teradata FastLoad example

The following script attached below will load a sample fixed-length columns extract into a
Teradata database using FastLoad.
Use the following command to run load the ggclients.fastload file using Teradata FastLoad
script:
fastload < ggclients.fastload
Contents of a ggclients.fastload script:
SESSIONS 4;
ERRLIMIT 25;
logon tdpid/username,password;
create table gg_cli (

wh_cust_no integer not null,
cust_name varchar(200),
bal_amt decimal(15,3) format ‘ZZZ,ZZ9.999’
)
unique primary index( wh_cust_no ) ;
SET RECORD UNFORMATTED;
define
wh_cust_no(char(10)), delim1(char(1)),
cust_name(char(200)), delim2(char(1)),
bal_amt(char(18)), delim3(char(1))
newlinechar(char(1))
file=insert.input;
SHOW;
BEGIN LOADING gg_cli errorfiles error_1, error_2;
insert into gg_cli (

:wh_cust_no,
:cust_name,
:bal_amt
);
END LOADING;
logoff;
Teradata MultiLoad example

The following script attached below will load a sample fixed-length columns extract into a
Teradata database using MultiLoad.
Use the following command to run load the ggclients.mload file using Teradata FastLoad
script:
mload < ggclients.mload
Contents of a ggclients.mload mload script:
.logtable inslogtable;
.logon tdpid/username,password;
create table gg_cli (

wh_cust_no integer not null,
cust_name varchar(200),
bal_amt decimal(15,3) format ‘ZZZ,ZZ9.999’
)
unique primary index( wh_cust_no ) ;
.BEGIN IMPORT MLOAD tables gg_cli;
.layout ggclilayout;
.field wh_cust_no 1 char(10);
.field cust_name 12 char(200);

.field bal_amt 213 char(18);
.dml label insertclidml;
insert into gg_cli.*;
.import infile insert.input

format text
layout ggclilayout
apply insertclidml;
.END MLOAD;
.logoff;
Teradata TPump example

The sample script attached below loads a sample fixed-length columns extract into a
Teradata database using Parallel Data Pump - Teradata TPump.
Contents of a ggclients.tpump script:

.logtable tpumplogtable;
.logon tdpid/username,password;
.BEGIN LOAD SESSION 4;
.layout ggclilayout;
.field wh_cust_no 1 char(10);
.field cust_name 12 char(200);
.field bal_amt 213 char(18);
.dml label insertclidml;
insert into gg_cli.*;
.IMPORT INFILE insert.input

layout ggclilayout
apply insertclidml;
.END LOAD;
.logoff;

Comparison of The Teradata Loading Utilities

Uploaded by

Document Informationclick to expand document information

Copyright:

Available Formats

Comparison of The Teradata Loading Utilities

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Comparison of The Teradata Loading Utilities

Uploaded by

Copyright:

Available Formats

Comparison of the Teradata loading utilities

Teradata Fast Load

Teradata Multi Load

TERADATA PARALLEL DATA PUMP (TPUMP)

Teradata FastLoad example

Contents of a ggclients.fastload script:

create table gg_cli (

SET RECORD UNFORMATTED;

BEGIN LOADING gg_cli errorfiles error_1, error_2;

insert into gg_cli (

Teradata MultiLoad example

mload < ggclients.mload

Contents of a ggclients.mload mload script:

create table gg_cli (

.field wh_cust_no 1 char(10);

.field cust_name 12 char(200);

.import infile insert.input

Teradata TPump example

Contents of a ggclients.tpump script:

.BEGIN LOAD SESSION 4;

insert into gg_cli.*;

.IMPORT INFILE insert.input

You might also like