When an SQL Statement Fails in Teradata Mode | Basic Teradata Query - When an SQL Statement Fails in Teradata Mode - Basic Teradata Query

Basic Teradata® Query Reference

Product
Basic Teradata Query
Release Number
17.10
Published
February 2022
Language
English (United States)
Last Update
2022-02-03
dita:mapPath
nnw1608578382132.ditamap
dita:ditavalPath
obe1474387269547.ditaval
dita:id
B035-2414
lifecycle
previous
Product Category
Teradata Tools and Utilities

When any statement in a Teradata Mode request fails, BTEQ returns a failure message. When an SQL statement fails, the 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, the 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 the 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