DDL statements in stored procedure is rolled back in case procedure failure

[former_member841238]

Hello everyone,

I have a scenario where I am truncating the column table (HDBTABLE) inside the stored procedure as shown below. There is no explicit COMMIT/ROLLBACK statement used in the stored procedure.

I believe TRUNCATE statement being DDL, will be auto committed and it cannot be rolled back.

However, if the procedure is failed due to any reason at the end, after TRUNCATE statement is executed, all the transaction is getting rolled back including TRUNCATE statement. I was expecting the table to be empty even if the procedure is failed.

PROCEDURE SP1

BEGIN

--Truncating the table TAB1

TRUNCATE TABLE TAB1; -- Line 4

--Failing the procedure forcefully

SELECT 1/0 FROM DUMMY;--Line 6

END;

In the above example, once the procedure is failed at line 6, TRUNCATE statement at line 4 must have been committed and table should have been empty after the procedure failure.

Please help me in understanding why TRUNCATE statement is getting rolled back and advice if I am missing anything here.

Thanks in advance.

[junwu]

why you believe it will auto commit??

[former_member841238]

Because that's the behavior observed when we execute DDL statements directly in SQL console (Not in procedure).

[CharlesFeng]

Hi vijayvj6,

When creating a procedure, there is an option "AUTOCOMMIT DDL", the default value is "OFF".

----------------------------------------------
AUTOCOMMIT DDL { ON | OFF }

Specifies whether to automatically commit DDL statements in the procedure body. Specify ON when automatic commit of DDL statements is required; for example, during administrative operations such as importing data.

This clause is only supported for SQLScript procedures that are not read-only. The default value is OFF.
----------------------------------------------

Reference: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/20d467407519101484f190f...

And, "SQLScript transactions normally run in AUTOCOMMIT DDL OFF, so an implicit commit is not performed for TRUNCATE TABLE."

Reference: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/20fe29f0751910149904f0c...

So, could you please set "AUTOCOMMIT DDL ON" when creating the procedure and then test it again?

Best regards,
Charles