Teradata TPT job is successful in case there are records in Error Table 2

Question

I have teradata TPT job defined, there is Error Limit =1 set in Update operator. When there are records in Error Table 1, the job fails, but when there are records only in Error Table 2, the job is successful. How do I make it fail in case of Error table 2 as well?

Answer 1

You could use the DDL operator to explicitly check after the UPDATE completes.

STEP FailIfError2Exists (
 APPLY ('ABORT WHERE (SELECT COUNT(*) FROM DBC.TablesV WHERE DatabaseName=''workingDatabaseName'' AND TableName=''errorTable2Name'')=1;')
 TO OPERATOR ($DDL() ATTRIBUTES(...));
);

The ABORT will return success if the condition is false or a 3514 error if it is true. Note that you will also need to wrap your UPDATE operator in an explicit STEP if it isn't already.

Note that a checkpoint file will be left on the client, and by default TPT would try to restart at the failing step. If you want the next iteration of the job to start at the beginning, you will want to remove that checkpoint (e.g. with twbrmcp).