17.00 - Usage Notes - Parallel Data Pump

Teradata® Parallel Data Pump Reference

Parallel Data Pump
June 2020
Programming Reference

The SQL EXECUTE command must be used between the BEGIN LOAD command and the END LOAD command.

All INSERT, UPDATE, DELETE, and EXECUTE statements specified in the Teradata TPump script should fully specify the primary index of the referenced table to prevent the generation of table-level locks.

A maximum of 1500 DML statements may be packed into a request; the default is 20 statements.

Teradata TPump assumes that row hash locking is used by INSERT, UPDATE, DELETE, and EXECUTE statements. If row hash locking is not used, Teradata TPump will run anyway, but may encounter trouble because table-level locking will cause each statement to block.

In addition, Teradata TPump does not support UPDATE or EXECUTE statements that modify the primary index of the target table. Teradata TPump performs no checking to prevent the script author from creating DML that requests unreasonable updates, except that Teradata TPump will not use Atomic UPSERT if the UPDATE portion of the UPSERT specifies an unreasonable update. This restriction is imposed by the database.

IGNORE DUPLICATE ROWS does not apply if there are ANY unique indexes in the row.

Teradata TPump converts INSERT, UPDATE, and DELETE statements into macro equivalents, and, depending on the packing specified, submits multiple statements in one request. Teradata TPump also supports the EXECUTE statement, which can be used to bypass the macro creation step for frequently executed macros. For more information on the EXECUTE statement, see the EXECUTE command.

The maximum number of INSERT, UPDATE, and DELETE statements that can be referenced in an IMPORT is 128. The 128th DML which would cause the insertion of the DML sequence number of 128 for the DMLSEQ field in the error table could lead to database 3520 error.

At the end of an IMPORT, an environmental variable is established for each DML command executed. Teradata TPump variables are not limited to 30 characters. These variables contain the activity counts associated with each statement. The variables created are of the form:

&IMP <n>_<Apply label>_<x>
where the following is true:
  • n = the number of the IMPORT, from one through four.
  • Apply label = the label of the clause containing the DML command in question.
  • x = the number of the statement within the containing APPLY clause.