BT/ET

UDA
SN
Enthusiast

BT/ET

hi,

this is regarding the BT/ET feature, in reference to the post 'Transaction' on 05/19 and Fred's response (good info!)

I am just trying to understand.....if coding as

BT;
stmt 1;
stmt 2;
stmt 3;
ET;

doesnt really make it as a one single explicit transaction (meaning stmt one is not rolled back if stmt 2 fails) and
coding as

BT
;stmt 1
;stmt 2
;stmt3
;ET;
makes it as one implicit transaction (even without BT & ET which is true)
----> what is the use of the explicit BT/ET feature?

I referred TD manual..but is sounds like both format revert all sql stmts in case of failure.........any thoughts??

thx
7 REPLIES
Teradata Employee

Re: BT/ET

In either form:
If stmt 3 fails, all three (stmt 3, stmt 2, stmt 1) get rolled back.
If stmt 2 fails, both stmt 2 and stmt 1 get rolled back. So far, so good.

But a ROLLBACK implicitly ends the transaction. It's up to the application to check for errors after each request and react accordingly. In the "single request with multiple statements" case, the DBMS knows to skip stmt 3 after an error on stmt 2. But in the "multiple requests with one statement each" case, the database has no way to know that a new request containing stmt 3 was intended to be part of the same transaction; the application was notified of the error, so a new request is assumed to be an intentional request to start a new transaction.

This is standard behavior for processing a unit of work made up of multiple requests; it's not unique to BT/ET semantics (also applies to ANSI COMMIT semantics), to the use of BTEQ, or even to the Teradata DBMS.

Why would you choose to implement a transaction as multiple requests versus one multi-statement request? Not all databases or tools support multi-statement requests. And some transactions may include conditional processing requirements, retrieving the results or status from one statement to determine what to do next.
Enthusiast

Re: BT/ET

Some more information on multistatements......ie,

BT
;stmt1
;stmt2
;ET;

In Teradata these are referred as multistatement inserts.It seems multistatement insert is a feature in teradata that allows for optimum performance on insert operations... this works ONLY on empty tables. Even if we are doing multiple INSERT operations (no delete,update) on a NON-empty table, it will be as good as running the INSERTs separately.there's no good on running multistatement if it's not multiple inserts to an empty table.

Any comments?
SN
Enthusiast

Re: BT/ET

thx Fred ....thas answers my question!

Enthusiast

Re: BT/ET

to continue this topic .. i have a question.

Is there any difference in these 2 scenarios in terms of the the way teradata processes them?

1. using BT ET and contains 2 DML requests.
2. same 2 DML requests coded as multi-statement-requests?

Thanks in advance.
Enthusiast

Re: BT/ET

If everything is going OK, there is no difference.
But if they are run in bteq, and there is a restart or deadlock during statement 2 (case 1), statement 1 is rolled back and statement 2 is resubmitted. (Unless RETRY OFF is in force.)
The two requests as a MSR is OK; if there is a restart or deadlock, both are rolled back and restarted.
Enthusiast

Re: BT/ET

Hi, While submitting request as BT;stmt 1;stmt 2;stmt 3;ET; in Teradata SQL ASSISTANT and Unix has different behaiour, in SQL ASSISTANT after failure of one statement transaction rollsback but in UNIX it is taking all three statements as individual statemets (why not as one transaction). Someone help. thx

Teradata Employee

Re: BT/ET

Not sure what you mean by "in Unix". Do you mean "in BTEQ?"

By default SQL Assistant stops executing SQL when an error is encountered.

In BTEQ you must explicitly check - using .IF after each statement and/or .SET MAXERROR - to exit the script on failure.

Or use "leading semicolon" syntax to have BTEQ submit a multi-statement request:

stmt1

;stmt2

;stmt3;