SAP HANA: Workaround for using Commit & Rollback in exception handling block of a Stored Procedure
Hello Folks,
In this blog, i would like to share my experiences on using Commit & Rollback in exception handling block of a stored procedure in SAP HANA on versions < SP10.
Note: From SP10, Please find the details in the below link
COMMIT and ROLLBACK – SAP HANA SQLScript Reference – SAP Library
Firstly I want to thank Nelson my colleague who helped me in getting this work around. 🙂
Problem description:
If we are using a procedure to load data into any table and if the procedure encounters any error while loading data it may result in faulty data when we are reloading the same table.
To ensure consistency of the data we need to “Roll back” the previous transaction happened and not to commit the data into the table unless the procedure is completely successful.
In the below mentioned example, will be explaining on how to use “COMMIT” and “ROLLBACK” in exception handling block.
Let us take an example procedure, which does “insertion” of data into a table as shown below:
/* Creating a test table with column ‘EMPNO’ as Pk for our example */
CREATE TABLE EMPLOYEE_TEST (EMPNO INTEGER PRIMARY KEY)
/* Creating a Procedure for testing Predefined Exception Handling */
CREATE PROCEDURE EXCEPTION_HANDLING_TEST AS
BEGIN
/*Pre Defined exception handler */
DECLARE EXIT HANDLER FOR SQLEXCEPTION
SELECT ::SQL_ERROR_CODE AS “Error Code”, ::SQL_ERROR_MESSAGE AS “Error Message” FROM DUMMY; — To display error code and message
INSERT INTO EMPLOYEE_TEST VALUES (1);
INSERT INTO EMPLOYEE_TEST VALUES (1); — Again inserting 1 will result in unique violation error
END;
/*Calling the procedure*/
CALL EXCEPTION_HANDLING_TEST;
/*Output which shows the predefined error code along with the error message as shown below*/
We are able to handle the exception and show the error message but still the first insertion is completed and the table holds the data which is not desirable as shown below:
so we will use “ROLLBACK” to stop the insertion happening if the procedure encounters any error while loading as shown below:
/* Creating a Procedure for testing Predefined Exception Handling */
CREATE PROCEDURE EXCEPTION_HANDLING_TEST AS
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
INSERT INTO EMPLOYEE_TEST VALUES (1);
INSERT INTO EMPLOYEE_TEST VALUES (1); — Again inserting 1 will result in unique violation error
COMMIT;
END;
We get an compilation error “Feature not supported” as shown above while trying to use “ROLLBACK” in the exception handler block or using “COMMIT” in the procedure as shown below:
Let us try taking the “ROLLBACK” ,”COMMIT” into a variable as a work around and try to execute them and see if it works fine as shown below:
DROP PROCEDURE EXCEPTION_HANDLING_TEST;
/* Creating a Procedure for testing Predefined Exception Handling */
CREATE PROCEDURE EXCEPTION_HANDLING_TEST (OUT Status_Code INTEGER ) AS
BEGIN
DECLARE var_commit VARCHAR(100) := ‘COMMIT’;
DECLARE var_rollback VARCHAR(100) := ‘ROLLBACK’ ;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
EXEC (:var_rollback);
INSERT INTO “ERROR_LOGS” ( error_no,error_cd,error_msg ) VALUES ( <schemaname>.SNO.NEXTVAL,::SQL_ERROR_CODE,::SQL_ERROR_MESSAGE );
Status_Code := 1;
END;
INSERT INTO EMPLOYEE_TEST VALUES (1);
INSERT INTO EMPLOYEE_TEST VALUES (1); — Again inserting 1 will result in unique violation error
EXEC (:var_commit);
Status_Code := 0;
END;
The work around has worked we are able to compile the procedure with out any errors.
Now let us call the procedure and check if the inserts are getting rolled back or still a record is getting inserted.
CALL EXCEPTION_HANDLING_TEST(?);
Caller of the procedure can understand if there is any failure in the load by the value of Out_status as shown below: ( if 1 = Failure else Success) as shown below:
You can check the ERROR_LOGS table to see the error code and message:
SELECT * FROM EMPLOYEE_TEST;
Thus we are able to achieve the intended result.
Hoping this document has helped you in understanding on how to use “COMMIT” and “ROLLBACK” in “Exception Handling Block” in SAP HANA.
Please let me know your feedback on this.
Note: Please be informed that in the latest versions you need not use COMMIT or ROLLBACK in the procedure for consistency of your data it is handled implicitly. Also be informed that we came across side effects of redo log volume increasing due to some inconsistency caused while manually controlling commit. You may choose to use the above workaround if you need COMMIT/ROLLBACK for any-other purpose and also be mindful it is not supported by SAP.
Your’s
Krishna Tangudu 🙂
Hi Krishna ,
Excellent Post.
Useful Information.
Thanks Pratik 🙂
Wish you a Happy new year ahead/
Hi there,
sorry, but this is not actually very clever.
Now, your procedure either succeeds (ok) or rolls back.
In the later case, the caller of the procedure has no idea what happened.
Much better would be to simply pass the error upwards. Then the caller can rollback (or still commit if wanted) but the error reason is not hidden away.
- Lars
Hi Lars Breddemann,
We used this work around in a recent implementation we did for loading data into SAP HANA by shell scripts invoked from jobs.
The requirement was if any load fails there should not be a partial commit of records instead the job should fail and the error message has to be logged.
If my procedure rolls back, i can log the error message (which is materialized in a table) by the above approach and stop the inserts/deletes from getting committed using ROLLBACK. When the job calling this proc fails (it understands by output from the loading procedure) it will trigger an email with this error message information. And support team receives the error message. This is how it is currently implemented.
This approach which i have mentioned is only to stop the "incomplete" load into HANA if there are any errors while loading as per the requirement.
I was not able to understand if you meant storing the "ROLLBACK" into a variable and executing is not a correct/good way or the way i wrote the execution of ROLLBACK is incorrect.
Could you please guide me on this to understand and correct this approach?
Thanks for your valuable time in reading this blog.
Regards,
Krishna Tangudu
Hi Krishna,
yes you could write out the error code into a logging table and then try and find the logging output later on.
Still if someone calls the procedure it will always return OK - regardless of the error.
The problem here is that you place the error handling on a level too low.
The problem is not fixable at that level. Reporting "OK" as a return code is misleading.
Better raise the error and let the calling code/person figure out what to do next (retry, restart from scratch, fix values...).
Concerning the email triggering: there is no way in HANA to trigger emails based on transaction behavior. Yes, you could monitor your logging table, but that's after the fact and will eventually swamp the support team.
To me, all this reads as if existing Data Services features shall be re-implemented in SQLScript. And as much as I am no DS person I can tell: implementing such functionality (although it seems easy at first) will take a lot of time (= money).
Alright, there you go. That's my opinion on that 🙂
- Lars
Hi Lars Breddemann,
Thank you for your reply. ( I have been eagerly waiting for your reply to understand and correct my approach 🙂 ).
When the procedure is called and if there is a load failure, the procedure will return '1' and if the procedure call is successful it will return '0'. This is read and captured in shell scripts by writing into logs and this based on '0' or '1' in the log, the support team gets notified with this message. ( Shell scripts login to HANA by HDBSQL and in Auto commit mode OFF ).
I have edited the procedure above in the blog to explain you the same. Where you can see when we call the procedure it fails and returns '1' & logs the error code and message into a Table. Advice me if there are other side- effects because of this approach?
Yes i agree with you on this. Being a DS Consultant i proposed the same to the client but they were flexible with Time to Money ( Additional cost to buy BODS licenses 🙁 ).
The above blog gives a very high level overview of we had actually implemented. i wanted to ask one more question to you.
We were able to make this procedure generic but to form the column-list i.e
insert into <table> <column-list> ( Select <column-list> from table )
We are using cursors and using exec to execute the dynamically formed SQL.
Want to know your thoughts if there is any other way to make the procedure generic ( i.e user should be able to give any schema or table name to be loaded ) without the usage of cursors as we have done.
Awaiting your response.
Regards,
Krishna Tangudu
Hi Lars,
just a short follow-up question. Isn't there something like "DML Error Logging" in HANA?
In the past days i see a lot of procedure related HANA topics, which destroys my picture of independency of a database vendor. I may misunderstand the whole thing, but that was the "big flag" of SAP to change its underlying infrastructure without a notice as the business / transformation logic is happening on a higher layer.
If we would be able to use all of the database depended features of all vendors (like it is mostly done with HANA by SAP) it would be much faster in parts on all underlying platforms (thinking about Fast Refresh MVs, etc.).
Thanks.
Regards
Stefan
Hi Stefan,
in short: no, currently there is no functionality comparable to what your preferred DBMS offers.
Concerning vendor independence: it's not the same as being vendor agnostic.
Yes, SAP does primarily develop on HANA platform today and will continue to do so (at least that is what I personally understand - don't mistake this with an official company statement ok?).
However, we have to and we will continue to support the other platforms.
And the other platform vendors are free to provide their platform specific implementation of performance critical functions, like we do in HANA.
So in fact, it's getting better for the end-users now. Whatever platform is chosen, chances are that platform specific benefits are leveraged and used.
Does that mean that suddenly everything will be supported? No, surely not.
But just as SAP BW does have a long history of being very platform specific, this now extends to SAP NetWeaver in general.
- Lars
Hey Yogesh,
please don't double post questions. Thanks.
- Lars
Good Info - thanks
Rama Shankar
Thanks Rama 🙂
Hello Krishna,
It is a defect in HANA tool at this time, that it doesn't have capability to re-throw any exceptions to the clients (Called programs). They will need to fix this issue in later releases.
The approch that you mentioned above works great if called and calling programs are within HANA.
What if Java/.NET client invokes a stored procedure and returns a stauts code "1" (let's say there is an error inside the procedure), the client doesn't know how to read the error tables for actual error message.
Instead, if stored procedure logs the error message and re-throw the exception (error code and message) to the client, client will figure out how to handle these exceptoins. Client doesn't need to read another table for actual error messages.
All database systems in the world follow this approch except HANA.
(Including OpenSource MySQL)
Regards,
TK
Hi Thandava,
Thanks for your time in reading this blog and for your reply.
Have used this approach where the HANA procedure is called from HDBSQL which is getting invoked from the Shell Script as mentioned in the below document
SAP HANA: Replicating Data into SAP HANA using HDBSQL
And also used this approach to maintain data consistency in the generic procedure like this SAP HANA: Generic Procedure using Arrays to Load delta data into Tables
And then again reading the error table from hdbsql to throw the error to the support team. So at the point of implementation we approached this work around to complete it.
Regards,
Krishna Tangudu
Hi there
just to make this clear: HANA does support raising (signalling) errors and re-signalling them.
Krishnas workaround is about the unavailable transaction control commands in SQLScript.
There is no "defect" to be fixed here concerning the exception handling.
Also: for common SQL errors that would require a rollback (e.g. duplicate record) the rollback of the command is done automatically (just as it is in any other DBMS).
The manual handling of those things is only required if you want to do custom stuff instead or in addition (e.g. filling a logging table or rolling back a whole set of previously successfull inserts).
- Lars
Hi Lars,
Is there a way to easily roll back if an IMPORT fails?
Currently it is inserting the data after the erroneous row is encountered but I want to whole IMPORT to roll back all the data e.g. not have partial data.
Many thanks,
Sean
Nope, IMPORT will commit aber the BATCH size, no matter what.
Be very clear about that IMPORT is not a data transfer management tool.
It only pumps data into the database. That's it.
If you want to manage data flows, you need something more sophisticated like an ETL tool or some scripting like the one presented in this blog post.
- Lars
Thanks for sharing!!