DB2 for i: commitment control and isolation level

The place for general PHP questions and hints for PHP on IBM i
Post Reply
vaa03
Posts: 20
Joined: Sun Apr 04, 2010 11:57 pm

DB2 for i: commitment control and isolation level

Post by vaa03 » Thu Jun 27, 2013 10:15 am

I need to change the isolation level of an application that uses commitment control.
I have already set up php.ini ibm_db2.i5_allow_commit flag and all my table are journalized.

Now, according to db2_connect/pconnect documentation i changed isolation level using connection options 'i5_commit' => DB2_I5_TXN_SERIALIZABLE but the connection function emits following warning message:
Warning: db2_connect() [function.db2-connect]: i5_commit (DB2_I5_TXN_NO_COMMIT, DB2_I5_TXN_READ_UNCOMMITTED, DB2_I5_TXN_READ_COMMITTED, DB2_I5_TXN_REPEATABLE_READ, DB2_I5_TXN_SERIALIZABLE) in ....

i tried to get numeric value of isolation level constant and using it instead of php constant but the message doesn't change.
Anyone can change isolation level correctly using connection options?

Using db2_set_option after connection i can adjust isolation level according my needs (.. i think!): db2_set_option function doesn't rise any warning but... how can i check which isolation level is currently set-up?
With db2_get_option i can't get 'i5_commit' value.

Who wants to share his experience?

THANK YOU

Mirco Vescovi

User avatar
clarkphp
Zend Global Services
Posts: 34
Joined: Sat Jun 30, 2012 9:59 pm
Location: United States
Contact:

Re: DB2 for i: commitment control and isolation level

Post by clarkphp » Wed Sep 11, 2013 6:17 am

This is months after the initial question was asked, but I wonder if 'autocommit'=>DB2_AUTOCOMMIT_OFF was - or was not - one of the options passed in the db2_connect() call. It should have been passed, if commitment control (transactions) were desired.

vaa03
Posts: 20
Joined: Sun Apr 04, 2010 11:57 pm

Re: DB2 for i: commitment control and isolation level

Post by vaa03 » Mon Sep 16, 2013 11:13 am

Yes, it was passed to db2_connect() call.
However warning message is still raised.

User avatar
clarkphp
Zend Global Services
Posts: 34
Joined: Sat Jun 30, 2012 9:59 pm
Location: United States
Contact:

Re: DB2 for i: commitment control and isolation level

Post by clarkphp » Tue Sep 24, 2013 1:41 am

Hello,

You want to know how to check "which isolation level is currently set-up" - if by that you mean you want to know the current state of autocommit, you can use this
http://www.php.net/manual/en/function.d ... commit.php
When db2_autocommit() receives only the connection parameter, it returns the current state of AUTOCOMMIT for the requested connection as an integer value. A value of 0 indicates that AUTOCOMMIT is off, while a value of 1 indicates that AUTOCOMMIT is on.
If you mean you want to know what the current i5_commit setting is, I will have to dig deeper.

I noticed in the documentation at http://www.php.net/manual/en/function.db2-connect.php it says that (emphasis below is mine):
The i5_commit attribute should be set before the db2_connect(). If the value is changed after the connection has been established, and the connection is to a remote data source, the change does not take effect until the next successful db2_connect() for the connection handle.
Note:
The php.ini setting ibm_db2.i5_allow_commit==0 or DB2_I5_TXN_NO_COMMIT is the default, but may be overridden with the i5_commit option.
In db2_set_option() http://www.php.net/manual/en/function.d ... option.php I see option for autocommit, but not for i5_commit. Are you doing something like

Code: Select all

db2_set_option($conn, array('i5_commit' => DB2_I5_TXN_SERIALIZABLE), $resource_type)
If that is what you are doing, then i5_commit being passed might be ignored. I have not had a chance to test this myself.

If you look closely here http://www.php.net/manual/en/function.d ... option.php it seems the valid options to inquire for do not include i5_commit setting.

Check the return value of db2_set_option() and db2_get_option(). Are either of them returning false?

I wish I had more answers than speculation for you at this time, but I will continue to look into this as time permits.

