Bteq BT/ET function behavior for INSERT statement

Tools & Utilities
Highlighted

Bteq BT/ET function behavior for INSERT statement

Hi Geeks,

 

As I encountered, if I am writing multiple INSERT satements inside BT/ET in bteq script, then if one of the middle statement fails then it is not rolling back successfully executed INSERT commands. But the principle of treating all statement as one transaction is working fine for UPDATE. Why?

Scenrio 1:

BT;

UPDATE statement1;

UPDATE statement2; -- Error in this code

UPDATE statement3;

ET;

Study: If 2nd update statement fails then all update statement is rolling back.

Scenrio 2: If we have a INSERT command in between

BT;

UPDATE statement1;

UPDATE statement2; -- Error in this code

INSERT statement;

UPDATE statement3;

ET;

Study:In this case INSERT command is successfuly committing to database.

Q: Why is it behaving differently for INSERT command.

 

Thanks in advance

Tags (1)

Accepted Solutions
Junior Contributor

Re: Bteq BT/ET function behavior for INSERT statement

Q1: There's an ET without BT, e.g. Statement 2 fails, the transaction is rolled back, but BTEQ proceeds to the ET.

 

Q2:

 

BT; ---first BT/ET transaction
Statement1;
.IF ERRORCODE <> 0 THEN GOTO section2
Statement2; ---error in this statement
.IF ERRORCODE <> 0 THEN GOTO section2
Statement3;
.IF ERRORCODE <> 0 THEN GOTO section2
ET;

.LABEL section2
BT; ---second BT/ET transaction
Statement4;
.IF ERRORCODE <> 0 THEN .GOTO SECTIONA
 
ET;

.LABEL SECTIONA;

When you need multi statement transactions I would prefer MultiStatement Requests (MSR) in BTEQ:

 

 

-- 1st MSR, no need for BT/ET or error checking, it's all or nothing
Statement1 ;Statement2 ;Statement3 ; -- 2nd MSR Statement4 ;Statement5 ;

This will also simplify the logic for a rollback due to a deadlock 2631 , when RETRY is on BTEQ sends the whole MSR.

 

 

But IMHO best way is coding your logic in a Stored Procedure :-)

 

1 ACCEPTED SOLUTION
3 REPLIES
Teradata Employee

Re: Bteq BT/ET function behavior for INSERT statement

If this is the complete script, I doubt it's "working fine for UPDATE".

 

BTEQ does not automatically jump to the ET when it receives an error, it just continues with the next request. You must check the status after each request with explicit .IF and skip ahead or exit. Or if you simply want to exit the script in all cases, you can set .MAXERROR value.

 

When an error occurs in Teradata mode, the transaction is automatically rolled back before returning the error to the client. At that point there is no longer an active transaction.

Any subsequent requests are executed as stand-alone transactions until another BT; is received (and the now "extra" ET; will generate a warning).

Re: Bteq BT/ET function behavior for INSERT statement

Hi Fred,

 

Thanks for replay.

I got your point. However I am still not clear about BT/ET.

 

1. What is meant by error  *** Failure 3510 Too many END TRANSACTION statements.

               How to get rid of it?

2. Suppose I have two BT/ET transaction. If in 1st BT/ET some error occure then it would have to exit the transaction and moved to next BT/ET transcation. How to achieve it.

Pseudo code:

 

BT; ---first BT/ET transaction

Statement1;

.IF ERRORCODE <> 0 THEN ?????

Statement2; ---error in this statement

.IF ERRORCODE <> 0 THEN ?????

Statement3;

.IF ERRORCODE <> 0 THEN ?????

ET;

 

BT; ---second BT/ET transaction

Statement4;

.IF ERRORCODE <> 0 THEN .GOTO SECTIONA

 

ET;

 

If error happened in Statement2 then my first BT/ET transaction should end, but it continues with second BT/ET transaction )

Junior Contributor

Re: Bteq BT/ET function behavior for INSERT statement

Q1: There's an ET without BT, e.g. Statement 2 fails, the transaction is rolled back, but BTEQ proceeds to the ET.

 

Q2:

 

BT; ---first BT/ET transaction
Statement1;
.IF ERRORCODE <> 0 THEN GOTO section2
Statement2; ---error in this statement
.IF ERRORCODE <> 0 THEN GOTO section2
Statement3;
.IF ERRORCODE <> 0 THEN GOTO section2
ET;

.LABEL section2
BT; ---second BT/ET transaction
Statement4;
.IF ERRORCODE <> 0 THEN .GOTO SECTIONA
 
ET;

.LABEL SECTIONA;

When you need multi statement transactions I would prefer MultiStatement Requests (MSR) in BTEQ:

 

 

-- 1st MSR, no need for BT/ET or error checking, it's all or nothing
Statement1 ;Statement2 ;Statement3 ; -- 2nd MSR Statement4 ;Statement5 ;

This will also simplify the logic for a rollback due to a deadlock 2631 , when RETRY is on BTEQ sends the whole MSR.

 

 

But IMHO best way is coding your logic in a Stored Procedure :-)