15.10 - Determining the Cause of Errors - MultiLoad

Teradata MultiLoad Reference

prodname
MultiLoad
vrm_release
15.10
category
Programming Reference
featnum
B035-2409-035K

Determining the Cause of Errors

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

Table 31 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.

 

Table 31: 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 Table 32 is from the first error table (the acquisition or ET_tname1 error table.) The information indicates a problem with the example task.

 

Table 32: 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.