** TPump : Teradata Parallel
Data Pump
-
TPump was developed to handle batch loads with low volumes.
-
Other load utilities like,FastLoad and MultiLoad assemble massive volumes of
data rows into 64K blocks and then moves those blocks. But TPump, does NOT move
data in the large blocks. Instead, it 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
Has Many Unbelievable Abilities:
- Just in Time: OLTP systems, for ATM machines are having tremendous speed in executing transactions. But how soon can you get the data into the data warehouse?. TPump allows the user to accomplish near real-time updates from source systems into the Teradata data warehouse.
- Throttle-switch Capability: With TPump we may stipulate how many updates may occur per minute. This is also called the statement rate. In fact, you may change the statement rate during the job, "throttling up" the rate with a higher number, or "throttling down" the number of updates with a lower one.
An example: Having this capability, you
might want to throttle up the rate during the period from 12:00 noon to 1:30 PM
when most of the users have gone to lunch. You could then lower the rate when
they return and begin running their business queries.TPump running in the
background all the time, and just control its flow rate.
- DML Functions: Like MultiLoad, TPump does DML functions, including INSERT, UPDATE and DELETE it also supports UPSERTs like MultiLoad.
FastLoad can only load one table; MultiLoad
can load up to five tables. TPump can load more than 60 tables at a time!.And
the number of concurrent instances in such situations is unlimited.
How could you use this ability? Well, imagine
partitioning a huge table horizontally into multiple smaller tables and then
performing various DML functions on all of them in parallel. Keep in mind that
TPump places no limit on the number of jobs that may be established.
- Stopping without Repercussions: TPUMP can be stopped at any time and all of locks may be dropped with no ill consequences.
Benefits:
a. TPump allows both Unique and Non-Unique Secondary Indexes (USIs
and NUSIs). FastLoad allows neither, MultiLoad allows just NUSIs.
b. TPump allows the target tables to either be empty or to be
populated with data rows.
c. Tables allowing duplicate rows (MULTISET tables) are allowed.
d. Referential Integrity is allowed and need not be dropped.
e. Triggers are also allowed.
f. Queries can access a table concurrently with TPump.
g. Several TPump jobs can run against the same table at the same
time.
h. Allows conditional processing.
i. No session limit- use as many sessions as necessary.
j. No limit to the no.of concurrent instances.
k. User can specify how many updates occur minute by minute; can be
changed as the job runs.
TPump
Has Some Limits:
a. No concatenation of input data files
is allowed.
b. TPump will not process aggregates,
arithmetic functions or exponentiation. If you need data conversions or math,
you might consider using an INMOD to prepare the data prior to loading it.
c. The use of the SELECT function is not
allowed.
d. No more than four IMPORT commands may
be used in a single load task. This means that a most, four files can be
directly read in a single run.
e. 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. This must be specified when you create the table. Any
dates using the default yy format for the year are taken to mean 20th century
years.
f. On some network attached systems, the
maximum file size when using TPump is 2GB. This is true for a computer running
under a 32-bit operating system.
Creation
of sample ML script:
I. Creates
the logtable -Required.
II. Logon to Teradata
III. Begin
Load. Mention 1 Error_table for each 1 Target_table
IV. Defines
the Input file layout.
V. DML query under .DML label.
VI. Define the Input file and specify the
corresponding Layout & DML Q
VII. End load and logoff Teradata.
**
Note: Comment
lines have been placed inside the script [/*. . . . */].
TPump Commands and
Parameters:
- Each command in TPump must begin on a
new line, preceded by a dot. It may utilize several lines, but must always end
in a semi-colon.
- Like MultiLoad, TPump makes use of several
optional parameters in the .BEGIN LOAD command.
Common
LOAD Parameters with MultiLoad
.BEGIN
LOAD
SESSIONS
n (required)
ERRORTABLE tblname (optionl,
default: jobname_ET)
ERRLIMIT errcount/errpercent
CHECKPOINT freq (default: 15 min)
TENACITY hrs (default: 4)
SLEEP min (default:
6)
However, TPump has other unique parameters in
.BEGIN LOAD.
SERIALIZE On/Off (default: ON,if UPSERT)
PACK n (default:
20, max: 600)
PACKMAXIMUN
RATE n
LATENCY n (range : 10 - 600 sec)
NOMONITOR (default: monitor ON)
ROBUST On/Off (default: On)
MACRODB dbname (default:
logtable dbase)
ERRLIMIT:
Specify the maximum
number of errors, or the percentage, that you will tolerate during the
processing of a load job.
- ERRLIMIT no is always greater than the
PACK no. If the PACK factor is a smaller than the ERRLIMIT, the job will
terminate, telling you that you have gone over the ERRLIMIT. When this happens,
there will be no entries in the error tables.
CHECKPOINT:
It refers to the
number of minutes, or frequency, at which you wish a checkpoint to occur. This
is unlike Mulitload which allows either minutes or the number of rows.
SESSIONS: No. of sessions that should be
established with Teradata. ** TPump places no limit on the number of SESSIONS
you may have.
TENACITY: No. of hrs need to try to
establish a connection to the system. Default : 4.
SLEEP
: No. of mins need to
wait between login connections. Default : 6.
SERIALIZE: Use the SERIALIZE parameter when you
are going to specify a PRIMARY KEY in the .FIELD command.
- For example, ".FIELD Salaryrate *
DECIMAL KEY." If you specify SERIALIZE TPump will ensure that all
operations on a row will occur serially.
- If you code "SERIALIZE", but
do not specify ON or OFF, the default is ON. Otherwise, the default is OFF
unless doing an UPSERT.
- SERIALIZE guarantees both input record
order and all records with the same PI value will be handled in the same
session. It's recommended to specify the PI in the statement column(s) as KEY.
- SERIALIZE ON: can eliminate lock delays
or potential deadlocks caused by PI collisions, improving performance.
- SERIALIZE OFF: does not guarantee the
order in which transactions are processed. Transactions are processed in the
order they are encountered and placed in the first available buffer.
PACK:
Specifies the no.of statements to pack into a multi-statement request.
OR
- How
many rows are written to the table at a time.
- Improves network/channel efficiency by
reducing the no.of sends and receives between the application and Teradata.
RATE:
It refers to the
Statement Rate. It shows the initial maximum number of statements that will be
sent per minute.
OR
- How
many packs we'll see written to a table per minute.
-
A zero or no number at all means that the rate is unlimited. If the Statement
Rate specified is less than the PACK number, then TPump will send requests that
are smaller than the PACK number.
NOMONITOR:
Use this parameter
when you wish to keep TPump from checking either statement rates or update
status information for the TPump Monitor application.
ROBUST:
ROBUST defines how
TPump will conduct a RESTART.
- ROBUST ON means that one row is written
to the Logtable for every SQL transaction. The downside of running TPump in
ROBUST mode is that it incurs additional, and possibly unneeded, overhead. ON
is the default.
- If you specify ROBUST OFF, you are
telling TPump to utilize "simple" RESTART logic: Just start from the
last successful CHECKPOINT. Be aware that if some statements are reprocessed,
such as those processed after the last CHECKPOINT, then you may end up with
extra rows in your error tables. Why? Because some of the statements in the
original run may have found errors, in which case they would have recorded
those errors in an error table.
ROBUST ON is recommended for these
specific conditions:
-
INSERTS into MULTI-SET tables, as such tables will allow re-insertion of the
same rows multiple times.
- When UPDATEs are based on calculations
or percentage increases.
- If PACK factor are large,and applying
and rejecting duplicates after a restart would be time consuming.
- If data is time-stamped at the time
it is inserted into the database.
MACRODB: Indicate a database to
contain any acros used by TPump.
- Remember, TPump does not run the SQL
statements by itself. It places them into Macros and executes those Macros for
efficiency.
Eg:
.BEGIN LOAD
SESSIONS 64
TENACITY 2
ERRLIMIT 5
CHECKPOINT 1
PACK 40
RATE 1000
ERRORTABLE samples.emps_tmp_err;
ERRLIMIT 5 says that the job should terminate after
encountering five errors. You may set the limit that is tolerable for the load.
CHECKPOINT 1 tells TPump to pause and evaluate the
progress of the load in increments of one minute.
SESSIONS 64 tells TPump to establish 64 sessions with
Teradata.
TENACITY 2 says that if there is any problem
establishing sessions, then to keep on trying for a period of two hours.
PACK 40 tells TPump to "pack" 40 data rows
and load them at one time.
RATE 1000 means that 1,000 data rows will be sent per
minute.
** Note: for more
examples,see 24x7 pdf;
Handling Errors in TPump
Using the Error Table:
- Unlike FastLoad and
MultiLoad, TPump uses only ONE Error Table per target table, not two.
- If you name the table,
TPump will create it automatically. Entries are made to these tables whenever
errors occur during the load process.
- Like MultiLoad, TPump
offers the option to either MARK errors (include them in the error table) or
IGNORE errors (pay no attention to them whatsoever). These options are listed
in the .DML LABEL sections of the script and apply ONLY to the DML functions in
that LABEL. The general
default is to MARK. If you specify nothing, TPump will assume the default. When
doing an UPSERT, this default does not apply.
The error table does the following:
· Identifies errors
· Provides some detail about the
errors
.
Stores a portion the actual offending row for debugging.
- When compared to the
error tables in MultiLoad, the TPump error table is most similar to the
MultiLoad Acquisition error table. Like that table, it stores information about
errors that take place
while it is trying to
acquire data. It is the errors that occur when the data is being moved, such as
data translation problems that TPump will want to report on. It will also want
to report any difficulties compiling valid Primary Indexes. Remember, TPump has
less tolerance for errors than FastLoad or Multiload.
Input Formats:
- Supports the following five
format options: BINARY, FASTLOAD, TEXT, UNFORMAT and VARTEXT.
a. BINARY : Each record is a 2-byte
integer, n, that is followed by n bytes of data. A byte is the smallest address
space you can have in Teradata.
b. FASTLOAD : This format is the same as
Binary, plus a marker (X '0A' or X '0D') that specifies the end of the record.
c. TEXT: Each record has a variable
number of bytes and is followed by an end of the record marker.
d. UNFORMAT : The format for these input
records is defined in the LAYOUT statement of the MultiLoad script using the
components FIELD, FILLER and TABLE.
e. VARTEXT: This is variable length text
RECORD format separated by delimiters such as a comma. For this format you may
only use VARCHAR, LONG VARCHAR (IBM) or VARBYTE data formats in your MultiLoad
LAYOUT. Note that two delimiter characters in a row denote a null value between
them.
eg:
.IMPORT
INFILE "C:/TD/TP/inp_emps.txt"
FORMAT a/b/c/d/e
LAYOUT inp_layout
APPLY insrt_q;
- With upsert, if the
UPDATE fails because the target row does not exist, TPump automatically
executes the INSERT statement. This capability can save considerable loading
time by
completing this operation
in a single pass instead of two.
