Transaction Units across Multiple

Database
Teradata Employee

Transaction Units across Multiple

Commitment units are owned by a session - correct? As such, if I have a BTEQ script and want to execute several CALLS to SPL procs, can I wrap the calls with a BT/ET so that all the coalls are part of the explicit transaction? I would expect to but can't find explicit documentation (at least not yet;-)
2 REPLIES
N/A

Re: Transaction Units across Multiple

You can wrap BT/ ET around procedure calls as long as the procedures follow the rules. IE you cannot have DDL within a transaction unless the DDL comes immediately before the ET. So if any of the procedures contain the DDL, you cannot execute them within a BT/ ET block. The last procedure can have DDL only if there is no DML between the DDL and the ET.
It could give you maintainance problems too. If you run a number of procedures, they often give out little information about what they are doing. If there is a problem well into the script, debugging the problem can be a nightmare if you have just rolled back everything the proc calls did. I usually prefer to prepare a work table with the script and finally inseert/ delete/ update the work table to main. (Unless you really need a proc for looping capability!) Its much easier to debug.
If you do use BT/ ET in bteq - always set RETRY OFF in case of deadlocks or restarts.
Teradata Employee

Re: Transaction Units across Multiple

Thanks for the reply Jimm - much appreciated. I suspected it was that way and just wanted to confirm. However, I did not think about 'RETRY OFF in the BTEQ script. Thanks!