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.
TPUMP structure and process
It processes the data packet by packet
- 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
Inclined to build a profession as Teradata Developer? Then here is the blog post on, explore Teradata Training
- RUN FILE
- BEGIN LOAD
- END LOAD
.LOGON 127.0.0.1 myuser,myuser1;
.BEGIN LOAD session 1 pack 2
.field f0 * varchar(10);
.field f1 * varchar(20);
.field f2 * varchar(10);
.dml label test_insert;
insert into test_tpump
.IMPORT INFILE c:TESTtpump_infile.txt
format vartext '|'
Difference between TPUMP and Multi load
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:
- DROP error & Log tables & macros
- 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.
For every DML commands, we require one macro
For In-depth knowledge on Teradata click on:
- BTEQ Features in Teradata
- Memory Management in TeraData
- Types of OLAP’s in TeraData
- Multiple Cliques In System (OR) High Level In TeraData