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.
- 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 indicates that the error occurred while executing: change this statement of the first DML command, which is the UPDATE statement in the above task.
- Verify that the script shows that the DML command is used twice, once in each IMPORT.
- The value of 002 in the ImportSeq field shows that the error occurred in the second IMPORT clause.
- 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.
- 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.
- The ErrorField field of the error row shows that the error occurred while building field3 of the table.
- The script then shows that the error occurred when field3 was being built from :FIELD3 in the client data.
- The LAYOUT clause in the script shows where the problem data is positioned within the row coming from the client.
- 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.
- 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.