The following topics provide an abbreviated Teradata MultiLoad task example, an error table listing, and a procedure for determining the cause of the error.
Note that, for simplicity, the task example includes only DML and IMPORT commands for a complete Teradata MultiLoad job.
The example procedure uses all of the error information from the error table. In most cases, the error cause can be determined by evaluating only one or two columns of the error table entry.
Teradata MultiLoad Task Example
The following table shows the Sequence Type and Sequence Number columns that are the type and number assignments for each statement, and the Statement column shows the actual Teradata MultiLoad job statements.
Sequence Type | Sequence Number | Statement |
---|---|---|
DML | 001 | .DML LABEL FIRSTDML; |
STMT | 001 | INSERT INTO table1 VALUES( :FIELD1, :FIELD2 ); |
STMT | 002 | UPDATE table2 SET field3 = :FIELD3 WHERE field4 = :FIELD4; |
DML | 002 | .DML LABEL SECNDDML; |
STMT | 001 | DELETE FROM table3 WHERE field3 = :FIELD3; |
IMPORT | 001 | .IMPORT INFILE file1 LAYOUT layout1 |
APPLY | 001 | APPLY FIRSTDML |
IMPORT | 002 | .IMPORT INFILE file2 LAYOUT layout2 |
APPLY | 001 | APPLY FIRSTDML |
APPLY | 002 | APPLY SECONDDML; |
Error Table Listing
The information in the following table is from the first error table (the acquisition or ET_tname1 error table.) The information indicates a problem with the example task.
Import Seq | DML Seq | SMT Seq | Apply Seq | Source Seq | Error Code | Error Field |
---|---|---|---|---|---|---|
002 | 001 | 002 | 001 | 20456 | 2679 | field3 |
Error Isolation Procedure
The following procedure shows how to use the information from the error table to isolate the problem:
- Check the DMLSeq field to find the statement being executed. It contains the sequence number 001.
- Check the SMTSeq field. The sequence number 002 in this field means that the error occurred while executing the second statement after the first DML LABEL command, which is the UPDATE command in the example task.
- Verify that the Teradata MultiLoad job script uses two DML LABEL commands, one for each IMPORT command.
- Check the ImportSeq field. The value of 002 indicates that the error occurred while processing the second IMPORT command.
- Check the ApplySeq field. The value of 001 indicates that the error occurred while processing the first APPLY function.
- Check the meaning of the ErrorCode field. Error 2679, “The format or data contains a bad character” indicates a problem with the data from the client system.
- Check the ErrorField field. The field3 indicates that the error occurred while building field3 of the target table.
- Check the Teradata MultiLoad job script. It shows that field3 of the target table was being built from FIELD3 of the client data.
- Note that the LAYOUT specification of the IMPORT command shows where the problem data is positioned within the row of the client data.
Since the script shows that the IMPORT command was loading file2:
- The error that occurred is known
- Which statement detected the error
- Which file has the error
- Check the SourceSeq field. The value of 20456 indicates that the problem is with the 20,456th record of the file.
The problem is isolated, and can now be fixed.