ET stmt with no BT in BTEQ; failed with error

Tools & Utilities
Enthusiast

ET stmt with no BT in BTEQ; failed with error

Hello, In a BTEQ script we have an ET statement by mistake without a BT and BTEQ failed with below error when it encountered ET. I have a question, there are several SQLs before this ET and all of them completed successfully. My question is will all the data from those SQLs completed be commited to the database (including the previuos SQL before the ET)? or because of this error would some SQL which was commited would have got rolled back from Database? can someone kindly answer this? Thanks!

 

*** Failure 3510 Too many END TRANSACTION statements.

 

3 REPLIES
Teradata Employee

Re: ET stmt with no BT in BTEQ; failed with error

Data already committed is not rolled back. Only the "extra" ET itself fails.

Enthusiast

Re: ET stmt with no BT in BTEQ; failed with error

Thank very much. one more question- had this been with a BT before the ET statement and bunch of SQLs and if some SQL fails, in that case, data committed to DB will get rolled back, is it correct? Thanks again!!!!

Teradata Employee

Re: ET stmt with no BT in BTEQ; failed with error

In that case, the failing SQL and any other statements in the transaction (back to and including the BT) would be rolled back, precisely because they have NOT been committed.  And the rollback will also end the transaction

Note that in BTEQ you should always SET RETRY OFF and use MAXERROR and/or explicitly check for errors with IF statement after each database request whenever you use BT/ET with transactions that contain multiple requests.