Usage Notes - MultiLoad

Teradata® MultiLoad Reference
Product
MultiLoad
Release Number
16.20
Published
October 2018
Language
English (United States)
Last Update
2018-10-10
dita:mapPath
lsl1527114222348.ditamap
dita:ditavalPath
Audience_PDF_product_tpt_userguide_include.ditaval
dita:id
B035-2409
lifecycle
previous
Product Category
Teradata Tools and Utilities

The following table describes the things to consider when using the DML LABEL command.

DML LABEL Considerations 
Topic Usage Notes
Bypassing the Duplicate Row Check Duplicate row checking is not performed if the table is a multiset table (which allows duplicate rows) or if the table has a unique primary index (the uniqueness test takes the place of the duplicate row check).
DO INSERT FOR ROWS Option By following the rules for upsert operations, a number of uses for the DO INSERT ROWS option can be found.

With an upsert operation, Teradata MultiLoad needs only one pass of the data to both:

  • Update the rows that need to be updated.
  • Insert the rows that need to be inserted.

The alternative would be to either:

  • Presort the data for the update and insert operations.
  • First use an UPDATE statement with all of the data, and then use an INSERT statement with the data that failed the update operation.
Import Tasks For import tasks, as many as five distinct error treatment options with one DML LABEL command can be specified. For example:
.DML LABEL COMPLEX
IGNORE DUPLICATE INSERT ROWS
MARK   DUPLICATE UPDATE ROWS
IGNORE MISSING   UPDATE ROWS
MARK   MISSING   DELETE ROWS
DO INSERT FOR MISSING UPDATE ROWS;
MARK MISSING UPDATE ROWS Option Specifying MARK MISSING UPDATE ROWS while using the DO INSERT ROWS option, causes Teradata MultiLoad to record any update record that fails in the tname4 application error table, along with an error code indicating that the INSERT statement was then executed.

Then, if the insert operation fails, the insert record is also recorded in the application error table.

The default for an upsert operation, however, is to not mark missing update rows. This is because when the upsert feature is used, it expects the insert operation to occur when the update operation fails.

Failure of the update portion of an upsert operation does not, in itself, constitute an error and it is not to be treated as one.
MARK MISSING DELETE ROWS Option The MARK MISSING DELETE ROWS option has no meaning when used with the DO INSERT FOR ROWS option.
Upsert Feature When using the upsert feature:
  • There must be exactly two DML statements in the DML group.
  • The first DML statement must be an UPDATE statement that follows all of the Teradata MultiLoad task rules.
  • The second DML statement must be an INSERT statement, and it must reflect the same primary index specified in the WHERE clause of the UPDATE statement. This is true for both a single-column primary index and a compound primary index.
  • The value for the partitioning column for an update must be specified if the target table has PPI.
  • Both DML statements must refer to the same table.
  • The data length defined for any character fields in the WHERE condition of an UPDATE statement for an upsert must be less than or equal to the corresponding field length. Otherwise, the upsert is not processed. For example:
       UPDATE T1 SET c1=:f2 WHERE P1=:f1

If P1 is char(5) and f1 is char(6), the upsert is canceled.