Teradata TPump Command Conventions

Teradata Parallel Data Pump Reference

brand
Teradata Tools and Utilities
prodname
Parallel Data Pump
vrm_release
15.00
category
Programming Reference
featnum
B035-3021-034K

Teradata TPump Command Conventions

The following command conventions apply when using Teradata TPump.

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 (see Table 13). Their use can result in ambiguous expressions.

Table 13 contains a list of Teradata TPump Operators.

 

Table 13: Teradata TPump Operators 

Commands

AND

BETWEEN

EQ

GE

GT

IN

IS

LE

LIKE

LT

MOD

NE

NOT

NULL

OR

Teradata SQL Reserved Words

Teradata TPump supports a subset of Teradata SQL listed in Table 25 on page 87. The subset of Teradata SQL consists only of statements beginning with one of the reserved words (or keywords) in Table 1. Avoid the use of the Teradata SQL reserved words listed in Teradata TPump commands.

Conditional Expressions

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

Table 14 contains a list of Teradata TPump Conditional Expressions.

 

Table 14: Teradata TPump Conditional Expressions 

Commands

+

/

MOD

NOT

||

IS NOT NULL

IS NULL

EQ

 

=

NE

<>

^=

 

NOT=

~=

GE

>=

 

GT

>

LE

<=

 

LT

<

BETWEEN

NOT BETWEEN

 

AND

OR

IN

NOT IN

 

These conditional expressions are similar to those described in SQL Functions, Operators, Expressions, and Predicates (B035‑1145), with the following exceptions:

1 In the reference manual, 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:

a All comparison operators documented in SQL Functions, Operators, Expressions, and Predicates (B035‑1145).

b 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:

a The exponentiation operator

b Aggregate operators

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