Syntax - MultiLoad

Error treatment options specified by a previous DML LABEL command for subsequent INSERT, UPDATE, or DELETE statements

Name of the access module file to be used to import data. These access modules include:

Teradata Access Module for OLE DB: oledb_axsmod.dll on Windows platforms
Teradata Access Module for Named Pipes:
- np_axsmod.so on all supported UNIX platforms
- np_axsmod.dll on Windows platforms
Teradata Access Module for WebSphere MQ (client version):
- libmqsc.so on all supported UNIX platforms
- libmqsc.dll on Windows platforms
Teradata Access Module for WebSphere MQ (server version):
- libmqs.so on all supported UNIX platforms
- libmqs.dll on Windows platforms
Teradata Access Module for JMS:
- libjmsam.so on all supported UNIX platforms
- libjmsam.dll on Windows platforms

A personal shared library file name can be used if a custom access module is used.

The AXSMOD option is not required for importing disk files on either workstation-attached or mainframe-attached client systems, or magnetic tape files on mainframe-attached client systems. It is required for importing magnetic tape and other types of files on workstation-attached client systems.

Optional specification of the delimiter character that separates fields in the variable-length text records of the input data source.

If a 'c' specification is not used, the default is the pipe character (|).

When the character set of the job script is different from the client character set used for the job, Teradata MultiLoad translates the effective delimiter from the script character encoding to the client character encoding before separating fields with it.

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 workstation-attached systems.

Network Example: If the client character set is UTF-16, the script character set is UTF-8 and the following command is given:

…FORMAT VARTEXT '-'…

Teradata MultiLoad translates '-' from UTF-8 to UTF-16, and then separates the fields in the record according to the UTF-16 form of '-'.

Mainframe Example: If the client character set is UTF-8, the script character set is Teradata EBCDIC, and the following command is given:

…FORMAT VARTEXT '6A'xc…

Teradata MultiLoad interprets x’6A’ according to Teradata EBCDIC, translates it to the corresponding Unicode code point (U+007C “VERTICAL LINE”), and uses the UTF-8 encoding scheme of U+007C, 0x7C (which is '|' in 7-bit ASCII) as the delimiter character for the record.

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 which might be required in the job script. See International Character Set Support, B035-1125 for details.

For example, the code point of '|' is x'4F' in most IBM EBCDIC code pages. If '|' is specified as the delimiter in the script or leave the delimiter to default in a system environment using that type of IBM EBCDIC code page and UTF-8 data uses x'7C' (which is '|' in Unicode) as the delimiter, the job will run into errors because:

The code point of x'4F' in Teradata EBCDIC maps to U+008D, not U+007C.
Any character sequence that appears in the data cannot be used as a delimiter
No control character other than a tab character can be used in a delimiter

Optional keyword specification that writes input data records that produce errors to the standard error file.

Optional keyword specification that writes input data records that produce errors to the user specified error file. If the user doesn't specify the error file name, the default error destination is the standard error file. If the user does not specify the error file name, the default error destination is the standard error file for workstation-attached platforms, and the SYSOUT for mainframe-attached platforms.

Number of records, as an integer, starting at record m, to be processed.

If a FOR n or a THRU k specification is not used, Teradata MultiLoad continues processing through the last record obtained from the data source.

When “FOR 0” is used, Teradata MultiLoad defaults FROM as 2 and THRU as 1. A warning is issued. This makes Teradata Multi load only the 2nd record of the data file to the target table.

Format of the input record, where:

FASTLOAD specifies that each input record is a 2-byte integer, n, followed by n bytes of data, followed by an end-of-record marker (either X'0A' or X'0D').
BINARY specifies that each input record is a 2-byte integer, n, followed by n bytes of data.
TEXT specifies that each record consists of an arbitrary number of characters in the client session character set, followed by an end-of-record marker, which is:
- On UNIX platforms, the newline character (identified in Unicode as LINE FEED U+000A)
- On Windows platforms, the two-character sequence carriage return followed by line feed (identified in Unicode as CARRIAGE RETURN U+000D and LINE FEED U+000A, respectively)
For client session character sets other than UTF16, the end-of-record marker byte sequence is:
- On UNIX platforms, X'0A'
- On Windows platforms, X'0D0A'
For the UTF16 client session character set (in which each character is encoded in two bytes), the end-of-record marker byte sequence is:
- On big endian UNIX platforms, X'000A'
- On little endian UNIX platforms, X'0A00'
- On Windows platforms, X'0D000A00'
TEXT format should only be specified for character data like CHAR or ANSIDATE. Do not specify TEXT format for binary data, such as, INTEGER, BYTEINT, PERIOD, and other binary data. Depending on the actual byte values of the binary data, unexpected results may occur.

INDICATORS mode is not recommended when using TEXT record format. Please use UNFORMATTED record format instead.
UNFORMAT specifies that each input record is defined by FIELD, FILLER, and TABLE commands of the specified layout.
When using UNFORMAT formatting in z/OS, ensure that the data stream and data source are consistent with the layout defined in the utility script. Discrepancies in the length of the data stream could result in data corruption.
VARTEXT specifies that each input record is in variable-length text record format, with each field separated by delimiter characters, which:
- cannot be characters that appear in the data
- cannot be control characters, other than a TAB

If a FORMAT option is not specified, the default format is FASTLOAD.

Deallocation of the tape input device specified by ddname when the import operation completes on mainframe-attached client systems.

