We're currently running PHP 5.3 on linux, and we're connecting to DB2 on the iSeries version 6.1.

We want to able to use commitment control in our PHP code. I've successfully been able to rollback an insert statement executed within the php code, however, when I try putting that same insert statement into a stored procedure and call the procedure from PHP, it won't rollback the changes.

Here's some sample code that will rollback the insert:

$options = array ('autocommit' => DB2_AUTOCOMMIT_OFF);
$conn = db2_connect (...)

$sql = "insert into some_table values('test')";
$stmt = db2_prepare($conn, $sql);
$result = db2_execute($stmt);
db2_rollback($conn);
db2_close($conn);

However, when I change the $sql variable above to call a procedure using:

$stmt = "call test_procedure ()", with test_procedure having the following definition, it won't rollback:

CREATE PROCEDURE test_procedure()
LANGUAGE SQL
MODIFIES SQL DATA
COMMIT = *NONE
BEGIN
INSERT INTO some_table values ('test');
END ;


I have read the following commit behaviour, but I'm not sure how to set the autocommit off for the stored procedure.

If I explicitly set COMMIT = *CS (or any setting other setting than *None), the procedure will rollback. However, I don't know if this is the right approach. I would think the procedure should inherit the isolation level from the client.

I’ve read some other similar threads, but haven’t seen any solution.
Any ideas?

Thanks

It might be worth it to check System i manuals at SQL CALL statement (stored procedures)

Thanks for the link. Basically what I got out of that was to ensure the stored procedure runs in the same activation group. I verified that my procedure is already using *Caller for the activation group.