Following is an abbreviated Update operator task, an error table listing, and a procedure for determining the cause of the error. This task example includes only one DML group consisting of two DML statements, an INSERT statement, and an UPDATE statement for a complete Update operator job.
The example uses all of the error information from the error table. In most cases, you can determine the cause by evaluating only one or two columns of the error table entry.
This example uses the following APPLY statement to create the error tables in this section:
APPLY 'INSERT INTO table1 VALUES (:FIELD1,:FIELD2 ); UPDATE table2 SET field3 = :FIELD3 WHERE field4 = :FIELD4;'
In the following example, the Sequence Type and Number columns are the type and number assignments for each statement. The Statement column shows the actual job statements.
'INSERT INTO table1 VALUES (:FIELD1,:FIELD2 ); UPDATE table2 SET field3 = :FIELD3 WHERE field4 = :FIELD4;'
INSERT INTO table1 VALUES (:FIELD1, :FIELD2 );
UPDATE table2 SET field3 = :FIELD3 WHERE field4 = :FIELD4;
Following is the first error table created by the above task. The information indicates a problem with the example task:
Use the following procedure to evaluate error table information to isolate the problem:
- Check the DMLSeq field to find the DML group. It contains the sequence number 001.
- Check the STMTSeq field. The sequence number 002 in this field means that the error occurred while executing the second DML statement, which is the UPDATE statement in the example task.
- Verify that the Update operator job script uses two DML statements in the first DML group (because DMLSeq was 001).
- Check the ImportSeq field. The value of 002 indicates that the error occurred while processing a row from the second input data source.
- Check the meaning of the ErrorCode field. Error 2679, “The format or data contains a bad character” indicates a problem with the data from your client system.
- Check the ErrorField field. The field3 indicates that the error occurred while building field3 of the target table. The name refers to the field in the input schema from the Update operator job script.
- Because the script shows that the UPDATE statement is loading table2, you now know:
- What error occurred
- Which statement detected the error
- Which input data source has the error
- Which field in table2 has the error
- Check the SourceSeq field. The value of 20456 indicates that the problem is with the 20,456th record of the input source.
The problem is isolated, and it can now be fixed.