DWHPro Insights

Teradata TPump: What it is and How it Facilitates Real-Time Data Updates

By Roland Wenzlofsky ·

Teradata TPump: What it is and How it Facilitates Real-Time Data Updates

What is Teradata TPump?

TPump is short for Teradata Parallel Data Pump.

TPump performs transactional row operations but can pack multiple operations into requests. Request packing and session configuration affect throughput; it is misleading to describe every row as a separate client request.

Teradata TPump is not intended for loading large volumes of data rapidly. Instead, it facilitates a steady stream of data to enter the database.

The Teradata TPump Features

Transactional systems require rapid data transfer to the data warehouse. TPump facilitates near real-time updates from source systems to Teradata.

Throttling: TPump enables you to establish a statement rate for data updates during loading, indicating the desired frequency of updates per minute.

The statement rate can be adjusted during TPump job execution. It may be expedient to accelerate the load process during nightly batch processing and decelerate it (thus reducing resource utilization) when business users generate their reports during the day.

DML Functionality: TPump can do DML functions (like Multiload). This includes INSERT, UPDATE, and DELETE statements.

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

TPump offers advantages over bulk loading with Fastload or Multiload by allowing for the use of USI and NUSI. Unlike Fastload, which requires the target table to be empty, TPump can load data into an already populated table, including tables using Multiload.

Duplicate-row behavior depends on table design and operation. A TPump BEGIN LOAD task has its configured error table; do not assume one error table per target table or a universal duplicate contrast with MultiLoad.

The most critical TPump Limitations

  • No concatenation of input data files is allowed.
  • TPump can’t handle aggregates, arithmetic functions, or exponentiation.
  • The use of SELECT is not allowed.
  • A single load task may use no more than four IMPORT commands. We can directly read at most four files in a single run.
  • TPump performance will decrease if access logging is enabled on the target objects.

Monitoring TPump

The TPump Monitor tool, included with Teradata TPump, enables real-time monitoring of TPump job statuses and permits modification of statement rates for active jobs.

To initiate the monitor on LINUX, use the command:

tpumpmon [-h] [TDPID/],[,]

TPump Error Handling

Common TPump load errors include:

2817: Activity count greater than one for TPump UPDATE/DELETE.

TPump encounters this error when it attempts to UPDATE or DELETE additional rows.

2818 indicates that a TPump UPDATE or DELETE affected no rows. Decide whether that is an error using the operation and business rules; a zero-row result does not by itself prove a utility failure.

A missing match can be expected or unexpected. Review the task error handling and source keys before retrying.

TPump Restartability

TPump Example Script

Incomplete historical TPump control-job sketch: input layout and full task setup are not supplied, and standalone instructions must be comments. This is not a runnable utility job; full execution, restart and batching behavior were not tested.

Incomplete historical TPump control example. Supply the target DDL, matching input file, credentials and release-specific utility options before testing. This article does not provide a self-contained tested TPump job or verify restart behavior.

.logtable TheDatabase.tpumplog;
.logon DWHPRO,******;
database TheDatabase;
.name test;
.begin load errortable ET_CUST
sleep 5
checkpoint 5
sessions 8
errlimit 4
pack 4
tenacity 4
serialize on;
/* Specify the applicable key in the .FIELD command. */
.layout mylayout;
.field CUSTID * varchar(5) key;
.field CUSTNAME * varchar(30);
.dml label INST;
insert into CUSTOMER
(CUSTID,CUSTOMER_NAME
)
values
(:CUSTID,
:CUSTNAME
);
.import infile CUSTDATA.txt
format vartext ','
layout mylayout
apply INST;
.end load;
.logoff;

The Fundamentals of FastLoading on Teradata

Building or improving a data pipeline?

Discuss ingestion, transformations, orchestration, quality checks and operational handover.

Explore data engineering services

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam