Structuring Requests in BTEQ for Retryable (and Other) Errors

Tools
Tools covers the tools and utilities you use to work with Teradata and its supporting ecosystem. You'll find information on everything from the Teradata Eclipse plug-in to load/extract tools.
bwb
Teradata Employee

Structuring Requests in BTEQ for Retryable (and Other) Errors

The Teradata RDBMS can return a variety of errors. Some of these errors are retryable (that is, the request can be resubmitted); the simplest example of this is a 2631 (Transaction aborted due to %VSTR) caused by a deadlock condition. Other errors are not retryable; data-related errors (constraint violations, etc.) are an example.

For certain retryable error codes, BTEQ will, by default, retry the failing request. The user can disable such retries via the SET RETRY command. For example, if a 2631 occurs, and retries have not been disabled, BTEQ will resubmit the request.

Normally, resubmittal of requests that failed with retryable errors is the logical behavior. However, the user must be aware of the implications of this behavior when a transaction spans multiple requests. Further, the behavior of the Teradata RDBMS depends on which session mode is set.

Teradata vs. ANSI Transaction Semantics

The Teradata RDBMS offers two primary session modes: Teradata and ANSI. These are frequently referred to as "transaction semantics," although there is more to the modes than just transaction semantics. However, for the purposes of this article, only transaction semantics are significant. The session mode can be set explicitly (in BTEQ, this is done using the SET SESSION TRANSACTION BTET|ANSI command), or defaulted to the SessionMode DBS Control field setting in the DBS GDO.

In Teradata (BTET) mode, transactions can be either explicit or implicit. An explicit transaction begins with a BEGIN TRANSACTION (BT) statement and ends with one of the following:

  • An END TRANSACTION (ET) statement, which ends the transaction normally
  • An ABORT statement, which rolls back and ends the transaction
  • A request failure, which rolls back and ends the transaction

An implicit transaction occurs if a request contains multiple statements (including cases where a multi-statement macro is executed). A single-statement request not within a BT/ET can also be considered an implicit transaction.

In ANSI mode, a transaction is automatically initiated when a request is submitted and a transaction is not already active. A transaction ends with one of the following:

  • A COMMIT statement, which ends the transaction normally (the next request will initiate another transaction)
  • A ROLLBACK statement, which will roll back and end the transaction

Under almost all conditions, a request failure in ANSI mode will roll back the request but leave the transaction active. There are a few situations in which the entire transaction will be rolled back and ended, but for purposes of this discussion those scenarios will be ignored.

BTEQ Operates at the Request Level

BTEQ operates at the request, rather than the transaction, level. In fact, BTEQ isn't aware of a session's transactional state. Let's examine the behavior of multi-request transactions in both Teradata (BTET) and ANSI modes.

Teradata Mode

Let's discuss Teradata mode first. Assume an explicit transaction that spans three requests (in this and subsequent SQL, semicolons are positioned to allow BTEQ to properly determine request boundaries):

Request 1:

BT
;INSERT INTO <table> VALUES (1,1,...)
;INSERT INTO <table> VALUES (1,2,...);

Request 2:

INSERT INTO <table> VALUES (2,1,...)
;INSERT INTO <table> VALUES (2,2,...)
;INSERT INTO <table> VALUES (2,3,...);

Request 3:

INSERT INTO <table> VALUES (3,1,...)
;INSERT INTO <table> VALUES (3,2,...)
;ET;

Consider what happens if, for instance, a data-related error (such as a range or uniqueness constraint violation) occurs in each of the following three scenarios:

  • If error raised in request 1:
    • Request 1 raises a data-related error; request 1 is rolled back and the explicit transaction is ended; then
    • Request 2 is processed successfully as an implicit (multi-statement request) transaction; then
    • Request 3 raises an error, because the ET occurs while not in an explicit transaction; the implicit (multi-statement request) transaction is rolled back.
  • If error raised in request 2:
    • Request 1 is processed successfully, initiating an explicit transaction; then
    • Request 2 raises a data-related error; requests 1 and 2 are rolled back and the explicit transaction is ended; then
    • Request 3 raises an error, because the ET occurs while not in an explicit transaction; the implicit (multi-statement request) transaction is rolled back.
  • If error raised in request 3:
    • Request 1 is processed successfully, initiating an explicit transaction; then
    • Request 2 is processed successfully, continuing the explicit transaction; then
    • Request 3 raises a data-related error; requests 1, 2, and 3 are rolled back and the explicit transaction is ended.

Regardless of the retry setting, we will have inconsistent results:

  • For an error in the first request, rows (2,1,...), (2,2,...), and (2,3,...) will be inserted into the target table.
  • For an error in the second or third request, no rows will be inserted into the target table.

The same behavior occurs for retryable errors if retry is disabled.

However, if retry is enabled, and a retryable error (such as deadlock) occurs, the behavior is different:

  • If error raised in request 1:
    • Request 1 raises a retryable error; request 1 is rolled back and the explicit transaction is ended; then
    • Request 1 is resubmitted and is processed successfully, initiating an explicit transaction; then
    • Request 2 is processed successfully, continuing the explicit transaction; then
    • Request 3 is processed successfully, completing the explicit transaction.
  • If error raised in request 2:
    • Request 1 is processed successfully, initiating an explicit transaction; then
    • Request 2 raises a retryable error; requests 1 and 2 are rolled back and the explicit transaction is ended; then
    • Request 2 is resubmitted and is processed successfully as an implicit (multi-statement request) transaction; then
    • Request 3 raises an error, because the ET occurs while not in an explicit transaction; the implicit (multi-statement request) transaction is rolled back.
  • If error raised in request 3:
    • Request 1 is processed successfully, initiating an explicit transaction; then
    • Request 2 is processed successfully, continuing the explicit transaction; then
    • Request 3 raises a retryable error; requests 1, 2, and 3 are rolled back and the explicit transaction is ended; then
    • Request 3 is resubmitted and raises an error, because the ET occurs while not in an explicit transaction; the implicit (multi-statement request) transaction is rolled back.

We again have inconsistent (but different) results:

  • For a retryable error in the first request, with a successful resubmittal, all eight rows will be inserted into the target table.
  • For a retryable error in the second request, rows (2,1,...), (2,2,...), and (2,3,...) will be inserted into the target table.
  • For a retryable error in the third request, no rows will be inserted into the target table.

ANSI Mode

Now let's consider ANSI mode. Again, assume a transaction that spans three requests:

Request 1:

INSERT INTO <table> VALUES (1,1,...)
;INSERT INTO <table> VALUES (1,2,...);

Request 2:

INSERT INTO <table> VALUES (2,1,...)
;INSERT INTO <table> VALUES (2,2,...)
;INSERT INTO <table> VALUES (2,3,...);

Request 3:

INSERT INTO <table> VALUES (3,1,...)
;INSERT INTO <table> VALUES (3,2,...)
;COMMIT;

Consider what happens if, for instance, a data-related error (such as a range or uniqueness constraint violation) occurs in each of the following three scenarios:

  • If error raised in request 1:
    • Request 1 raises a data-related error; request 1 is rolled back and the transaction is ended; then
    • Request 2 is processed successfully, initiating a new transaction; then
    • Request 3 is processed successfully, completing the transaction.
  • If error raised in request 2:
    • Request 1 is processed successfully, initiating a new transaction; then
    • Request 2 raises a data-related error; request 2 (but not request 1) is rolled back, but the transaction initiated in request 1 remains open; then
    • Request 3 is processed successfully, completing the transaction.
  • If error raised in request 3:
    • Request 1 is processed successfully, initiating a new transaction; then
    • Request 2 is processed successfully, continuing the transaction; then
    • Request 3 raises a data-related error; request 3 (but not requests 1 and 2) is rolled back, but the transaction initiated in request 1 remains open (a subsequent COMMIT will complete the transaction).

Regardless of the retry setting, we will have inconsistent results:

  • For an error in the first request, rows (2,1,...), (2,2,...), (2,3,...), (3,1,...), and (3,2,...) will be inserted into the target table.
  • For an error in the second request, rows (1,1,...), (1,2,...), (3,1,...), and (3,2,...) will be inserted into the target table.
  • For an error in the third request, rows (1,1,...), (1,2,...), (2,1,...), (2,2,...), and (2,3,...) will be inserted into the target table (assuming a subsequent COMMIT completes the transaction)

The same behavior occurs for retryable errors if retry is disabled.

However, if retry is enabled, and a retryable error (such as deadlock) occurs, the behavior is different:

  • If error raised in request 1:
    • Request 1 raises a retryable error; request 1 is rolled back and the transaction is ended; then
    • Request 1 is resubmitted and is processed successfully, initiating a new transaction; then
    • Request 2 is processed successfully, continuing the transaction; then
    • Request 3 is processed successfully, completing the transaction.
  • If error raised in request 2:
    • Request 1 is processed successfully, initiating a new transaction; then
    • Request 2 raises a retryable error; request 2 (but not request 1) is rolled back, but the transaction initiated in request 1 remains open; then
    • Request 2 is resubmitted and is processed successfully, continuing the transaction; then
    • Request 3 is processed successfully, completing the transaction.
  • If error raised in request 3:
    • Request 1 is processed successfully, initiating a new transaction; then
    • Request 2 is processed successfully, continuing the transaction; then
    • Request 3 raises a retryable error; request 3 (but not requests 1 and 2) is rolled back, but the transaction initiated in request 1 and continued in request 2 remains open; then
    • Request 3 is resubmitted and is processed successfully, completing the transaction.

In this case we have consistent results; all eight rows will be inserted into the target table. So, if the only errors that occur are retryable, it appears as though ANSI mode is the answer. However, that presumes that we can guarantee the absence of non-retryable errors.

What are the Alternatives?

At first blush, it may appear that the simplest solution is to use ANSI session mode. However, it is nearly impossible to ensure that no non-retryable errors will occur while processing the SQL statements in a BTEQ script.

Another alternative, regardless of session mode, is to use only single-request transactions (i.e., multi-statement requests). Taking our earlier examples, the following Teradata (BTET) mode request will either be completely applied or not applied at all (note that the BT/ET are not necessary, since the multi-statement request is an implicit transaction):

INSERT INTO <table> VALUES (1,1,...)
;INSERT INTO <table> VALUES (1,2,...)
;INSERT INTO <table> VALUES (2,1,...)
;INSERT INTO <table> VALUES (2,2,...)
;INSERT INTO <table> VALUES (2,3,...)
;INSERT INTO <table> VALUES (3,1,...)
;INSERT INTO <table> VALUES (3,2,...);

Similarly, the following ANSI mode request will either be completly applied or not applied at all (the COMMIT is required):

INSERT INTO <table> VALUES (1,1,...)
;INSERT INTO <table> VALUES (1,2,...)
;INSERT INTO <table> VALUES (2,1,...)
;INSERT INTO <table> VALUES (2,2,...)
;INSERT INTO <table> VALUES (2,3,...)
;INSERT INTO <table> VALUES (3,1,...)
;INSERT INTO <table> VALUES (3,2,...)
;COMMIT;

Depending on the particular usage, the single-request transaction approach may not be feasible. In some cases, use of single-statement requests with error handling (IF ERRORCODE ... THEN or IF ERRORLEVEL ... THEN command) may provide sufficient flexibility.

More complex cases may require a programmatic solution rather than a BTEQ script. For example, a Java application using JDBC and incorporating application-specific error-handling may be necessary.

References

SQL Fundamentals (B035-1141); chapter 4 (SQL Data Handling) includes sections discussing requests, transactions, and session modes.

SQL Request and Transaction Processing (B035-1142); chapter 9 (Locking and Transaction Processing) includes sections discussing transactions, requests, statements, and session modes.

Basic Teradata Query Reference (B035-2414); chapter 5 (BTEQ Commands) includes sections discussing the IF ... THEN, SET RETRY, and SET SESSION TRANSACTION commands; chapter 3 (Using BTEQ) includes sections discussing testing and branching and handling errors; chapter 4 (BTEQ Output) includes a section discussing disabling the retry function.

Utilities: Volume 1 (A-K) (B035-1102); chapter 12 (DBS Control (dbscontrol)) includes a section discussing the system-level default session mode.

1 REPLY
s_1
Enthusiast

Re: Structuring Requests in BTEQ for Retryable (and Other) Errors

hi,

how to check Activitycount in runtime BTEQ Script based upon ACtivityCount we are loading / skiping the script how plz write the simila way ?

Ratnam