TPump DML Label

Database
Enthusiast

TPump DML Label

Hi,

What do the following statements in the DML LABEL section in a tpump script do exactly:
IGNORE MISSING ROWS
IGNORE EXTRA ROWS;

The input is a flat file with records inserting data into a database table.
Tags (1)
2 REPLIES
bwb
Teradata Employee

Re: TPump DML Label

The short answer: if you're doing only INSERTs, you needn't worry about IGNORE MISSING/EXTRA ROWS, which only apply to UPDATEs and DELETEs.

The long answer:

TPump (and the TPT Stream Operator...I'll use TPump below to refer to both) allow the user to specify what should happen in a number of exceptional situations.

If an INSERT into a SET target table with no unique indexes receives a duplicate row error (2802), MARK DUPLICATE INSERT ROWS requests that the error be recorded in the TPump error table, while IGNORE DUPLICATE INSERT ROWS requests that the error not be recorded in the error table.

Similarly, if an UPDATE changes a row in a SET target table with no unique indexes such that the row becomes a duplicate row, MARK/IGNORE DUPLICATE UPDATE ROWS controls whether or not the error is recorded in the TPump error table.

Duplicate row errors as described above are recorded in the error table as 2816 errors if MARK DUPLICATE INSERT/UPDATE ROWS is specified.

Note that MARK/IGNORE DUPLICATE INSERT/UPDATE ROWS has no effect if the target table is MULTISET (because duplicate rows are allowed in MULTISET tables) or if there are any unique indexes on the target table (because in the case of unique indexes, a uniqueness violation such as 2801 or 2803 will be returned rather than a duplicate row error). Uniqueness violations errors are always recorded in the error table.

An UPDATE or DELETE can update/delete zero, one, or more than one row in the target table. Normally, we expect exactly one row to be affected. Some applications may be designed such that affecting zero rows and/or more than one row are unexpected conditions to be logged, while other applications may expect zero or multiple rows to be affected.

Therefore, TPump offers the option to MARK or IGNORE MISSING/EXTRA UPDATE/DELETE ROWS, as four individual conditions (i.e., MISSING UPDATE, MISSING DELETE, EXTRA UPDATE and EXTRA DELETE). The code recorded in the error table for MARK EXTRA ROWS is 2817 (for both UPDATE and DELETE). The code recorded in the error table for MARK MISSING ROWS is 2818 (for both UPDATE and DELETE).
Enthusiast

Re: TPump DML Label

Thanks for the explanation