17.00 - The Atomic Upsert Feature - Parallel Data Pump

Teradata® Parallel Data Pump Reference

Parallel Data Pump
June 2020
Programming Reference

The basic upsert function has been enhanced to support an Atomic upsert capability. This enhancement permits Teradata TPump to perform single-row upserts in a single pass. This one-pass logic adopts the upsert-handling technique used by MultiLoad. The one-pass logic is designated Atomic to distinguish the grouping of paired UPDATE and INSERT statements which are executed as a single SQL statement.

The syntax for Atomic upsert consists of an UPDATE statement and an INSERT statement, separated by an ELSE keyword.

Existing Teradata TPump scripts using the Atomic upsert form do not have to be changed. Teradata TPump automatically converts the old UPDATE/INSERT pairs to the Atomic upsert feature whenever appropriate. Any attempts to change this will result in a syntax error.

The new syntax, which can also be used by CLIv2 and BTEQ applications, is dependent on whether or not the database version, against which the Teradata TPump job is run, supports this feature. If the database does not support Atomic Upsert, Teradata TPump reverts to the earlier logic of sending the INSERT request if an UPDATE request fails.

The three basic constraints on the upsert feature are:
  • SAME TABLE: The UPDATE and INSERT statements must specify the same table.
  • SAME ROW: The UPDATE and INSERT statements must specify the same row; that is, the primary index value in the INSERT row must be the same as the primary value in the targeted UPDATE row.
  • HASHED ROW ACCESS: The UPDATE fully specifies the primary index, allowing the target row to be accessed with a one-AMP hashed operation.

Although Teradata TPump does not verify basic upsert constraints, the database will reject Atomic upsert constructs that fail the constraint test, and notify Teradata TPump by returning an appropriate error message to the client.

If the Atomic Upsert is done on a Temporal table, the optional Temporal qualifier can only be added before the UPDATE statement.