Usage Notes - Parallel Data Pump

Teradata® Parallel Data Pump Reference

Product
Parallel Data Pump
Release Number
16.20
Published
September 2019
Language
English (United States)
Last Update
2019-10-11
dita:mapPath
dmq1512702641516.ditamap
dita:ditavalPath
Audience_PDF_include.ditaval
dita:id
B035-3021
lifecycle
previous
Product Category
Teradata Tools and Utilities

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 manual.

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 a Teradata Database server 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 Teradata Database 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 Teradata Database server is updated to a compatible release. If Teradata 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 Teradata Database as an UPDATE activity type, and an Atomic upsert that results in an INSERT will be reported by Teradata Database as an INSERT activity type.