teradata error handlers and datastage

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.

teradata error handlers and datastage

Hello,

I am launching a stored procedure from a Stored Procedure Stage in Datastage.

The procedure behaves differently from Teradata Studio and Datastage.
Put things simple, the procedure is:

 

 

    REPLACE PROCEDURE PR_DM_PROVA ( )
    BEGIN 

    DECLARE w INTEGER;

    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
    INSERT INTO MYLOGS ('ROW ERROR');
    END;

    INSERT INTO MYLOGS ('ROW BEFORE');

    select 1/0 INTO w;

    INSERT INTO MYLOGS ('ROW AFTER');

    END;



If I launch this procedure from Teradata Studio, I will get 3 rows in my MYLOGS table, and the command runs fine.

If I launch this procedure from Datastage, I will get only 2 rows in my MYLOGS table, namely the 'ROW ERROR' and 'ROW AFTER', and the command terminates with error: "3510 Too Many END TRANSACTION Statements".
So, it seems Datastage is doing some kind of rollback.

Has anyone idea of what's going on? What is Datasgtage doing?

Thank you.

 


Accepted Solutions
Teradata Employee

Re: teradata error handlers and datastage

Error handling works differently in Teradata transaction mode vs ANSI mode. Studio defaults to ANSI mode. DataStage appears to be using Teradata mode in your example. You need to change the Transaction Mode property for the DataStage connection to ANSI. Note that the stored procedure should / must be compiled with the same transaction mode as you use to call the procedure.

 

In ANSI mode error handling, only the statement with the error is rolled back and the transaction is maintained while the handler gets control. This is generally the most useful behavior when coding error handling logic.

 

When an error occurs in Teradata mode, the current transaction (in this case a BEGIN TRANSACTION sent by DataStage prior to the procedure call) is aborted and completely rolled back, and then the handler gets control. The two INSERTs you see were then run as stand-alone implicit transactions and committed automatically. And when DataStage sends an END TRANSACTION after the procedure call, that is also treated as an error because there is no longer an active transaction.

 

 

 

1 ACCEPTED SOLUTION
3 REPLIES
Teradata Employee

Re: teradata error handlers and datastage

Error handling works differently in Teradata transaction mode vs ANSI mode. Studio defaults to ANSI mode. DataStage appears to be using Teradata mode in your example. You need to change the Transaction Mode property for the DataStage connection to ANSI. Note that the stored procedure should / must be compiled with the same transaction mode as you use to call the procedure.

 

In ANSI mode error handling, only the statement with the error is rolled back and the transaction is maintained while the handler gets control. This is generally the most useful behavior when coding error handling logic.

 

When an error occurs in Teradata mode, the current transaction (in this case a BEGIN TRANSACTION sent by DataStage prior to the procedure call) is aborted and completely rolled back, and then the handler gets control. The two INSERTs you see were then run as stand-alone implicit transactions and committed automatically. And when DataStage sends an END TRANSACTION after the procedure call, that is also treated as an error because there is no longer an active transaction.

 

 

 

Re: teradata error handlers and datastage

Thank you @Fred. What you say sounds good. However I am pretty sure I am running Studio in Teradata mode, not ANSI. Moreover, if the procedure were compiled in ANSI (using Studio), and Datastage runs the procedure in Teradata mode, it should give an error, doesn't it?

 

Highlighted
Teradata Employee

Re: teradata error handlers and datastage

Yes, if the mode is different you should get a 5510 error - so I need to amend my explanation:

 

When run from Studio, the INSERTs and the SELECT are each being treated as individual implicit transactions and the INSERTs are automatically committed. When the failure occurs, the SELECT is rolled back (though that's not visible) and control is given to the handler.

 

But (as noted above) DataStage in Teradata mode issues an explicit BT (begin transaction) before the procedure call and an explicit ET (end transaction) afterwards, which is what causes the difference in behavior.