Re: db2, php, AUTOCOMMIT off rollback not working

Sheldon Foster <mailto:sheldon@xxxxxxxxxxx>
March 7, 2013 6:04 PM
Thanks for your replies Bryan, Chuck, and Alan.
==
Bryan:
I am using "connect". I have as yet to try "pconnect".
==
Alan:
In ibm_db2.ini I have the following line: ibm_db2.i5_allow_commit=1. I
notice it is not in the php.ini file. Does it need to be there as well?
==
Chuck:
I will try the "with UR" approach when I get a chance and let you know if it
helped.
==
On another note I tried interactive SQL on the iSeries to try the rollback
there and discovered a couple things.
1. The file was not being journaled. (probably overlooked because of how
painfully obvious that should have been?)
2. Even with the file being journaled, on the iSeries in interactive SQL, I
had to use the sql command "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ"
in order for the rollback to work but at least it worked - perhaps using the
"with UR" will accomplish the same thing?

I'll keep trying . . .

----------------------------------------------------------------------
date: Tue, 5 Mar 2013 20:32:20 -0500
from: Bdietz400<bdietz400@xxxxxxxxx>
subject: Re: [WEB400] db2, php, AUTOCOMMIT off rollback not working

Maybe just a wild guess, and not 100% it would make a difference. Are you
using "connect" or "pconnect"?

--
Bryan


On Mar 5, 2013, at 12:38 PM, "Sheldon Foster"<sheldon@xxxxxxxxxxx> 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).

For example, if I start out with 2 records, here is the result I get:

Original record count: 2

AUTOCOMMIT is off.

Record count after delete: 0

Record count after rollback: 0



$db = "*LOCAL";

$options = array ('i5_naming' => DB2_I5_NAMING_ON, 'autocommit' =>
DB2_AUTOCOMMIT_OFF );

if ($conn) {

$stmt = db2_exec($conn, "SELECT count(*) FROM teltrn");

$res = db2_fetch_array( $stmt );

echo "Original record count: " . $res[0] . "\n";



// Turn AUTOCOMMIT off

//db2_autocommit($conn, DB2_AUTOCOMMIT_OFF);

$ac = db2_autocommit($conn);

if ($ac == 0) {

print "AUTOCOMMIT is off.";

} else {

print "AUTOCOMMIT is on.";

}

// Delete all rows from teltrn

db2_exec($conn, "DELETE FROM teltrn");



$stmt = db2_exec($conn, "SELECT count(*) FROM teltrn");

$res = db2_fetch_array( $stmt );

echo "Record count after delete: " . $res[0] . "\n";



// Roll back the DELETE statement

db2_rollback( $conn );



$stmt = db2_exec( $conn, "SELECT count(*) FROM teltrn" );

$res = db2_fetch_array( $stmt );

echo "Record count after rollback: " . $res[0] . "\n";

}

--
This is the Web Enabling the IBM i (AS/400 and iSeries) (WEB400)
mailing list To post a message email: WEB400@xxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/web400
or email: WEB400-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/web400.

------------------------------
date: Wed, 06 Mar 2013 08:51:41 -0800
from: CRPence<CRPbottle@xxxxxxxxx>
subject: Re: [WEB400] 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");

--
Regards, Chuck


------------------------------
date: Wed, 06 Mar 2013 16:22:09 -0500
from: Alan Seiden<alan@xxxxxxxxxxxxxx>
subject: Re: [WEB400] db2, php, AUTOCOMMIT off rollback not working

Have you turned on commitment control at the global PHP level using
ibm_db2.i5_allow_commit=1?

See these slides, particularly pages 37-42.
http://www.slideshare.net/aseiden/db2-and-php-best-practices-on-ibm-i

Alan
--
*Alan Seiden Consulting LLC*
PHP for IBM i expertise and advocacy

Project leader, Zend PHP Toolkit for IBM i Zend Framework certified
contributor

Office: 201-447-2437 Cell: 201-248-4704
http://www.alanseiden.com | alan@xxxxxxxxxxxxxx

http://twitter.com/alanseiden