Usage Notes - MultiLoad

Teradata® MultiLoad Reference

Product
MultiLoad
Release Number
17.00
Published
June 2020
Language
English (United States)
Last Update
2020-06-18
dita:mapPath
aim1544831946660.ditamap
dita:ditavalPath
gyk1507317446489.ditaval
dita:id
B035-2409
lifecycle
previous
Product Category
Teradata Tools and Utilities

The following table describes the things to consider when using the IMPORT command.

Topic Usage Notes
Data Type Specifications When using the VARTEXT specification, VARCHAR, VARBYTE and LONG VARCHAR are the only valid data type specifications which can be used in the Teradata MultiLoad layout FIELD and FILLER commands.
Error Record Handling When Teradata MultiLoad encounters an error condition in an input record, it normally discards the record and terminates. When loading variable-length text records, either or both of these functions can be inhibited by specifying the error-handling options:
  • DISPLAY ERRORS
  • NOSTOP

    By specifying both options and redirecting STDERR to a file location instead of the terminal screen, the Teradata MultiLoad job runs to completion and saves all the error records. Then they can be manually modified and loaded into the table.

FREE/HOLD Option When Running Under z/OS The disposition of the output device specified in the JCL must be KEEP, not PASS, for the FREE/HOLD option to work in Teradata MultiLoad tasks running under z/OS.
Import Task Command Restrictions The combined number of Teradata SQL statements under the DML commands cannot exceed 100 within a single Teradata MultiLoad import task.

Sending an excessive number of statements to the database produces an error message indicating that there are too many DML steps for one Teradata MultiLoad import task.

For an import task, a candidate statement or group of statements is applied if no condition is specified, or if the specified condition is true.

Note, however, that for an import task, the only DML statements that are candidates for application by an IMPORT command are those within the scope of DML commands whose labels appear in one or more of the IMPORT command APPLY clauses. (The referenced DML commands and their following DML statements must appear between the BEGIN MLOAD command that defines the import task and the referencing IMPORT commands.)

Input Record Requirements The total number of fields in each input record must be equal to or greater than the number of fields described in the Teradata MultiLoad layout FIELD and FILLER commands.

If it is less, Teradata MultiLoad generates an error message. If it is more, the database ignores the extra fields.

Note that a delimiter character in the last field of a record is optional.

Null Fields Two consecutive delimiter characters direct Teradata MultiLoad to null the field corresponding to the one right after the first delimiter character.

Also, if the last character in a record is a delimiter character, and yet there was at least one more field to be processed, then Teradata MultiLoad nulls the field corresponding to the next one to be processed, as defined in the layout FIELD and FILLER commands.

Multiple APPLY Clauses In an import task, multiple APPLY clauses can be applied to the same data record in either of two ways. This features allows the same data record to be applied to different tables under the same or different conditions.

First, if an APPLY clause refers to a label whose scope includes multiple DML statements, each of these statements is applied to the same data record under the same condition specified in the clause.

Second, if multiple APPLY clauses are used, each can refer to thelabel specification of a different DML statement or group of statements. Each label specification is applied to the same data record under the condition specified in the respective clause.

Primary Indexes and Partitioning Column Sets IMPORT tasks require that all values of the primary index column be set and all values of the partitioning column be set for deletes and updates. IMPORT tasks do not support updates of the partitioning column set. IMPORT tasks do not support primary index updates.
Record Length Validation By default, Teradata MultiLoad does not compare the actual record length of the import data with the record length indicated by the layout specifications for the job.
If they are not the same, the default behavior of Teradata MultiLoad depends on whether the actual import data record length is less than or greater than the record length indicated by the layout specifications:
  • If the actual import data record length is less than the length indicated by the layout specifications, then Teradata MultiLoad terminates with an error indication.
  • If the actual import data record length is greater than the length indicated by the layout specifications, then Teradata MultiLoad ignores the extra fields and continues with the import task.

To change the default behavior and enforce a record-length validation check, use a MATCHLEN=on entry in the Teradata MultiLoad configuration file before invoking Teradata MultiLoad. In this case, Teradata MultiLoad terminates with an error message whenever the actual and specified record lengths are different.

For information about using the Teradata MultiLoad configuration file, see Teradata MultiLoad Configuration File.

VARTEXT Records When VARTEXT is specified, Teradata MultiLoad assumes that the input data is variable-length text fields separated by up to 10 field delimiter characters. The utility parses each input data record on a field-by-field basis, and creates a VARCHAR field for each input text field.