Tuesday, March 22, 2011

Zend Server for IBM iSeries platform does not support multiple persistent connection to DB2

_
_
Edit: This post's content may only apply to i5/OS V5R4 without latest PTF. Please refer to comments for detailed info.

Persistent connection in PHP has its advantage and disadvantage. According to my observation about creating DB2 connection on i5/OS, time used to set up connection via normal connection and persistent connection can be 50 ~ 100 times different. However, persistent connection has its disadvantage as well. Some php programmer even call it evil. Also, it might not be worthy to take risk from persistent connection if the access traffic is not so high.

In a brief way to describe, the causer for persistent connection has disadvantage is that PHP itself is a stateless script language and database connection is usually stateful. That is, php script's life time only exists from the request coming and request end. Meanwhile, database connection will hold some state like table lock, user defined variables etc. So, the problem will occur when a php script use a persistent connection as a fresh new connection and it is not. PHP persistent does not support a kind of "private" connection, which will be used by one user session only. This kind private connection implementation can be found in easyComm i5 toolkit.

Back to what I observed recently. I find that Zend Server (ver 5.0.4) for IBM i5/OS V5R4 does not supports having multiple persistent connection in one request to php scripts. Below is a simple testing code. When you run it, you can see that the output Current Schema is not ALWAYS as what you specified in db2 connection string. I guess the hash function used Zend Server for resource manager may has some problem. Or, DB2 extension has a bug if it does not intend to support only one persistent connection in one PHP script request.

Change the db2_pconnect to db2_connect to see different output. You need to run this script many times to see that you can not ALWAYS get the right schema as your current schema. Most likely, you need to take 5 minutes to 10 minutes break during the testing and see the wrong result to come out.


<?php 

$sysStr = 'select CURRENT SCHEMA from SYSIBM.SYSDUMMY1 ';

$params = array(
 'username' => 'accountNameOne', 
 'password' => 'accountPwdOne',
 'dbname' => '*LOCAL', 
 'driver_options' => array('i5_lib' => 'libraryOne')
);
$connOne = db2_pconnect($params['dbname'], $params['username'], 
$params['password'], $params['driver_options']);
$sysStmt = db2_prepare($connOne, $sysStr);
db2_execute($sysStmt);
$returnObj = db2_fetch_object($sysStmt);
var_dump($returnObj);

$params2 = array(
 'username' => 'accountNameTwo', 
 'password' => 'accountPwdTwo', 
 'dbname' => '*LOCAL', 
 'driver_options' => array('i5_lib' => 'libraryTwo')
);
$connTwo = db2_pconnect($params2['dbname'], $params2['username'], 
$params2['password'], $params2['driver_options']);
$sysStmt2 = db2_prepare($connTwo, $sysStr);
db2_execute($sysStmt2);
$returnObj2 = db2_fetch_object($sysStmt2);
var_dump($returnObj2);

3 comments:

  1. I ran your test above many times using browser refresh button as fast as i could press the button and it worked every time with correct output ...
    object(stdClass)#1 (1) { ["00001"]=> string(10) "LIBRARYONE" } object(stdClass)#2 (1) { ["00001"]=> string(10) "LIBRARYTWO" }
    ... perhaps try Zend Server 5.1 (latest).

    Also tried with Apache benchmark tool 'ab' to simulate 10 browsers for 10 seconds and was completely successful ...
    adc@cairns:~$ ab -t 10 -c 10 http://lp0164d/adc/twolibspconnect.php
    This is ApacheBench, Version 2.3 <$Revision: 655654 $>
    Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
    Licensed to The Apache Software Foundation, http://www.apache.org/
    Benchmarking lp0164d (be patient)
    Completed 5000 requests
    Finished 5356 requests
    Server Software: Apache
    Server Hostname: lp0164d
    Server Port: 80
    Document Path: /adc/twolibspconnect.php
    Document Length: 134 bytes
    Concurrency Level: 10
    Time taken for tests: 10.001 seconds
    Complete requests: 5356
    Failed requests: 0
    Write errors: 0
    Total transferred: 1558887 bytes
    HTML transferred: 717838 bytes
    Requests per second: 535.57 [#/sec] (mean)
    Time per request: 18.672 [ms] (mean)
    Time per request: 1.867 [ms] (mean, across all concurrent requests)
    Transfer rate: 152.23 [Kbytes/sec] received

    send me a note on how you got this to fail adc@us.ibm.com.

    ReplyDelete
  2. After further testing with senior engineer from IBM, we have brief conclusion as shown below,

    1) On i5 V5R4 box with PTF level from September 2010, we can reproduce the issue when we set the interval time around 5 minutes.

    2) on i5 V5R4 box with PTF level from November 2009, we can not reproduce the problem so far.

    The testing method is,

    1) create a php script with Curl client to call testing code as shown in the post via HTTP request.

    2) creating a shell script to call the curl client repeatedly. Between each loop, make around several minutes break. 5 minutes break is the magic number to see the problem on i5/OS without latest PTF installed.

    ReplyDelete
  3. Here are more related info, very helpful.

    Alan Seiden's presentation: http://www.alanseiden.com/presentation%20slides/DB2-and-PHP-best-practices-on-IBM-i.pdf

    Tony Cairns' article: http://www.youngiprofessionals.com/wiki/FastCGI

    ReplyDelete