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:

ibm_db2.i5_allow_commit=1

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.

<?PHP
//Add commitment control to the DB2 Connect by using i5_commit
//Apparently you can't set this using ini_set. Therefore this would have to be set in the PHP.ini
//ini_set('ibm_db2.i5_allow_commit',1);
ini_set('ibm_db2.autocommit',DB2_AUTOCOMMIT_OFF);
$options = array('i5_lib' => 'MYLIB','autocommit' => DB2_AUTOCOMMIT_OFF,'i5_commit' => DB2_I5_TXN_SERIALIZABLE);
//Connect to DB2 – Commitments wont take place until we call
//db2_commit($db2Connection) on the connection.
$db2Connection = db2_connect('', '', '', $options);
if (!$db2Connection) {die("false – Connection failed.");}
//Run queries that change the table
$sql = "INSERT INTO MYTABLE (COl1) VALUES(?)";
$insertstmt = db2_prepare($db2Connection, $sql);
$dataArray = array('1','2','3');
foreach ($dataArray as $value)
{
$result = db2_execute($insertstmt, $value);
//Note: below we use die which would stop any records from being inserted
//alternatively could just echo out the error and let the other record go through.
if($result==false) {die(db2_stmt_errormsg());}
}
//The 3 records that were created will now be pushed live
$commitResult = db2_commit($db2Connection);
if(!$commitResult) {die("Commit failed");}
?>

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

Debugging

This will give you everything you want to know about your db2 connection to the IBM i

<?php
$server = db2_server_info( $db2Connection );
var_dump($server);exit();
//DFT_ISOLATION and ISOLATION_OPTION should be looked at
?>

Notes:

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.

Links

IBM_DB2 Runtime Config

Function DB2_Connect

XMLSERVICE

IBM_DB2 PHP extension source