TPUMP Structure and Process In TeraData

Ratings:
(4)
Views: 0
Banner-Img
Share this blog:

TPUMP

TPump is the shortened name for Teradata Parallel Data Pump. Fastload and Multiload are the loads that contain a huge volume of data. But TPump loads data one row at a time, using row hash locks. Because it locks at this level, and not at the table level like MultiLoad, TPump can make many simultaneous, or concurrent, updates on a table.

TPump performs Inserts, Updates, Deletes, and Upserts from Flat filed to populated Teradata tables at ROW LEVEL.

Table of Contents

TPUMP supports:

TPUMP Structure and Process

It processes the data packet by packet

TPUMP Structure

  • TPUMP Structure is similar to multi-load
  • TPUMP does not Run in phases But it creates macro for every DME Operation
  • This macro performs loading of data

Trump supports One Error Table. The error table does the following:

  • Identifies errors
  • Provides some detail about the errors
  • Stores a portion of the actual offending row for debugging

 

Want to acquire industry skills and gain complete knowledge of Teradata? Enroll in Instructor-Led live Teradata Training to become Job Ready!

TPUMP COMMANDS

  • ACCEPT
  • RUN FILE
  • SET
  • BEGIN LOAD
  • FIELD
  • DML
  • END LOAD
  • IMPORT
  • LAYOUT

TPUMP Example:

.Logtable myuser.tpump_yemp1;

.LOGON 127.0.0.1 myuser,myuser1;

 DATABASE myuser;

.NAME test_tpump;

.BEGIN LOAD session 1 pack 2

 errortable ytpump_ET;

.LAYOUT infile1;

.field f0 * varchar(10);

.field f1 * varchar(20);

.field f2 * varchar(10);

.dml label test_insert;

insert into test_tpump

values (:f0,:f1,:f2);

.IMPORT INFILE c:TESTtpump_infile.txt

 format vartext '|'

 apply test_tpump;

.END LOAD;

.LOGOFF;

Difference between TPUMP and Multi load

                        TPUMP                      Multi load
  • Loads  60 or more table(max 64 Tables)
  • Loads max 5 Tables
  • Does not Runs in phases But create macros for DML Operations
  • Runs in 5 phases
  • No limitation in parallel Running of script
  • max 5 scripts of ML, FL exp at a time
  • Locks at the Row level
  • Locks at Tables level
  • Process packet by packet
  • Process block by block
  • Len volume of data loading
  • Huge volume of data loading
  • Supports USI, RI sand Triggers data loading
  • Does not support

 

Read these latest Teradata Interview Questions that help you grab high-paying jobs

Limitations of TPUMP

  • No concatenation of input data files is allowed.
  • TPump will not process aggregates, arithmetic functions, or exponentiation.
  • The use of the SELECT function is not allowed.
  • No more than four IMPORT commands may be used in a single load task.
  • Dates before 1900 or after 1999 must be represented by the yyyy format for the year portion of the date, not the default format of yy.
  • On some network-attached systems, the maximum file size when using TPump is 2GB.
  • TPump performance will be diminished if Access Logging is used.

TPUMP Error Handling

TPUMP Script is failed and error tables are available then how do we restart?

a) In case of the old file to restart:

  • Do not Drop error, log tables & macros
  • Rectify the error in the script or file and Run freshly

b)In case of the new file the restart:

  1. DROP error & Log tables & macros
  2. Specify a new file in the script and Run freshly

Note:  in case of the old file to restart if macros are available to execute macros directly by using exec or execute commands.

Exec<Macro Name>

For every DML commands, we require one macro

For In-depth knowledge on Teradata click on:

 

You liked the article?

Like: 0

Vote for difficulty

Current difficulty (Avg): Medium

EasyMediumHardDifficultExpert
IMPROVE ARTICLEReport Issue

About Author

Authorlogo
Name
TekSlate
Author Bio

TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills in the market.