An import task quickly applies large amounts of client data to one or more tables or views on Teradata Database.
If a Teradata Database restart/recovery operation interrupts a Teradata MultiLoad import task, the utility resumes processing from the point of interruption. It does not roll back and repeat processing that took place before the interruption. This effectively eliminates the processing penalty that would be incurred by transaction roll back during a restart/recovery operation.
The following table describes considerations when programming import tasks in the Teradata MultiLoad job script.
Topic | Import Task Considerations |
---|---|
Archive/Recovery Utility Statements | These statements include:
When associated with a Teradata MultiLoad target table, Teradata Database will:
The application phase can prevent normal processing of a DUMP statement. Take the following precautions to protect the target tables before starting a Teradata MultiLoad job:
Now, if a table becomes corrupted because of bad data or an operational error, restore the table from the dump and roll it forward to the checkpoint. |
Column Value Specifications | When considered in conjunction with each applicable imported record, column values must be explicitly specified for all columns:
To fulfill the requirement for update and delete target tables, use the AND logical operator with a series of terms in either of the following forms:
Teradata MultiLoad cannot process UPDATE or DELETE statements with OR logical operators. Instead, use two separate DML statements and apply them conditionally.
|
Data Source Restriction | All of the source data for a Teradata MultiLoad import task must originate from a data source on the client system. It cannot originate from existing tables in Teradata Database. Additionally, no statement within an import task can even reference any table or any row in Teradata Database that is not a specified import task table. |
DROP, and Other DDL Statements | Teradata MultiLoad rejects all DDL statements except DROP during an import task. During the application phase, however, while it is actively applying the source data to the target tables, Teradata MultiLoad delays both processing of the DROP statements and rejection of the others. After the application phase completes, Teradata MultiLoad processes any waiting DROP statements. Though this is the usual treatment for DROP statements, some cases may require that Teradata MultiLoad be stopped. At all other times, Teradata MultiLoad processes DROP statements immediately.
|
Invalid Command Parameters | The following command parameters are not allowed in a Teradata MultiLoad delete task:
|
Locks | Teradata MultiLoad uses the access lock to allow concurrent select access to all affected target tables throughout the entire import task. Concurrent DML statements that require read or write locks are permitted during the acquisition phase:
Except for select access, concurrent DML statements are not permitted during the application and cleanup phases of an import task. |
Multiple Operations | Each import task can include multiple INSERT, UPDATE, and DELETE statements, and the multiple DML operations can be conditionally applied to as many as five tables with a single pass of the client file. When associated with a client system file, each DML operation must provide a value for each nonnullable or nondefaultable column making up the primary index of the target table or the underlaying table of a target view. For each DML operation:
If a single client record satisfies more than one condition, Teradata MultiLoad sends multiple copies of the record to Teradata Database, each with a different match tag. |
Return Code | Depending on the particular customer application that is using MultiLoad, rows in either error table (ET or UV) are not necessarily unexpected, so setting a non-zero return/exit code is not the correct course of action in all cases. |
Statements Supported | Teradata MultiLoad supports any combination of the following DML statements when importing data from one or more sources:
Each group of DML statements must be preceded by a DML LABEL command that defines the label and error treatment options. Each data source:
Each LAYOUT command requires a sequence of FIELD commands, FILLER commands, and TABLE commands to define the record layout of a data source. |
Target Tables | Each import task can access up to five target tables on Teradata Database. To perform an import task, the appropriate access permission (for INSERT, UPDATE, and DELETE) are required on each target table. Each target table:
Each import task command need not access the same target table. |
Upsert Function | The Teradata MultiLoad upsert function applies consecutive UPDATE and INSERT statements to the same row. If the UPDATE statement fails because the target table row does not exist, Teradata MultiLoad automatically executes the INSERT statement, completing the operation in a single pass instead of two. If the Update statement fails due to an invalid length of data in the statement's WHERE clause, modify the data length and resubmit the statement. This might occur when the length of the data field in the UPDATE statement is larger than the Primary Index column length. Possible workarounds are using FILLER or an explicit cast of the data field to match the primary index column length. |