TPump

** 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:

1. 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.
2.  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.

3. 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.

4. 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.