1
Teradata Client Tools
Client Tools
2
Application Utilities
Teradata Database provides several
application utilities for data manipulation
Each utility exploits the capabilities of
Teradata parallel architecture
Teradata application utilities are
supported on several hardware platforms
including a wide range of channelattached mainframes and LAN-attached
clients
Regardless of host platform, all access
between the host and the Teradata
Database relies on Call Level Interface
Client Tools
3
Application Utilities .
The Teradata application utilities run on
the host computer, whether it is
mainframe or a workstation
BTEQ is a transparent interface to CLI, to
transmit textual SQL to Teradata server,
and deliver response to the user
FastLoad, MultiLoad and TPump performs
high volume data transfer from the host to
Teradata
Fast Export performs high volume SELECTs
to export data from Teradata server to the
host
Client Tools
4
Application Utilities .
Operating System
BTEQ
Fast
Loa
d
Support Environment
Mult
i
Loa
CLI
d
Host
Server
Fast
Expor
t
TPum
p
Client Tools
5
Request and Response Flow
Applicati
on
Request
CLI
MTDP
MOSI
Response
Serve
r
Client Tools
6
Data Flow
Host
BTEQ
FastLoad
MultiLoad
TPump Fast Export
Gateway
PE
AMP
AMP
PE
AMP
Data
Disk
AMP
Data
Disk
Disk
Disk
Client Tools
7
Application Development
More
INMODs
FlexibilityApplication
Less
Utilities
Easy
Ease of Use
Difficult
election of the right vehicle can be crucial and
epends on how difficult it is to implement and
maintain
Use of application utilities
Offer less complexity
Takes full advantage of parallel processing
Client Tools
8
Using An INMOD Routine
The application utilities allow input data to be
read or pre-processed by a user-written
INMOD routine.
An INMOD routine can perform various
functions:
Generate records to be passed to the
utility
Validate a data record before passing it to
the utility
Read data directly from one or more
database systems
Select specific records for input to the
Teradata Database
Client Tools
9
Using An INMOD Routine
IMS
ORACLE
Userwritten INMOD
FastLoad, MultiLoad, TPump,
Server
10
Client Tools
BTEQ
FastLoad
Fast Export
MultiLoad
TPump
Client Tools
11
BTEQ
Batch mode utility for submitting SQL
requests to
the Teradata Database
Runs on every supported platform
laptop to
mainframe
Flexible and easy to use report writer
Exports data to a client system from the
Teradata
Database
Reads input data and imports it to the
Teradata
Database
Limited ability to branch forward to a
LABEL
Client Tools
12
BTEQ
Request Flow
BTEQ script
.logon
select
.logoff
CLI
BTEQ
MTDP
Network attached Client
MOSI
Server
PE
AMP
AMP
Data
Data
AMP
AMP
Data
Data
Client Tools
13
Response Flow
BTEQ script
.logon
select
.logoff
CLI
BTEQ
MTDP
Network attached Client
PE
BTEQ
Server
Generated answer set
AMP
AMP
Data
Data
MOSI
AMP
AMP
Data
Data
Client Tools
14
Conditional Logic
BTEQ
BTEQ has the ability to branch forward in a
script based on a test of either an error
code or an activity count
This is not a true loop function yet it can
be used to avoid unnecessary, timeconsuming steps
Client Tools
15
Conditional Logic - Example
BTEQ
DELETE Customer_Info ALL;
.IF ERRORCODE = 0 THEN .GOTO TableOK
CREATE TABLE Customer_Info ( Acc_no
INTEGER,
Cust_Name
VARCHAR(20),
Balance
DECIMAL(9,2));
.LABEL TableOK
INSERT INTO Customer_Info (
SELECT A.Acc_no, Cust_Name, Balance
FROM Accounts A, Acc_Cust B, Customer C
WHERE Balance > 100000
AND A.Acc_no = B.Acc_no);
.IF ACTIVITYCOUNT > 0 THEN .GOTO Continue
.QUIT
Client Tools
16
Command Set
BTEQ commands can be classified as
follows:
Session control commands
File control commands
Sequence control commands
Format control commands
BTEQ
Client Tools
17
Session Control Commands
Controls session characteristics.
Commands:
LOGON
SESSIONS
LOGOFF
QUIT,EXIT
SHOW CONTROLS
SESSION TRANSACTION
BTEQ
Client Tools
18
File Control Commands
Specify the format of incoming/outgoing
information and identify the source and
destination of input/output streams.
Commands:
OS
RUN
IMPORT
REPEAT
QUIET
EXPORT
INDICDATA/RECORDMODE
BTEQ
Client Tools
19
Sequence Control Commands
BTEQ
Controls the sequence in which other BTEQ
commands and SQL statements will be
executed within the scripts and macros.
Commands:
IF THEN
ELSE
ENDIF
GOTO
Client Tools
40
Format Control Commands
BTEQ
To specify the way in which BTEQ presents
information on the screen/printer.
Commands:
FOOTING
HEADING
RETLIMIT
FOLDLINE
SUPPRESS
QUIET
Client Tools
41
EXPORT
BTEQ
BTEQ by default delivers a response to all
SQL queries that includes a helpful message
along with helpful diagnostic information
about the time taken to perform the query.
If all of this information is captured in a
single output file, this mixed output typically
renders the data unsuitable for some
purposes.
The .EXPORT feature provides the ability to
separate the report or output data from the
accounting information.
Client Tools
42
BTEQ
EXPORT
BTEQ Script
.logon
mac/user,passwd
.export data
file=x.dat
sel * from table;
.export reset
.quit;
BTEQ
Logon complete
1200 rows returned
Time 15 seconds
Default Output
12348009
23498763
8568733
933435490
x.dat
Client Tools
43
.EXPORT
.EXPORT
.EXPORT
.EXPORT
EXPORT Types
BTEQ
REPORT
DATA
INDICDATA
DIF
Output converted to DIF used to transport
data to
various PC programs.
Example: dBase, Lotus 12-3
.EXPORT RESET
Reverses the effect of a previous .EXPORT
Client Tools
44
BTEQ
Data Modes
Field mode is set by .EXPORT REPORT
.EXPORT REPORT
Sends results to a host file in field mode.
Data set
contains column headings and formatted
data.
column A
1
4
7
column B
2
5
8
column C
3
6
9
Transfers data one column at a time with
numeric data
converted to
character.
Client Tools
45
Data Modes .
Record mode is set by .EXPORT DATA.
.EXPORT DATA
Sends results to a host file in record
mode.
field1 field2 field3
field4
Transfers data one row at a time in a host
format. Nulls are represented as zeros or
spaces.
BTEQ
Client Tools
46
Data Modes .
BTEQ
Record mode is set by .EXPORT
INDICDATA.
.EXPORT INDICDATA
Sends query results that contain indicator
variables to a host file. Allows host programs
field1
field2 field3 field4
to Ind
deal with
NULLs.
Var
Transfers data one row at a time in a host
format, sending an indicator variable for
nulls. Nulls are represented as zeros or
spaces.
Client Tools
47
BTEQ
Indicator Variables
Indicator variables allow utilities to process
records that contain NULL indicators.
.EXPORT INDICDATA
.[SET] INDICDATA [ON]
INDICATORS ON
INDICATORS
Export
BTEQ
BTEQ
FastLoad
MultiLoad, Fast
TPump
NULL Columns
F F
010010 00000000
1 2
00
F
3
F
4
F
5
F
6
F
7
..
.
F1
2
Client Tools
48
Data Handling With IMPORT
BTEQ
Useful to IMPORT data from the host to
Teradata as a series of INSERTs, UPDATEs,
DELETEs and macro transactions
BTEQ supports IMPORT in DATA,
INDICDATA and REPORT modes only
.IMPORT loads data from the host to the
Teradata database with a USING clause
Client Tools
49
Data Load From Host
BTEQ
.LOGON TDPid/UserName, Password
.IMPORT DATA FILE=datain.dat;
.QUIET ON
.REPEAT *
USING CustNo( INTEGER ),
Filler
( CHAR(30)),
CustName( CHAR(20))
INSERT INTO Customer
( Customer_Number,
Name )
VALUES ( :CustNo, :CustName );
.QUIT
.REPEAT * Causes BTEQ to read records until
EOF.
USING
Defines the input data from the
Client Tools
50
Application Utility Checklist
BTE FastLoa Fast
Export
DDL Functions QAll d
DML Functions All
Yes
Multiple DML
Multiple tables Yes
Yes
Multiple
SQ
sessions
Protocol used
L
Yes
Conditional
Exp.
Arithmetic
Yes
Calc
Data
Yes
conversion
Error files
No
Error limits
No
User routines
No
BTEQ
MultiLoa TPum
d
p
Client Tools
51
FastLoad
Fast batch mode utility for loading new
tables onto
the Teradata Database
Can reload previously emptied tables
Full restart capability
Error limits and error tables, accessible
using SQL
Restartable INMOD routine capability
Ability to load data in several stages
Client Tools
52
Purpose/Concepts
FastLoad
Purpose:
Load large amounts of data into an
empty table at
high speed.
Concepts:
Load into an empty table with no
secondary indexes.
Creates two error tables.
Checkpoints can be taken for restarts.
Client Tools
53
Restrictions
FastLoad
If an AMP goes down, FastLoad cannot be
restarted until it is back online
The Teradata Database will accommodate
up to 15 FL/ML/FE runs at one time
Tables defined with referential integrity or
secondary indexes cannot be loaded with
FastLoad
Duplicate rows cannot be loaded into
multi-set table with FastLoad
Client Tools
54
FastLoad Phases Phase 1
FastLoad
Phase 1
FastLoad uses one SQL session to define
AMP steps
The PE sends a block to each AMP
AMPs hash each record and redistribute
them to the AMP responsible for the hash
value
Records are written to the target table in
unsorted blocks
Client Tools
55
HostSQL & Data
FastLoad
Phase 1
PE
BYNET
AMP
Disk
AMP
Disk
Client tools
Tools
56
FastLoad
Phase 1
PE
Host
Data
BYNET
AMP
Disk
AMP
Disk
Client Tools
57
FastLoad Phases Phase 2
FastLoad
Phase 2
When loading completes, each AMP
sorts the target table, puts the rows
into blocks, and writes the blocks to
the disk
Fall back rows are then generated if
required
Client Tools
FastLoad
Phase 2
58
PE
BYNET
AMP
AMP
Data
Disk
Client Tools
59
FastLoad Script
FastLoad
fastload < floadjob >
floadjob.out
.logon Tdpid/User,Passwd
.begin loading Customer
errorfiles custerr1, custerr2;
define
CustNum ( Integer ),
Name
( Char(20) )
file = CustData.dat;
insert into Customer values
(:CustNum, :Name);
.end Loading;
.logoff;
Name of empty
table
Starts Phase 1
Defines input
record
SQL Insert
statement
Start phase 2;
if omitted
utility will
pause
Client Tools
60
datafil
e1
Paused FastLoad Job
SQL &
Data
PE
BYNET
AMP
Disk
AMP
Disk
FastLoad
Client Tools
61
Paused FastLoad Job .
PE
datafil
e1 Data
BYNET
AMP
Disk
AMP
Disk
FastLoad
Client Tools
62
Paused FastLoad Job .
PE
datafile
Data
n
BYNET
AMP
Disk
AMP
Disk
FastLoad
Client Tools
63
Paused FastLoad Job .
PE
BYNET
AMP
Disk
AMP
Disk
FastLoad
Client Tools
64
Error Tables
FastLoad
Error Table 1
Contains one row for each row which failed
to be loaded due to constraint violations or
translation errors.
Column_Na
me
Error_Code
Error Field
name
Data Parcel
Content
The Error code in
DBC.ErrorMsgs
The column that cause the
error
The data record sent by the
host
Error Table 2
Captures rows that contain duplicate values
for UPIs
Client Tools
65
Data
INMOD
INMO
D
FastLoa
d
FastLoad
Teradat
a
Server
To acquire data from a non-standard data
source, make unusual conversion of data,
FastLoad can use an INMOD or exit routine.
Communication needs to be established
between the INMOD and FastLoad. This is
done via return codes, and the INMOD will
pass data records to FastLoad.
Client Tools
66
FastLoad
INMOD/FastLoad Communication
INMOD to FastLoad return codes
0
Non
0
Indicates that INMOD is returning a
record
INMOD indicates end-of-file
condition
FastLoad to INMOD return codes
0
1
Calling for the first time. INMOD
should open files to read data.
FastLoad expects a record.
indicates Next Record Transfer
Client Tools
67
Additional Return Codes
FastLoad
FastLoad to INMOD
FastLoad has been restarted. INMOD
3
2
4
5
should position itself to the last
checkpoint. FastLoad is not
expecting a record and will not send
a zero return
code.
Indicates
a checkpoint
has been
written and INMOD should remember
it. No record expected.
Indicates a Teradata failure, INMOD
should position itself to the last
checkpoint. No record expected.
FastLoad instructs INMOD to clean
up.
Client Tools
68
Other FastLoad Commands
AXSMOD
init-string (optional )
SESSIONS
max [min]
ERRLIMIT
max rejected records
TENACITY
hours
SLEEP
DATEFORM
minutes
integerdate/ansidate
FastLoad
Client Tools
69
Application Utility Checklist
BTE
DDL Functions QAll
DML Functions All
Multiple DML
Yes
Multiple tables Yes
Multiple
Yes
sessions
Protocol used
SQ
L
Conditional
Yes
Exp.
Arithmetic
Yes
Calc
Data
Yes
conversion
Error files
No
Error limits
No
User routines
No
FastLoa Fast
dLimite Export
d INS
No
No
Yes
FastLoa
d No
No
Limite
d Yes
Yes
Yes
FastLoad
MultiLoa TPum
d
p
Client Tools
70
Support Environment
Launch vehicle for utilities such as
MultiLoad, Fast Export and TPump
Provides a fully nested .RUN file facility
Interprets utility commands and provides
error reporting
Supports a wide range of DDL and DML
commands
Allows for conditional processing of
commands
Supports system and user defined
variables
Provides recovery management from a
Teradata or host failure
Client Tools
71
Invoking Utilities
MultiLoad is invoked with:
.BEGIN IMPORT MLOAD
or
.BEGIN DELETE MLOAD
Fast Export is invoked with:
.BEGIN EXPORT
TPump is invoked with:
.BEGIN LOAD
Support
Environm
ent
Client Tools
72
Invoking Utilities .
A sample Fast Export script
.logtable cust_table;
.logon
mac/user,passwd;
.begin export;
.export outfile
Dataout;
select * from
Transaction;
.end export;
Support
Environme
nt
Command
s
Support
Environm
ent
Client Tools
73
Commands
Support
Environm
ent
.LOGTABLE Acquires or creates the restart log
table
.LOGON
Teradata
Connects multiple session to
.LOGOFF
Terminates the utility operation
.RUN
Specifies an external script file
.IF/.ELSE
Identifies statements to be
executed if
certain conditions
are true or otherwise
.ENDIF
condition
Resumes the program after a .IF
Client Tools
74
System Variables
.SET var [TO]
expression;
Variable
Example
Support
Environm
ent
Permits a variable to
be set or reset to an
expression or a preexisting variable
Description
Format
&SYSDATE System Date
YY/MM/DD
98/04/01
&SYSTIME System Time
HH:MM:SS
11:05:00
&SYSDAY
Day Of Week
X(3)
MON
&SYSOS
Host Op. System X(5)
VM/SP
Client Tools
75
Example
.logtable cust_logtable;
.run File Logon;
.IF &SYSDAY NE FRI
THEN
.DISPLAY This job runs
Fridays only TO file
ErrFile;
.LOGOFF;
.ENDIF;
******
******
******
Support
Environm
ent
Create or
acquire restart
Accept
LOGON
log table
string from
Logon File
Check for Day
of week.
Write a
message and
terminate job
if not FRI
Invoke utility
Client Tools
76
Fast Export
Exports large volumes of formatted data
from Teradata to a host file or userwritten application
Export from multiple tables
Uses support environment
Fully automated restart
Client Tools
77
BEGIN Export
Fast Export
.BEGIN EXPORT
SESSIONS max min
TENACITY hours
SLEEP
minutes
NOTIFY
high | medium | low | ;
To Specify:
Number of SESSIONS required
Number of hours Fast Export will try to
establish a connection
Number of minutes between each logon
attempts
Parameter for specifying the notify user
Client Tools
78
END Export
Fast Export
.END EXPORT
Delimits a series of commands that
define a single EXPORT action
Must be the last directive in a Fast
Export script
Causes the utility to send the SELECT to
the Teradata Database
Client Tools
79
Sample Fast Export Script
.logtable RestartLog;
Fast Export
Define Restart Log
.run file logon;
.begin export sessions 12;
Specify sessions.
.export outfile dataout;
Destination file.
select * from Customer;
.end export;
.logoff;
Send request
Terminate
sessions
Client Tools
80
Application Utility Checklist
BTE
DDL Functions QAll
DML Functions All
Multiple DML
Yes
Multiple tables Yes
Multiple
Yes
sessions
Protocol used
SQ
L
Conditional
Yes
Exp.
Arithmetic
Yes
Calc
Data
Yes
conversion
Error files
No
Error limits
No
User routines
No
Fast Export
FastLoa Fast
MultiLoa TPum
dLimite Export
d
p
No
d INS
SEL
No
Yes
No
Yes
Yes
Yes
FastLoa EXPORT
d No
Yes
No
Yes
Limite
Yes
d Yes
No
Yes
No
Yes
Yes
Client Tools
81
MultiLoad
Supports up to five populated tables
Multiple operations with one pass of input
files
Conditional logic for applying changes
Supports Inserts, UPDATEs, DELETEs and
UPSERTs
Full restart capability
Error reporting via error tables
Support for INMODs
Client Tools
82
MultiLoad
Table A
Table B
update Insert
MultiLoad
Table C
Delete
Table D
Host
Table E
Serve
r
Client Tools
83
MultiLoad Limitations
MultiLoad
No data retrieval capability
Arithmetic functions, exponentiation or
aggregates are not processed
Cannot process tables with Secondary
Indexes or Referential Integrity defined
Import tasks require use of Primary
Index
Client Tools
84
MultiLoad Tasks
MultiLoad
MultiLoad allows INSERT, UPDATE, DELETE
and UPSERT operations against up to five
target tables per task.
Two distinct tasks are:
IMPORT task:
These are the tasks which intermix a
number of different SQL/DML statements and
apply them to up to five different tables
depending on the APPLY conditions
DELETE task:
These are tasks which execute a single
DELETE statement
on a single table.
Client Tools
85
MultiLoad
IMPORT TASK
inserts
updat
Input
es
Data
delete
s
upsert
MultiLoad s
Hos
t
Apply
Conditions
Serv
er
T
1
T
2
T
3
T
4
T
5
Client Tools
86
Hos
t
MultiLoad
DELETE TASK
Data value
deletes only
MultiLoad
delete Rows
Serv
er
T
1
Client tools
88
Sample MultiLoad Script
MultiLoad
.logtable logtable001;
define
.logon TDPID/User/Passwd;
logtable
.begin mload tables Emploee, Emp_History;
begin
loading
.layout Employee_Trans;
define
.filler Transcode 1 char(3);
.field Empno
* smallint;
.field Deptno
* smallint;
.field Salary
* decimal(8,2);
.dml label payroll;
do insert for missing update rows;
update Employee set Salary = :Salary where
Empno = :Empno;
insert into Employee (Empno, Salary ) values
(:Empno, :Salary );
Client Tools
89
Sample MultiLoad Script .
MultiLoad
.dml label Terminate;
delete from Employee where Empno = :Empno;
insert into Emp_History (Empno, Deptno)
values (:Empno, :Deptno );
.import infile Filename;
File
layout Employee_Trans
name
Insert
apply Payroll where Transcode = PAY
stateme
nt
apply Terminate where Transcode = DEL;
.end mload
End
.logoff
loading
Client Tools
90
Script Components
MultiLoad
.logtable defines the name of the restart log.
.logon defines username which will own the
sessions.
.begin mload tables defines the tables which
will participate in the MultiLoad.
.layout defines the layout of the incoming
record(s).
.field defines the name of an input field, its
position in the record, and its datatype.
.dml label defines a set of DML instructions
which will be applied if conditions are met.
Client tools
91
Script Components .
MultiLoad
do insert for missing update rows indicates
that UPSERT processing follows:
do update if present.
do insert if not present.
.import infile references filename of the input
file.
layout references previously defined
layout.
apply references label to be applied
and conditions
under which to do so.
.end mload defines end of MultiLoad script.
.logoff terminate the sessions.
Client Tools
92
IMPORT Phases
MultiLoad
IMPORT consists of five separate phases of
processing.
Preliminary Phase
Basic setup
DML Phase
on AMPs
Get DML steps down
Acquisition Phase
to AMPs and
Send the input data
sort it
Application Phase
Apply the input data
to the
appropriate target tables
End Phase
Basic clean up
Client Tools
93
Preliminary Phase
MultiLoad
Validate all statements
and SQL
MultiLoad
Start all sessions
#AMPS + 2
Create work tables
table
One per target
Create error tables
target table
Two per
Create restart log
IMPORT run
One per
Apply locks to target tables
Prevent DDL
Client Tools
94
DML Transaction Phase
MultiLoad
Send prototype DML to the Server
Send DML steps in work tables
Add a USING modifier to the request
Host data to be filled in from input
file
Client Tools
95
Acquisition Phase
MultiLoad
Get the data from host and apply it to
appropriate AMP worktables.
- Duplicate rows for each successful
APPLY
- Make blocks and send to AMPs
- Deblock and resend record to correct
AMP
Reblock and store in worktable of target
table.
- Sort the reblocked records in the work
tables
- Sort by hash value and sequence to
be applied
Client Tools
96
Application Phase
MultiLoad
Execute MLOAD for each target table as a
single multi-statement request.
- End of host interaction until end of
phase
- AMPs independently apply changes to
target
tables
- Executed as a single transaction
without
rollback
- Restartable based on last checkpoint
- No Transient Journal needed
Client Tools
97
Cleanup Phase
MultiLoad
Execute END MLOAD processing as a series
of transactions performed by the host utility.
- All locks are released
- Table headers are restored across all
AMPs
- Dictionary cache of Target tables is
spoiled
- Statistics are reported
- Final error code is reported
- Target tables are made available to
other users
- Work tables are dropped
- Logtable is dropped
Client Tools
98
DELETE/IMPORT Differences
MultiLoad
DELETE tasks operate very similarly to
IMPORT tasks with some differences.
Deleting based on a Primary Index is not
permitted
DML DELETE statement sent to each AMP
with a match tag parcel
No Acquisition phase because no variable
input record to apply
Application phase reads each target block
and
deletes qualifying rows
All other aspects similar to IMPORT task
Client Tools
99
Restarting MultiLoad
MultiLoad
DBC restart
MLOAD reinitiated automatically after
DBC
recovery
Continue from checkpoint without user
interaction
Host restart
Resubmit the script as-is
MLOAD determines its stopping point and
restarts
Client Tools
100
Application Utility Checklist
BTE
DDL Functions QAll
DML Functions All
Multiple DML
Yes
Multiple tables Yes
Multiple
Yes
sessions
Protocol used
SQ
L
Conditional
Yes
Exp.
Arithmetic
Yes
Calc
Data
Yes
conversion
Error files
No
Error limits
No
User routines
No
MultiLoad
FastLoa Fast
MultiLoa TPum
dLimite Export
d All
p
No
d INS
SEL Ins/upd/d
el Yes
No
Yes
No
Yes
Yes
Yes
Yes
Yes
FastLoa EXPORT MultiLoa
d No
d Yes
Yes
No
Yes
Yes
Limite
Yes
Yes
d Yes
No
Yes
Yes
No
Yes
Yes
Yes
Yes
Client Tools
101
TPump
Allows near real-time updates from
transactional systems into the wearhouse
Performs INSERT, UPDATE, and DELETE
operations, or a combination, to more
than 60 tables at a time from the same
source
Alternative to MultiLoad for low-volume
batch maintenance of large databases
Allows target tables to:
- Have secondary indexes and Referential
Integrity
constraints
- Be MULISET or SET
Client Tools
102
TPump
Allows conditional processing
Supports automatic restarts
No session limit
Uses row-hash locks, allowing concurrent
updates on the same table
User can specify how many updates occur
minute by minute
No limit to the number of concurrent
instances
Client Tools
103
Limitations
Exponential operators are not allowed
Aggregate operators are not allowed
Arithmetic functions are not supported
There is a limit of four IMPORT
commands within a single TPump
load task
TPum
p
Client Tools
104
TPump Sample Script
.logtable logtbl002;
.logon TDPID/User,Passwd;
.begin load
errlimit 50
checkpoint 1
sessions 11
tenacity 2
error table tlnterr BTET pack 10;
.layout lay1a;
.filler ATest * byteint;
.field AByteInt * byteint;
.field AInteger * integer;
TPum
p
Client Tools
105
TPump Sample Script .
TPum
p
.dml label labela
ignore duplicate rows
ignore missing rows
ignore extra rows
insert into TPumptbl values (:AByteInt,
:AInteger );
.dml label labelb
ignore missing rows
ignore extra rows
do insert for missing update rows;
update TPumptbl set AInteger = AInteger
+1
where AByteInt = :AByteInt;
Client Tools
106
TPump Sample Script .
.import infile TPumptbl.dat
layout lay1a from 1 for 400
apply labela where ATest = 1;
.import infile Tpumptbl.dat
layout lay1a from 1 for 400
apply labelb;
.end mload;
.logoff;
TPum
p
Client Tools
107
TPump / MultiLoad
TPum
p
MultiLoad performance improves as the
volume of changes increases.
TPump does better on relatively low
volumes of changes
TPump uses macros to modify tables rather
than actual DML commands
MultiLoad uses the DML statements.
TPump uses row hash locking to allow for
concurrent read and write access to target
tables.
MultiLoad locks tables for write access
(Phase 2) until it completes
Client Tools
108
Application Utility Checklist
BTE
DDL Functions QAll
DML Functions All
Multiple DML
Yes
Multiple tables Yes
Multiple
Yes
sessions
Protocol used
SQ
L
Conditional
Yes
Exp.
Arithmetic
Yes
Calc
Data
Yes
conversion
Error files
No
Error limits
No
User routines
No
TPum
p
FastLoa Fast
MultiLoa TPum
dLimite Export
d All
p All
No
d INS
SEL Ins/upd/d I/U/
el Yes
DYes
No
Yes
No
Yes
Yes
Yes
Yes
Yes
Yes
Yes
FastLoa EXPORT MultiLoa TPump
d No
d Yes
Yes
Yes
No
No
Yes
Yes
Yes
Limite
Yes
Yes
d Yes
Yes
No
Yes
Yes
Yes
No
Yes
Yes
Yes
Yes
Yes
Client Tools
109
Choosing The Best Method
Utility support
- Does it run on your host?
Restart capability
- Is there a restart log?
- What happens with a Teradata restart?
- What happens if the host fails?
Multiple sessions
- Does the utility support multiple
sessions?
- How do you choose the optimum
number?
Error handling
- Are errors captured in an error file?
- Do you have control over error
