Mload - error treatment in DML label clause

Tools

Mload - error treatment in DML label clause

Greetings Experts,

"When there are no expressions involved and at most one conversion required before

loading the data into the target table, the data conversion takes place in the acquisition

phase and the converted data is loaded into the worktable. This means that any errors with the data or with the conversion will be detected in the acquisition phase.
"

If there are expressions involved, which doesn't make conversion in acquisition phase, then will ET table be loaded with any rows at the end of acquisition phase?  If so, what kind of errors!

Does "IGNORE DUPLICATE ROWS" applicable to constraint violations or does we have error treatment options only to the entire DUPLICATE row.  Say, if I have a check constraint on a column abc to have only values 1,2,3.  If I update the column abc in the target that has a value of 3 with 55, will the record in the source be placed into UV table?

Also, does "IGNORE DUPLICATE ROWS" have effect on unique constraint violations (USI, primary key)?  Will the records that violate USI, primary key be logged into the UV table or ignored!

Tags (2)
1 REPLY
Teradata Employee

Re: Mload - error treatment in DML label clause

Hi Cheeli,

MARK/IGNORE DUPLICATE ROWS has no effect if the table is a multiset table

(which allows duplicate rows).

IGNORE DUPLICATE ROWS has no effect if the table has a unique primary

index. Since a duplicate row implies a uniqueness violation in this case, the row

is logged to the uniqueness violation table.

In the case of an upsert operation, both the insert and update portions must fail

for an error to be recorded. In this case, the mark rows for the missing update

operations then have nulls for the target table columns.

If either INSERT or UPDATE with DUPLICATE is specified, then the MARK or

IGNORE specification applies to both insert and update operations.

Similarly, if either UPDATE or DELETE with MISSING is not specified, then the

MARK or IGNORE specification applies to both update and delete operations.

Note: MARK is the default for all actions except MISSING UPDATE for an

upsert operation.

You can have a try on your specific test case, and let us know if you have any question on the utility behavior. Thanks!

--Ivy.