17.00 - Teradata TPump Command Conventions - Parallel Data Pump

Teradata® Parallel Data Pump Reference

Parallel Data Pump
June 2020
Programming Reference

Teradata TPump Reserved Words

Commands supported by Teradata TPump do not use reserved words (or keywords), except those that are operators, and only where an expression is allowed. Although there is no official restriction against the use of Teradata TPump reserved words as variable names, it is strongly recommended to avoid their use, as well as the use of Teradata SQL reserved words. Avoid words which are operators. Their use can result in ambiguous expressions.

The following table contains a list of Teradata TPump Operators.

Teradata TPump Operators 

Teradata SQL Reserved Words

Teradata TPump supports a subset of Teradata SQL listed in Teradata TPump Teradata SQL Statements. The subset of Teradata SQL consists only of statements beginning with one of the reserved words (or keywords) in Input Data Formats. Avoid the use of the Teradata SQL reserved words listed in Teradata TPump commands.

Conditional Expressions

Some of the commands described in this Programming Considerationssection use conditional expressions. If they evaluate to true, conditional expressions return a result of 1; if false, they return a result of 0.

The following table contains a list of Teradata TPump Conditional Expressions.

Teradata TPump Conditional Expressions 
+ - / MOD NOT
= NE <> ^=  
NOT= ~= GE >=  
GT > LE <=  

These conditional expressions are similar to those described in Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145, with the following exceptions:

  1. In the reference document, a column name in a conditional expression is equivalent, in this document, to the field name, in records from an external data source or a utility variable.
  2. In logical expressions that make up a conditional expression, the LIKE operator is not supported. In these expressions, only the following operators are supported:
    1. All comparison operators documented in Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145.
    2. NOT IN operator (only the first of the two forms).
  3. In arithmetic expressions that make up a logical expression, the following elements are not supported:
    1. The exponentiation operator
    2. Aggregate operators
    3. Arithmetic functions

Use Task Commands

A BEGIN LOAD command must begin each task to declare, at a minimum, the number of sessions involved in the load.

The logged on user must have the appropriate user privileges on the tables. At the time the BEGIN LOAD is initiated, SELECT privileges, as well as INSERT, UPDATE, and DELETE privileges are required, depending on the DML statements specified in the current task. Privileges must follow standard Teradata privilege rules. The kind of privilege required depends on the kind of DML statements to be applied. Teradata TPump tasks require that the target table is owned or access is available. Additional privilege for the target table is required, depending on the DML command, INSERT, UPDATE, or DELETE. The additional privilege is described for each statement type in later sections. No matter what kind of statement, CREATE TABLE privilege on the databases where the error tables are going to be placed. CREATE TABLE privilege for Teradata TPump to create a restart log table is also required. If the restart log table specified for the support environment already exists, INSERT and UPDATE privileges on the table are required.

In a Teradata TPump task, it is possible for more than one statement/data record combination to affect a single row. If application of any statement/data record combination to a row would produce an error, it is not applied, but all prior and subsequent error-free combinations affecting the same row or other rows are applied.

Teradata TPump can guarantee the order of operations on a given row via the correct use of the serialize option to specify the primary index of a given target table. When serialize is used, operations for a given set of rows occurs in order on one session. Without serialize, statements are executed on the first session available; hence, operations may occur out of order.

Assuming that the serialize option is in effect, note that the order in which DML statement or host record pairings are applied to a given target row is totally deterministic; so too is the order in which rows are applied to the target rows. Operations occur in exactly the same order as they are read from the data source and, if there are multiple apply clauses, in order by apply clause from first to last.

In addition to using serialize option in the BEGIN LOAD command, the SERIALIZEON keyword can also be specified in the DML command, which lets serialization be turned on for the fields specified. The SERIALIZEON keyword can be used in the DML command with the SERIALIZE keyword in the BEGIN LOAD command. When this is done, the DML-level serialization ignores and overrides the BEGIN LOAD-level serialization. In this case, the DML command with the serialization option in effect will be serialized on the fields specified.

Operations generated from the first IMPORT statement take place before operations generated from the second IMPORT.