On a related note, have you had a chance to review "DB2 and PHP - Best Practices on IBM i" webinar on this page http://www.zend.com/en/resources/webinars/i5-os

I will follow up with you.
Clark

vaa03
Posts: 20
Joined: Sun Apr 04, 2010 11:57 pm

Re: DB2 for i: commitment control and isolation level

Post by vaa03 » Mon Sep 30, 2013 2:12 pm

While i was investigating why that warning was raised, i discovered that the problem was within my application, especially into Strategic_Db_Adapter_Db2 that i use to connect to IBMi.
That adapter isn't designed to receive i5_commit parameter starting from a Zend_Config_Ini file (at least, at the version that i'm using). In this case i'm using a Zend Framework 1 application and each connection is configured within application.ini using multidb resource.

Code: Select all

resources.multidb.ibmi.adapterNamespace 		= Strategic_Db_Adapter
resources.multidb.ibmi.adapter                                 = "Db2"
resources.multidb.ibmi.username 				= "****"
resources.multidb.ibmi.password 				="****"
resources.multidb.ibmi.dbname 			        = "*LOCAL"
resources.multidb.ibmi.os 						= "i5"
resources.multidb.ibmi.persistent 				= false
;resources.multidb.ibmi.driver_options.i5_commit     = DB2_I5_TXN_REPEATABLE_READ
resources.multidb.ibmi.driver_options.autocommit    = DB2_AUTOCOMMIT_OFF
resources.multidb.ibmi.driver_options.i5_naming      = DB2_I5_NAMING_ON
resources.multidb.ibmi.driver_options.i5_libl  	       = "HQ_SYS E_PORTALEP QGPL"
resources.multidb.ibmi.isDefaultTableAdapter 	       = true
The value i put within my application.ini (DB2_I5_TXN_REPEATABLE_READ) was converted implicity into a string value (i think that is the default behaviour of the function parse_ini_file) before it being passed to db2_connect function; db2_connect unleash a warning because doesn't recognize string value provided.

To correct this error i edited Strategic_Adapter_Class by adding these lines before of db2_connection function (at line 174)

Code: Select all

//Line 174 -------------------------------------------------------------------------------------------------------------------------------
        if ($this->_config['driver_options']['i5_commit']) {
            $this->_config['driver_options']['i5_commit'] = intval($this->_config['driver_options']['i5_commit'], 10);
        }
//End patch ------------------------------------------------------------------------------------------------------------------------------
About db2_set_option question,
i think that it can be used to change the isolation level in any point of the code only if the connection is to a local datasource (*LOCAL).
db2_set_option($conn, array('i5_commit'=>DB2_I5_TXN_REPEATABLE_READ, true); return true if a correct value is passed to i5_commit parameter.

Look at this test script

Code: Select all

$connection = db2_connect(
    '*LOCAL',
    $user,
    $pw,
    array(
        'i5_naming' => $namingMode,
        'i5_libl'	=> " QGPL",
        'autocommit'=> DB2_AUTOCOMMIT_OFF,
        'i5_commit' => DB2_I5_TXN_SERIALIZABLE
    )
);

$res = db2_set_option($connection, array('i5_commit'=>DB2_I5_TXN_REPEATABLE_READ), true);
var_dump($res); 
// print    ->  bool(true)

$res = db2_set_option($connection, array('i5_commit'=>"2"), true);
var_dump($res); 
// print    ->  Warning: db2_set_option(): i5_commit (DB2_I5_TXN_NO_COMMIT, DB2_I5_TXN_READ_UNCOMMITTED, DB2_I5_TXN_READ_COMMITTED, DB2_I5_TXN_REPEATABLE_READ, DB2_I5_TXN_SERIALIZABLE)
Still i have not found a way to get current isolation level setted because db2_get_option doesn't support 'i5_commit' parameter as input.

Code: Select all

$res = db2_get_option($connection, 'i5_commit');
var_dump($res); // print    ->  Warning: db2_get_option(): Incorrect option string passed in in /home/MVESCOVI/webapps/esc_portale/public/commitment/db2_get_option_test.php on line 43 bool(false)
Thank for sharing your knowledge and sorry for my mistake.

Mirco Vescovi

Post Reply