A maximum of 100 IMPORT commands can be used in a single Teradata TPump load task. A single load comprises the set of commands and statements bounded by a BEGIN LOAD-END LOAD command pair. If the number of IMPORTs sent to the database for the load exceeds 100, TPump terminates with an error message. It is recommended to use a lower number of IMPORTs per load in order to limit the amount of memory needed to keep track of job-related statistics.
The maximum number of INSERT, UPDATE, and DELETE statements that can be referenced in an IMPORT is 128. The 128th DML which would cause the insertion of the DML sequence number of 128 for the DMLSEQ field in the error table could lead to database 3520 error.
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’s APPLY clauses. The referenced DML commands and their following DML statements must appear between the BEGIN LOAD command that defines the task and the referencing IMPORT commands. A statement or group of statements is applied if no condition is specified, or if the specified condition is true.
Teradata TPump permits multiple statements to be applied to the same data record in either of two ways. 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 then refer to the label of a different DML statement or group of statements. Each label’s statements are applied to the same data record under that condition specified in the respective clause. These features allow the same data record to be applied to different tables under the same or differing conditions.
VARTEXT Record Usage
When VARTEXT is specified, Teradata TPump assumes that the input data is variable-length text fields separated by a field delimiter character. It parses each input data record on a field-by-field basis, and creates a VARCHAR field for each input text field.
When using the VARTEXT specification, VARCHAR, VARBYTE, and LONG VARCHAR are the only valid data type specifications to use in Teradata TPump layout FIELD and FILLER commands.
Two consecutive delimiter characters direct Teradata TPump to null the field corresponding to the one immediately following the first delimiter character.
Also, if the last character in a record is a delimiter character, and there is at least one more field to be processed, then Teradata TPump nulls the field corresponding to the next one to be processed, as defined in the layout FIELD and FIELD command.
The total number of fields in each input record must be equal to or greater than the number of fields described in the Teradata TPump layout FIELD and FIELD commands.
If it is less, Teradata TPump generates an error message. If it is more, the database ignores the extra fields.
The last field of a record does not have to end with a delimiter character. It can end with a delimiter character, but it is not required.
- DISPLAY ERRORS
If NOSTOP is specified, Teradata TPump will not terminate even if an error is encountered.
By specifying both options and redirecting STDERR to a file location instead of the terminal screen, the Teradata TPump job runs to completion and saves all the error records. Then the error records can be manually modified and loaded into the table.
All IMPORT commands for a Teradata TPump task must appear between the BEGIN LOAD and END LOAD commands for the task.
Teradata TPump imposes several syntax rules for the parms string for an INMOD user exit routine. On entry to any INMOD user exit routine for Teradata TPump, the conventional parameter register points to a parameter list of two 32-bit addresses used to communicate with the INMOD.
&IMP <n>_<Apply label>_<x>
- n = the number of the IMPORT, from 1 through 100.
- Apply label = the label of the clause containing the DML command in question.
- x = the number of the statement within the containing APPLY clause.
The following script is an example of a Teradata TPump job using the APPLY keyword to create conditional clauses to apply DML INSERTs, UPDATEs, and UPSERTs to the IMPORT.