When de-allocated, any attempt to open the input device, either in the same Teradata MultiLoad task or in another task within the same script, produces an undefined ddname error.

The default is to not deallocate the device.

Logical record number, as an integer, of the record in the identified data source where processing is to begin.

If a FROM m specification is not specified, Teradata MultiLoad begins processing with the first record received from the data source.

Default condition to not deallocate the input tape device specified by ddname when the import operation completes on mainframe-attached client systems. Instead, the HOLD specification deallocates the device when the entire Teradata MultiLoad operation completes.

External data source that contains the input records on mainframe-attached client z/OS client systems. In z/OS, this is a DDNAME.

If ddname is specified, Teradata MultiLoad reads data records from the specified source. If modulename is also specified, Teradata MultiLoad passes the records it reads to the specified module.

The DDNAME must obey the applicable rules of the external system and may reference a sequential or VSAM data set.

A DDNAME must obey the same construction rules as Teradata SQL column names except that:

The “at” character (@) is allowed as an alphabetic character
The underscore character (_) is not allowed

If the DDNAME represents a data source on magnetic tape, the tape may be either labeled or non-labeled, as supported by the operating system.

Fully qualified UNIX or Windows path name for an input file on workstation-attached client systems

If the path name has embedded white space characters, enclose the entire path name in single or double quotes.

If INFILE filename is specified, Teradata MultiLoad reads data from the specified source. If INMOD modulename is also specified, Teradata MultiLoad passes the data it reads to the specified module.

Optional initialization string for the access module

The initialization string can contain double quotes, but not single quotes.

User exit routine that optionally reads, and always preprocesses, each record before passing the record to Teradata MultiLoad for processing on mainframe-attached client systems
Fully qualified UNIX or Windows pathname of the INMOD executable code on workstation-attached client systems

The modulename specification must obey the same construction rules as Teradata SQL column names except that on mainframe-attached client systems:

The “at” character (@) is allowed as an alphabetic character
The underscore character (_) is not allowed

The modulename specification must obey the applicable rules of the external system.

On some versions of UNIX operating systems, a ./ prefix character may have to be added to the modulename specification if the module is in the current directory.
On Windows platforms, if the INMOD module output messages are to stdout, the character set that INMOD uses is independent of the character set that MultiLoad uses, the display on stdout can be of mixed character sets. For example, IMMOD can output messages in ASCII and Multi load can output messages in UTF-16.

Layout of the input record, as specified by a previous LAYOUT command.

Optional keyword specification that inhibits the Teradata MultiLoad termination in response to an error condition associated with a variable-length text record.

The QUOTE option allows the user to specify whether input data values will never be quoted (QUOTE NO), optionally be quoted (QUOTE OPTIONAL), or always be quoted (QUOTE YES).

If data values are to be optionally or always quoted, the user can specify the enclosing open and close quote, consisting of one or more characters. The default is the quotation mark (") for both open quote and close quote. The open quote ('q') and close quote ('r') can be different. If only 'q' is specified, it is assumed to be both the open quote and close quote. If open quote, close quote, or both include apostrophe(s), any apostrophes must be doubled in the QUOTE specification.

Logical record number, as an integer, of the record in the identified data source where processing is to end.

If a THRU k or a FOR n specification is not used, Teradata MultiLoad continues processing through the last record obtained from the data source.

The TRIM option allows the user to request that no trimming is to be done, or that leading, trailing, or both leading and trailing pad characters are to be trimmed. The default pad character is blank (space).

Character string containing whatever parameters are to be passed to the corresponding user exit routine:

The parms string can include one or more character strings, each delimited on either end by an apostrophe or quotation mark.
The maximum size of the parms string is 1 KB.
Parentheses within delimited character strings or comments have the same syntactical significance as alphabetic characters.
Before passing the parms string to the user exit routine, Teradata MultiLoad replaces the following with a single blank character:
- Each comment
- Each consecutive sequence of white-space characters, such as blank, tab and so on, that appears outside of delimited strings
The parms string, as a whole, must be enclosed in parentheses. On mainframe-attached client systems, the parentheses are included in the string passed to the user exit routine.
When the user exit routine is an old FastLoad INMOD, the parms string must be FDLINMOD.

Condition that determines whether the indicated label options are applied to the records and sent to the database per subsequent INSERT, UPDATE, or DELETE statements where:

condition true = yes
condition false = no

The condition specification can reference:

Any combination of fields defined in the currently active layout
System and user-defined constants and variables
The fieldname1 specified in FIELD commands

If a WHERE condition specification is not used, the default condition is true/yes.

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 specified and the import data referenced in the condition to the same character set before evaluating the condition.

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 workstation-attached systems.

Network Example: If the client character set is UTF-16, the script character set is UTF-8, and the following command is given:

…APPLY lable1 WHERE C1 = 'INSERT';

Teradata MultiLoad translates the data in the C1 field to the UTF-8 form and compares it with the UTF-8 form of 'INSERT' to obtain the evaluation result.

Mainframe Example: If the client character set is UTF-8, the script character set is Teradata EBCDIC, and the following command is given:

…APPLY lable2 WHERE C2 = 'DELETE';

Teradata MultiLoad translates the data in the C2 field from the UTF-8 form to the Teradata EBCDIC form and compares it with the Teradata EBCDIC form of 'DELETE' to obtain the evaluation result.

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 which might be required in the job script. See International Character Set Support, B035-1125 for details.