Re: db2, php, AUTOCOMMIT off rollback not working

On 05 Mar 2013 09:38, Sheldon Foster wrote:

I have the following code to test rollback transaction processing. I
get a count of records from a table, then delete all the records,
then perform a roll back which should (supposedly) restore the
records back to the table. I am able to confirm that the
db2_autocommit_off is indeed getting set, but the rollback function
never rolls back the prior deletion. I can't seem to find much on the
internet. What am I missing? (fyi, $telusr and $telpwd are being set
correctly but I have not included the code here).

<<SNIP>>
// Delete all rows from teltrn

db2_exec($conn, "DELETE FROM teltrn");
<<SNIP>>

I am not sure about the interface being used, but in some cases there is the /isolation level/ that must also be set to something other than NC [aka NONE or No Commit]; i.e. the auto-commit feature does not also define the commitment-control\isolation-level to be used. A SQL DELETE statement without the WITH-isolation-level clause will use the default isolation. Thus a circumvention [pending resolution to establishing the desired isolation-level for the connection, in addition to having established the no-autocommit], is to specify something like WITH UR on the request to explicitly request the isolation to use on the DML statement; e.g.:
db2_exec($conn, "DELETE FROM teltrn WITH UR");