17.00 - Usage Notes - Parallel Data Pump

Teradata® Parallel Data Pump Reference

Parallel Data Pump
June 2020
Programming Reference

The following notes describe how to use DELETE statements following a DML command.

A DELETE statement may also be used in the support environment; normal rules for DELETE are followed in that case.

Teradata TPump operates only on single table statements so DELETE statements must not contain any joins.

To delete records from a table, the username specified on the LOGON command must have DELETE privilege on the specified table.

When the condition(s) of the DELETE statement WHERE clause are evaluated, the result can be definitely true, definitely false, or indeterminate. If the result is true for a specific row, Teradata TPump deletes the row. An indeterminate result, due to an abnormal arithmetic condition such as underflow, overflow, or division by zero, is treated as an error, and Teradata TPump records both row and error code in the error table.

The DELETE statement must identify only one object.

Remember the following when constructing scripts:
  • A DELETE statement can be applied to either a table or view, provided that the view does not specify a join.
  • Equality values for all the primary index columns should normally be specified in the WHERE clause.
The OR construct can be used in the WHERE clause of a DELETE statement; alternatively, two or more separate DML statements (one per OR term) can be used, with the DML statements applied conditionally with the APPLY clause of the IMPORT command. The nature of the alternatives will usually make one of the methods more appropriate.
  • The columns specified in this clause need not be a part of any index, but can be one or more nonindexed columns. This clause may specify nonequality values for any combination of columns of unique indices, or any values for other columns.
  • 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.
  • The maximum number of DML statements that can be packed into a request is 1500. The default number of statements packed is 20.

DML validtime qualifier and NONTEMPORAL semantics are supported. For more information, see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.