When an SQL Statement Fails in Teradata Mode

Basic Teradata Query Reference

brand
Teradata Tools and Utilities
prodname
Basic Teradata Query
vrm_release
16.20
category
Programming Reference
featnum
B035-2414-108K

When any statement in a Teradata Mode request fails, BTEQ returns a failure message. When a Teradata SQL statement fails, Teradata Database aborts the entire request and backs out every statement in the request, not just the one that failed. And, if the request uses Teradata semantics rather than ANSI semantics and was embedded in a transaction, Teradata Database aborts the entire transaction and backs out every statement in the transaction. The result is that the database reverts back to the state it was in when the transaction was initiated.

The BTEQ RETRY command, however, only resubmits the request that failed. If the request was embedded in a transaction, or in either a multi-statement request or an executing macro, the effects of any statements before the failed request are lost, as is any indication that a database transaction is in progress.

Resubmitting only the request that failed, in these situations, would most likely introduce undesirable changes to the database.

Example 1 – RETRY

Setting the RETRY command option to ON does not work for transactions coded like the following example because Teradata Database rolls back the transaction to, and including, the BEGIN TRANSACTION statement if one of the subsequent statements fails:

BEGIN TRANSACTION ;
UPDATE ... ;
UPDATE ... ;
END TRANSACTION ;

After the rollback, BTEQ retries the statement that failed. If the first UPDATE statement failed, BTEQ retries starting at the first UPDATE statement.

Example 2 – RETRY

BTEQ resubmits requests, not transactions. To resubmit an entire transaction, write it as a multi-statement single request by employing just one trailing semicolon. This alternate request format enables BTEQ to submit all of the statements as a single request and therefore also enables BTEQ to resubmit the entire transaction when using RETRY ON.

The SQL statements must use the following syntax:

BEGIN TRANSACTION
; UPDATE ...
; UPDATE ...
; END TRANSACTION ;

Example 3 – RETRY

This is an alternate method for coding transactions:

BT;
UPDATE...;
.IF ERRORCODE <> 0 THEN .GOTO FORGETIT
UPDATE...;
.IF ERRORCODE <> 0 THEN .GOTO FORGETIT
UPDATE...;
.IF ERRORCODE <> 0 THEN .GOTO FORGETIT
ET;
.LABEL FORGETIT

Setting the RETRY command option to OFF and testing the error code after each request flushes the remaining statements. Be careful, however, when using this method. When executing a macro within a transaction, it must follow the same logic because BTEQ executes each statement from the macro individually. Just testing after the EXEC statement does not work because the test applies only to the last statement in the macro.

Use the following BTEQ RETRY command to disable the retry function:

.SET RETRY OFF