Interpreting Error Table Information|DML - Interpreting Error Table Information - Parallel Data Pump

The following Teradata TPump task describes how to interpret the error table information to isolate and fix the problem. This task is greatly abbreviated, containing only the DML command and the IMPORT command. A probable sequence of actions for locating and fixing the problem follows the task.

SEQ TYPE SEQ # 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 SECNDDML;

In this example, the Statement column represents the user entry. The SEQ # and SEQ TYPE columns are the Sequence Number and Sequence Type assigned to each statement. If an error occurs while using this task and the information in the following error table is displayed, where the error occurred and what was being executed at the time of the error can be determined.

ImportSeq DMLSeq SMTSeq ApplySeq SourceSeq DBCErrorCode DBCErrorField
---       ---    ---    ---      --------- -----        ------------
002       001    002    001          20456  2679              field3 

The following sequence provides a series of analytical steps for extracting and interpreting the information in this row of the error table.

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 indicates that the error occurred while executing: change this statement of the first DML command, which is the UPDATE statement in the task mentioned in the previous point.
3. Verify that the script shows that the DML command is used twice, once in each IMPORT.
4. The value of 002 in the ImportSeq field shows that the error occurred in the second IMPORT clause.
5. The value of 001 in the ApplySeq field indicates that the error occurred in the first apply of that clause, which was being executed when the error occurred.
6. The value of 2679 in the DBCErrorCode field shows:

   The format or data contains a bad character

   which indicates that bad data is coming from the client.

7. The ErrorField field of the error row shows that the error occurred while building field3 of the table.
8. The script then shows that the error occurred when field3 was being built from :FIELD3 in the client data.
9. The LAYOUT clause in the script shows where the problem data is positioned within the row coming from the client.
10. The script shows that the IMPORT clause with the error was loading file2, and indicates what error occurred, which statement detected the error, and which file has the error.
11. The SourceSeq field of the error table pinpoints the problem location in the 20456th record of this file. The problem is isolated and can now be fixed.

Most problems in the error tables do not require as much research as this example. This error was selected in order to use all of the information in the error table. As a rule, only one or two error table items need to be looked at to locate and correct problems.