Syntax - MultiLoad

Teradata® MultiLoad Reference

Product
MultiLoad
Release Number
16.20
Published
October 2018
Language
English (United States)
Last Update
2018-10-10
dita:mapPath
lsl1527114222348.ditamap
dita:ditavalPath
Audience_PDF_product_tpt_userguide_include.ditaval
dita:id
B035-2409
lifecycle
previous
Product Category
Teradata Tools and Utilities


where the following is true:

datadesc
Type and length of data in the field
Teradata MultiLoad generates the USING phrase accordingly, with the user-assigned field name, for subsequent insert, update, and delete operations.
For details on data types and data conversions, see SQL Data Definition Language (B035-1184) for details.
DROP…
Character positions to be dropped from the specified fieldname1
These must be of a character data type.
Teradata MultiLoad drops the specified characters and presents the field to Teradata Database as VARCHAR data type.
fieldexpr
Concatenation of two or more items, either:
  • fields
  • character constants
  • string constants
or a combination of these, as in:
fieldname2||fieldname2||fieldname2...
The field names within a layout must be unique. Nested concatenations are not supported.
Each fieldname2 that is actually a field name must be specified in its own FIELD or FILLER command with a startpos parameter and a datadesc parameter that specifies the character data type.
The fieldname1 parameter in other FIELD commands can be referenced in fieldexpr expressions.
When the character set of the job script is different from the client character set used for the job, Teradata MultiLoad translates the character and the string constants specified in the expression from the script character encoding to the client character encoding before concatenating the constants with the specified fields.
For example, the job script must be in Teradata EBCDIC when using the UTF-8 client character set on z/OS; the job script can be in UTF-8 when using the UTF-16 client character set on network-attached systems.
Before using the UTF-8 client character set on a mainframe platform, check the character set definition to determine the code points and the Teradata EBCDIC and Unicode character mapping. Different versions of EBCDIC do not always agree as to the placement of any special characters required in the job script. See International Character Set Support (B035-1125) for details.
fieldname1
Name of an input record field
The fieldname1 specification can be referenced by:
  • Subsequent INSERT, UPDATE, or DELETE statements
  • The NULLIF nullexpr expression of another FIELD command
  • The conditional expression of a LAYOUT command or the APPLY clause of an IMPORT command
A fieldname1 specification must obey the same construction rules as Teradata SQL column names.
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.
Each fieldname appearing in the conditional expression must be defined by either:
  • The startpos and datadesc parameters of the FIELD command
  • A FILLER command
  • A TABLE command
The fieldname specifications in a nullexpr condition cannot be defined by a fieldexpr of a FIELD command.
The fieldname1 parameter in other FIELD commands can be referenced in nullexpr conditions. A FastLoad-type value specification (NULLIF=value) cannot be used as the nullexpr specification. The nullexpr specification must be a complete conditional expression that includes a logical operator.
When the character set of the job script is different from the client character set used for the job, Teradata MultiLoad translates the string constants and the import data referenced in the expression to the same character set before evaluating the expression.
For example, the job script must be in Teradata EBCDIC when using the UTF-8 client character set on z/OS; the job script can be in UTF-8when using the UTF-16 client character set on network-attached systems.
Network Example: If the client character set is UTF-16 and the script character set is UTF-8, and the following commands are given, MLOAD translates the data in the C1 field to the UTF-8 form and compares it with the UTF-8 form of 'DELETED' to obtain the evaluation result.
.field C1 * varchar(20);
.field C2 * varchar(40) nullif c1 = 'DELETED';
Mainframe Example: If the client character set is UTF-8 and the script character set is Teradata EBCDIC, and the following commands are given, Teradata MultiLoad translates the data in the C1 field from the UTF-8 form to the Teradata EBCDIC form and compares it to the Teradata EBCDIC form of 'removed' to obtain the valuation result.
.field C1 * char(20);
.field C2 * char(40) nullif c1 = 'removed';
Before using the UTF-8 client character set on a mainframe platform, check the character set definition to determine the code points and the Teradata EBCDIC and Unicode character mapping. Different versions of EBCDIC do not always agree as to the placement of any special characters required in the job script. See International Character Set Support (B035-1125) for details.
startpos
Starting position of the specified field in the data records of an external data source
The startpos can be specified as:
  • An unsigned integer, which is a character position starting with 1
  • An asterisk, which means the next available character position beyond the preceding field
The field positions of input records can be redefined by specifying the same positions in multiple FIELD commands.