All Forums UDA
SN 77 posts Joined 01/07
21 May 2008

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) andcoding 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

Fred 1096 posts Joined 08/04
22 May 2008

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.

ashija 10 posts Joined 01/07
22 May 2008

Some more information on,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 77 posts Joined 01/07
22 May 2008

thx Fred ....thas answers my question!

teradata_techie 13 posts Joined 03/10
08 Jul 2010

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.

Jimm 298 posts Joined 09/07
08 Jul 2010

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.

abhishank18 12 posts Joined 03/16
10 Aug 2016

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


Fred 1096 posts Joined 08/04
15 Aug 2016

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:

You must sign in to leave a comment.