UPDATE: I’m currently experiencing issues with commitment control isolation level not working…. It appears to be a problem with a PHP Extension. This should issue should be fixed in newer versions of ZS and ibm_db2. My issues occurred on Zend Server 5.6, PHP Extension IBM_DB2 1.9.2
I was wondering if there was a way to lock a file from being read while i modified the table using PHP and db2_* functions. This came up because I wanted to modify a table but make sure the users of my application didn’t see a partial list of the data while the data was being inserted via SQL.
The answer is yes there is a way and its very simple! You use commitment control. By default commitment control is set to Autocommit which means the change applies after you do db2_execute. Here’s a simple example to show you how to change commitment control and make sure the changes don’t get applied until your ready.
Apparently yo have to change your commitment control in php.ini (/usr/local/zendsvr/etc/php.ini)
add this line:
alternatively you can put it in /usr/local/zendsvr/etc/conf.d/ibm_db2.ini which I think is the “best practice” but can be annoying to find since you usually look in php.ini first.
Here’s the options for commitment control from php.net . Depending on your circumstance you may want to allow the user to read the data while your inserting the new records and they give you various php numeric constant options below.
DB2_I5_TXN_NO_COMMIT – Commitment control is not used. Actual value 1
DB2_I5_TXN_READ_UNCOMMITTED – Dirty reads, nonrepeatable reads, and phantoms are possible. Actual value 2
DB2_I5_TXN_READ_COMMITTED – Dirty reads are not possible. Nonrepeatable reads, and phantoms are possible. Actual value 3
DB2_I5_TXN_REPEATABLE_READ – Dirty reads and nonrepeatable reads are not possible. Phantoms are possible. Actual value 4
DB2_I5_TXN_SERIALIZABLE – Transactions are serializable. Dirty reads, non-repeatable reads, and phantoms are not possible. Actual value 5
You’ll also have to make sure the Physical File is journaled http://www.ibm.com/developerworks/data/library/techarticle/0305milligan/0305milligan.html
This will give you everything you want to know about your db2 connection to the IBM i
The commitment control only works when PHP is on the IBMi – https://bugs.php.net/bug.php?id=60363
Don’t use this with Persistent connections as you could be rolling back/ committing data from someone sharing the connection.
I was not able to do ini_set(‘ibm_db2.i5_allow_commit’,1);
It came back with false. So either all your apps have to allow commit or not.
IBM_DB2 Runtime Config
IBM_DB2 PHP extension source