I’m pretty sure the PHP extension IBM_DB2 is overriding my commitment control isolation level on Zend Server 5.6, PHP Extension IBM_DB2 1.9.2.  In my PHP I’m running through 10,000 insert statements and then I’m doing a SQL SELECT on that table as a different user in a JDBC connection.  I’m getting dirty reads (getting the inserted rows before the commit) even though i have DB2_I5_TXN_SERIALIZABLE set in my db2_connect.

I’ve looked at the source and I think this is the issue.  On line 1403 and 1410 the IBM_DB2 extension sets the SQL_ATTR_COMMIT to whats passed into the db2_connect option i5_commit (which would be better named as commitment control isolation level).  On 2165 its being overwritten to no commit isolation level if c_i5_allow_commit is non-zero (its set to 1 to allow commits), and rc is non-zero (RC looks to be the result of setting SQL_ATTR_AUTOCOMMIT via SQLSetConnectAttr(), which would be turned off as you dont want to commit the changes until you have all your records done).

CLICK image for full screen

PHP extension uses whats passed into db2_connect

 

does ibm_db2 always uses nocommit2

PHP Extension later on in the code is overriding those options!!!  There’s no way to change it unless you turn i5_allow_commit off (this turns off commitment control and defeats the purpose), or turn off autocommit ( that won’t work either as I want to commit at the end of all my changes).

does ibm_db2 always uses nocommit

Try it yourself

<?php
error_reporting(E_ALL & ~E_NOTICE);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
for ($index = 0; $index < 10000; $index++)
{
$insertdata[] = array('test');
}
$options = array('i5_commit' => DB2_I5_TXN_SERIALIZABLE,'autocommit' => DB2_AUTOCOMMIT_OFF);
$db2Connection = db2_connect('', '', '', $options);
if (!$db2Connection)
{
echo "false – Connection failed.";exit();
}
$sql = "DELETE FROM MYLIB.MYTABLE";
$stmt = db2_exec($db2Connection, $sql);
$sql = "INSERT INTO MYLIB.MYTABLE (MYCOL1) VALUES(?)";
$insertstmt = db2_prepare($db2Connection, $sql);
foreach($insertdata as $row)
{
try
{
$result = db2_execute($insertstmt, $row);
}
catch (Exception $exc)
{
$result=false;
}
if($result==false)
{
echo db2_stmt_errormsg();
echo "Dump of Array being inserted:<br>"; var_dump($row);
}
}
//THIS WORKS – Commitment Control works, Isolation Levels do not (stopping others from reading the data as your modifying the table)
$commitResult = db2_rollback($db2Connection);
var_dump($commitResult);

Notes on IBM_DB2 php extension: DB2_I5_TXN_SERIALIZABLE is the same as SQL_TXN_SERIALIZABLE because of #define DB2_I5_TXN_SERIALIZABLE SQL_TXN_SERIALIZABLE. SQL_DEFAULT_TXN_ISOLATION is sent into SQLGetInfo() along with a bitmask. SQL_DEFAULT_TXN_ISOLATION is set in odbc and not in IBM_DB2. In this odbc.c file (which i dont know what obdc.c IBM is using) SQL_DEFAULT_TXN_ISOLATION is set to 26 (http://www.ncbi.nlm.nih.gov/IEB/ToolBox/CPP_DOC/lxr/source/include/dbapi/driver/odbc/unix_odbc/sql.h#L411). I believe the bitmask gets set to the default isolation and then that bitmask is compared using the bitwise AND operator (&) with the various isolation options. If they match UR,CS,RS,or RR is added to array

#ifdef PASE /* i5/OS ISOLATION_OPTION */

rc = SQLGetInfo(conn_res->hdbc, SQL_DEFAULT_TXN_ISOLATION, &bitmask, sizeof(bitmask), NULL);

#else

if( bitmask & SQL_TXN_READ_UNCOMMITTED ) {
add_index_stringl(array, key++, “UR”, 2, 1);
}
add_property_zval(return_value, “ISOLATION_OPTION”, array);