MQSeries.net :: View topic - Multiple SQL Stored Procs being called in Compute Node

EvolutionQuest · Posted: Fri Sep 19, 2003 10:24 am Post subject:

I have a question concerning calling two or more SQL Stored Procedure calls within one compute node in relation to commits.

I have created a message flow that will call up-to 4 DB2 7.x Stored Procedures which will do inserts, updates, or deletes. Depending on each call it will continue to the next stored procedure call or throw a user execption. I have the Transaction Mode set to Automatic, which from the documentation sounds like a commit would not occur until the end of the message flow. The problem is even when the first stored procedure does a successful insert and the second fails an automatic rollback does not occur. The failure could be from the return code from the stored procedure, or an exception thrown by the compute node.

Even if the stored procedures are split into 4 different compute nodes the problem seems to still be an issue.

Any ideas, experiences, or advice?

Example of SQL Stored Procedure:

DECLARE hello CHARACTER;
DECLARE goodbye CHARACTER;
DECLARE returncode INTEGER;
DECLARE message CHARACTER;

CALL MyStoredProc ( hello, goodbye, returncode, message);

-- If returncode is not equal to zero throw execption and let it rollback
IF (returncode <> 0) THEN
THROW USER EXCEPTION CATALOG 'MyMessage' MESSAGE returncode VALUES (message);
END IF;

CREATE PROCEDURE MyStoredProc ( IN hello CHARACTER, OUT goodbye CHARACTER, OUT returncode INTEGER, OUT message CHARACTER) EXTERNAL NAME "MySchema.MyStoredProc" ;

CALL MyStoredProc1 ( hello, goodbye, returncode, message);

-- If returncode is not equal to zero throw execption and let it all rollback
IF (returncode <> 0) THEN
THROW USER EXCEPTION CATALOG 'MyMessage1' MESSAGE returncode VALUES (message);
END IF;

CREATE PROCEDURE MyStoredProc1 ( IN hello CHARACTER, OUT goodbye CHARACTER, OUT returncode INTEGER, OUT message CHARACTER) EXTERNAL NAME "MySchema.MyStoredProc1" ;
_________________
Don't blame technology, for technology was created by humans!