|
|
|
Supreme Being
Group: Forum Members
Last Login: 8/11/2008 3:00:36 PM
Posts: 76,
Visits: 147
|
|
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
-SN
|
|
|
|
|
Supreme Being
Group: PAC and SFT Members
Last Login: Yesterday @ 10:56:35 AM
Posts: 298,
Visits: 363
|
|
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.
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 7/23/2008 6:10:10 AM
Posts: 10,
Visits: 39
|
|
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?
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 8/11/2008 3:00:36 PM
Posts: 76,
Visits: 147
|
|
thx Fred ....thas answers my question!
-SN
|
|
|
|