Determining the Cause of Errors - MultiLoad

Teradata® MultiLoad Reference

Product
MultiLoad
Release Number
17.10
Published
February 2022
Language
English (United States)
Last Update
2022-02-04
dita:mapPath
fel1608578437279.ditamap
dita:ditavalPath
kju1619195148891.ditaval
dita:id
B035-2409
lifecycle
previous
Product Category
Teradata Tools and Utilities

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 and Number for Example 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.

Error Table Listing 
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:

  1. Check the DMLSeq field to find the statement being executed. It contains the sequence number 001.
  2. 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.
  3. Verify that the Teradata MultiLoad job script uses two DML LABEL commands, one for each IMPORT command.
  4. Check the ImportSeq field. The value of 002 indicates that the error occurred while processing the second IMPORT command.
  5. Check the ApplySeq field. The value of 001 indicates that the error occurred while processing the first APPLY function.
  6. 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.
  7. Check the ErrorField field. The field3 indicates that the error occurred while building field3 of the target table.
  8. Check the Teradata MultiLoad job script. It shows that field3 of the target table was being built from FIELD3 of the client data.
  9. 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
  10. 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.