TPUMP Utility

TPUMP

TPump is shortened name for Teradata Parallel Data Pump . As learned Fastload and Multiload are loads 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,Upadtes,Deletes and Upserts from Flat filed to populated Teradata tables at ROW LEVEL.

TPump supports

• Secondary Indexes
• Referential Integrity
• Triggers
• Join indexes
• Pumpdata in at varying rates.

Tpump also have limitations.

• 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 supports One Error Table.The error table does the following:

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

Like the other utilities, a TPump script is fully restartable as long as the log table and error tables are not dropped

A Sample TPump Script

1. The script on the following page follows these steps:
2. Setting up a Logtable
3. Logging onto Teradata
4. Identifying the Target, Work and Error tables
5. Defining the INPUT flat file
6. Defining the DML activities to occur
7. Naming the IMPORT file
8. Telling TPump to use a particular LAYOUT
9. Telling the system to start loading
10. Finishing and log off of Teradata

LOGTABLE  EMPDB.EMP_TPUMP_LOG;

LOGON TDDB/USERNAME,PWD;

BEGIN LOAD
PACK 5
RATE 10
ERROR TABLE EMPDB.TPUMPERROR;

.LAYOUT RECLAYOUT;

.FIELD    EMP_NUM         * INTEGER;
.FIELD    DEPT_NUM       * SMALLINT;
.FIELD    FIRST_NAME    * CHAR(20);
.FIELD    LAST_NAME     * VARCHAR(20);
.FIELD    SALARY            * DECIMAL(8,2);

.DML LABEL EMP_INS;
INSERT INTO EMPDB.EMP_TABLE
(EMP_NUM, DEPT_NUM, FIRST_NAME, LAST_NAME, SALARY)
VALUES 
(:EMP_NUM, :DEPT_NUM, :FIRST_NAME, :LAST_NAME, :SALARY)

.IMPORT INFINE C:\TEMP\TPUMP_FLAT_FILE.txt;

LAYOUT RECLAYOUT
APPLY EMP_INS

.END LOAD;
.LOGOFF;