17.00 - Usage Notes - Parallel Data Pump

Teradata® Parallel Data Pump Reference

prodname
Parallel Data Pump
vrm_release
17.00
created_date
June 2020
category
Programming Reference
featnum
B035-3021-220K

Using predefined macros saves time because Teradata TPump does not need to create and drop new macros each time a Teradata TPump job script is run.

The rules for user-created macros are as follows:
  • Teradata TPump expects the parameter list for any macro to match the FIELD list specified by the LAYOUT in the script. FILLER fields are ignored. If the USE clause is used in the DML statement, Teradata TPump expects the parameter list for every macro in the DML statement to match the field list specified by the USE clause. The order should be the same as the fields in the LAYOUT.
  • The macro should specify a single prime index operation: INSERT, UPDATE, DELETE, or UPSERT. Teradata TPump reports an error if the macro contains more than one supported statement.
  • The restrictions on INSERT, UPDATE, DELETE, and UPSERT statements supported by Teradata TPump are described in the corresponding sections of this document.

If the EXECUTE statement is replacing an INSERT, UPDATE, DELETE, or UPSERT statement in a job script, the EXECUTE statement must be placed at the same location as the INSERT, UPDATE, DELETE, or UPSERT statement that it replaces. The following example shows an INSERT statement is replaced by an equivalent predefined macro:

   .DML LABEL LABELA;
   DELETE <delete-operands> ;
   INSERT <insert-operands> ;
   UPDATE <update-operands> ;

   .DML LABEL LABELA ;
   DELETE <delete-operands> ;
   EXECUTE <insert-macro-name> INSERT ;
   UPDATE <update-operands> ;
The correct syntax for a Teradata TPump predefined macro is one of the following:
  • CREATE MACRO <name> (<parameter list>) as (UPDATE....; ) ;
  • CREATE MACRO <name> (<parameter list>) as (INSERT.....; ) ;
  • CREATE MACRO <name> (<parameter list>) as (DELETE.....; ) ;
  • CREATE MACRO <name> (<parameter list>) as (UPSERT.....; ) ;
If the database supports Atomic upsert, then automatic use of Atomic upsert is allowed, when possible, without changing existing Teradata TPump scripts. This is accomplished in the following manner:
  • Teradata TPump attempts to use the Atomic upsert syntax in defining a single UPSERT macro (instead of an UPDATE/INSERT macro pair).
  • If the UPSERT macro is successfully defined, Teradata TPump uses the Atomic upsert function for the UPSERT.
  • If an error occurs during UPSERT macro definition, presumably due to a violation of Atomic upsert restrictions, Teradata TPump issues a warning and reverts to the current Teradata TPump upsert method of paired UPDATE/INSERT statements.

Teradata TPump will continue to operate as it does now when the existing Teradata TPump syntax for upsert is encountered, and references to predefined macros are used for either the UPDATE or the INSERT or both.

For example:

.DML LABEL <dml-label-name> DO INSERT FOR MISSING UPDATE ROWS ... ;
EXECUTE <update-macro-name> UPDATE ;
INSERT <insert-operands> ;

.DML LABEL <dml-label-name> DO INSERT FOR MISSING UPDATE ROWS ... ;
UPDATE <update-operands> ;
EXECUTE <insert-macro-name> INSERT ;

.DML LABEL <dml-label-name> DO INSERT FOR MISSING UPDATE ROWS ... ;
EXECUTE <update-macro-name> UPDATE ;
EXECUTE <insert-macro-name> INSERT ;

To allow for the use of predefined macros that take advantage of Atomic upsert, Teradata TPump command syntax supports an UPSERT macro:

.DML LABEL <dml-label>;
EXECUTE <upsert-macro-name> UPSERT ;

When using predefined macros for atomic upserts, the DML statement will have “Ignore Missing Update Rows” as a default option.

Atomic upsert syntax is not backward compatible; thus it cannot be used until the database is updated to a compatible release. If the database supports Atomic upsert, a Teradata TPump run can handle a mix of both standard and Atomic upserts.

Upserts are reported as UPDATEs and INSERTs in the statistics displayed by Teradata TPump (and passed to the NOTIFY EXIT routine), because an Atomic upsert that results in an UPDATE will be reported by the database as an UPDATE activity type, and an Atomic upsert that results in an INSERT will be reported by the database as an INSERT activity type.