|
|
|
Junior Member
 
Group: Forum Members
Last Login: 7/23/2008 6:10:10 AM
Posts: 10,
Visits: 39
|
|
Hi,
We came across a specific scenario in Teradata transaction processing.
As you might know we can use BT and ET in bteq section to control the rollback and commit process for multiple queries.
We tried two options for that
Putting ‘;’ at the end of each statement
BT;
DELETE STMT;
INSERT STMT;
ET;
Putting ‘;’ at the beginning of the next statement
BT
;DELETE STMT
;INSERT STMT
;ET;
In Ist option, if the DELETE statement fails and the INSERT statement ran successfully without errors; the INSERT transcation will not be rolled back.But in the other way when the DELETE statement ran successfully and INSERT statement fails, then commit transaction will not be done for the successful one.
And the IInd option the successful transaction will not be commited if the other one fails.
Why is it so? Could any one explain about this?
|
|
|
|
|
Supreme Being
Group: PAC and SFT Members
Last Login: Today @ 4:14:00 PM
Posts: 331,
Visits: 538
|
|
BTEQ does not recognize any special significance to BT/ET, it just treats them as SQL statements.
Putting ";" at the beginning of the next statement causes BTEQ to concatenate the SQL and send it to the database as a single multistatement request, which will be an implicit transaction. (So the BT/ET are redundant in this case.)
But in the first case, Teradata sent BT as the first request, then sent the DELETE as the second request. The DELETE failed and rolled back; which ended the explicit transaction. But you did not check for or act on that error so BTEQ proceeded to send the INSERT as a third request (now an implicit transaction in its own right). It should have then given you an error 3510 (Too many END TRANSACTION statements) on the ET, since there was no longer an active BT.
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 7/23/2008 6:10:10 AM
Posts: 10,
Visits: 39
|
|
|
Thanks Fred.This is really good information. So that means in option 1, we need to check the errorcode immediately after the DELETE statement...
|
|
|
|