Facing problem with "BEGIN NOT ATOMIC"

[Anupam]

Hi,

I have a stored procedure as follows:

CREATE PROCEDURE TEST_PROC2 ( )
LANGUAGE SQL
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN NOT ATOMIC

INSERT INTO
TEST_TABLE
(
NAME,
NO
)
VALUES
(
'PROC TWO',
2
);

--COMMIT;
-- Simulate an error condition here.
SIGNAL SQLSTATE '75002' SET MESSAGE_TEXT = 'Error in Proc 2';
END P1

Now as I understand, the NOT ATOMIC clause in the BEGIN means that if
an error occurs within the compound statement, then the entire
compound is not rolled back. However, in this case, when the SIGNAL is
raised, the INSERT is being rolled back. The only way to commit the
INSERT before the SIGNAL is raised is to include the COMMIT statement.
IS this how NOT ATOMIC is supposed to work? I am getting confused
here.

Regards
Anupam

[Gustavo Arocena]

Your understanding of BEGIN NOT ATMIC is correct. Maybe it's your
application (or the caller) the one that is rolling back the actions in the
procedure when it returns an sqlcode different from 00000.
-Gustavo