17.00 - Syntax - Parallel Data Pump

Teradata® Parallel Data Pump Reference

prodname
Parallel Data Pump
vrm_release
17.00
created_date
June 2020
category
Programming Reference
featnum
B035-3021-220K


where the following is true:

fieldname
Name of an input record field to which:
  1. a DML statement refers,
  2. a nullexpr of a FIELD command or condition expression of a LAYOUT command refers, or
  3. a condition expression of the IMPORT command APPLY clause refers.
A fieldname must obey the same rules for its construction as Teradata SQL column names.
fieldname can be referenced in other FIELD commands via NULLIF and field concatenation expressions, and in APPLY WHERE conditions in IMPORT commands.
startpos
Starting position of a field of the data records in an external data source
It may be specified as an unsigned integer which is a character position starting with 1, or as an asterisk which means the next available character position beyond the preceding field. Nothing prevents redefinition of positions of input records by specifying the same positions in multiple FIELD commands.
Note that where input records may be continued by use of the CONTINUEIF condition, a startpos specified as an unsigned integer refers to a character position in the final concatenated result from which the continuation indicator fields have been removed. Refer to the description of the condition parameter of the LAYOUT command.
datadesc
Type and length of data in the field.
Teradata TPump generates the USING phrase accordingly with the user-assigned field name to which the body of the DML statement refers.
MACROCHARSET
An optional clause for character fields only (including character fields constructed via concatenation). If a field-level MACROCHARSSET is present for a field of character type, TPump generates a CHARACTER SET clause in the macro creation DDL for the corresponding field using the specified character set.
servercharsetname
Server storage character set name. Refer to the following two documents for the valid server storage character set names: Teradata Vantage™ - Advanced SQL Engine International Character Set Support, B035-1125 and Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
nullexpr
Condition used for selectively inserting a null value into the affected column
The condition is specified as a conditional expression involving any number of fields, each represented by its fieldname, and constants. All fieldnames appearing in the conditional expression must be defined by any of the following specifications:
  1. The startpos and datadesc parameters of the FIELD command.
  2. A FILLER command.
  3. A TABLE command.
If the specified condition is true, Teradata TPump sends the data to the database with indicators, whether or not the INDICATORS option is specified on the LAYOUT command.
When the character set of the job script is different from the client character set used for the job (for example, on z/OS the job script must be in Teradata EBCDIC when using the UTF-8 client character set, or UTF-16 client character set can be used with the job script in UTF-8), Teradata TPump will translate the string constants specified in the expression and the import data referenced in the expression to the same character set before evaluating the expression.
For example, when the client character set is UTF-16 and the script character set is UTF-8, if the following commands are given:
.field C1  *  varchar(20);
.field C2  *  varchar(40) nullif c1 = 'DELETED';
Teradata TPump will translate the data in the C1 field to the UTF-8 form and compare it with the UTF-8 form of 'DELETED' to obtain the evaluation result.
Similarly, on the mainframe, when the client character set is UTF-8 and the script character set is Teradata EBCDIC, if the following commands are given:
.field C1  *  char(20);
.field C2  *  rchar(40) nullif c1 = 'removed';
Teradata TPump will translate the data in the C1 field from the UTF-8 form to the Teradata EBCDIC form and compare it to the Teradata EBCDIC form of 'removed' to obtain the valuation result.
When using UTF-8 client set on the mainframe, be sure to examine the definition in Teradata Vantage™ - Advanced SQL Engine International Character Set Support, B035-1125 to determine the code points of the special characters which might be required. Different versions of EBCDIC do not always agree as to the placement of these characters.

The mappings between Teradata EBCDIC and Unicode can be referred to in Teradata Vantage™ - Advanced SQL Engine International Character Set Support, B035-1125.

The fieldname1 parameter in other FIELD commands can be referenced in nullexpr.
fieldexpr
Concatenation of two or more items, either:
  • fields
  • character constants
  • string constants
or a combination of these, as in:
fieldname1||'C'||fieldname2||'STRING'||fieldname3...
The field names within a layout must be unique and the data type of the field must be either CHAR or VARCHAR. Nested concatenations are not supported.
If all items of the fieldexpr are fixed character (for example, no VARCHARs), the data type of the resulting field is CHAR(m), where “m” is the sum of the length for each component item.
If at least one component of the fieldexpr is a VARCHAR, the data type of the resulting field is VARCHAR(m), where “m” is the sum of the maximum length for each component item.
When the character set of the job script is different from the client character set used for the job (for example, on z/OS the job script must be in Teradata EBCDIC when using the UTF-8 client character set, or UTF-16 client character set can be used with the job script in UTF-8), Teradata TPump will translate the character constants and the string constants specified in the expression from the script character set form to the client character set form before concatenating the constants with the specified fields.
When using the UTF-8 client set on the mainframe, be sure to examine the definition in Teradata Vantage™ - Advanced SQL Engine International Character Set Support, B035-1125 to determine the code points of the special characters which might be require. Different versions of EBCDIC do not always agree as to the placement of these characters.

The mappings between Teradata EBCDIC and Unicode can be referred to in Teradata Vantage™ - Advanced SQL Engine International Character Set Support, B035-1125.

DROP
Characters present in the specified position(s) to be dropped from the specified fieldname, which must be of a character data type.
Teradata TPump drops the specified characters and presents the field to the database as a VARCHAR data type.
If two dropping actions are specified, they must not be identical.
If a FIELD command defines a fieldname in terms of two or more concatenated fieldname fields, any specified DROP clause applies to the concatenated result, not to the individual fieldname fields. But, because each fieldname must be defined by its own previous FIELD command, a DROP clause can be specified on these commands to apply to the individual fields.
KEY
Keyword, which, when added to the end of the FIELD command, specifies that the field is part of the hash key for purposes of serialization, if the SERIALIZE parameter on the BEGIN LOAD command is active.
The serialization feature is meaningful only when a primary key for the loaded data is specified via this KEY option.