questions about db2_pconnect and QSQSRVR job

General discussion on Zend Core for IBM System i

questions about db2_pconnect and QSQSRVR job

Postby yiyujia on Mon Jan 17, 2011 10:05 pm

My php scripts run on IBM i5 OS R5V4 with PHP Version 5.3.1, ZF version 1.10.1 and Zend server 5.0.1. The DB2 is running on same machine. I have some questions about DB2 connection.

1) I did not see much configuration parameters for db2_pconnect . The URL i checked is this, http://www.php.net/manual/en/ibm-db2.configuration.php . For example, I do not see a parameter like timeout. I am expecting that I can get timeout error if the database excuting takes longer than the timeout setting.

2) What will happen to QSQSRVR job if PHP script hit its time out and quit. Will Zend Server kill the QSQSRVR job and release all resources?

3)In case of db2_pconnect, how will Zend resource registry handle the timeout issue? Timeout issue means PHP script hit the runtime timeout setting before DB2 send back any response. According to the source code found from PECL(http://svn.php.net/viewvc/pecl/ibm_db2/), it seems that a persistent connection is managed in a hash table. DB2 SQL server mode applies to CLI calling. Since db2 extension uses CLI to communicate with DB2, it should generate QSQSRVR jobs. As I understand, DB2 SQL server mode will reuse QSQSRVR if same user name and password is used for creating connection too. So, what is the relationship between Zend server resource management and QSQSRVR resource management?

4) Finally, has anybody here successfully use db2_pconnect in a product enviroment?
yiyujia
 
Posts: 59
Joined: Tue May 18, 2010 3:02 pm

Re: questions about db2_pconnect and QSQSRVR job

Postby aseiden on Tue Jan 18, 2011 2:10 am

Hi, Yiyu,

I can answer #4.

I've used db2_pconnect in a production environment. It worked.

However: I eventually stopped using persistent connections. I discovered that after a table was accessed under a persistent connection, it retained a "shared read" lock. The only way I found to remove the lock was to restart the web server. "Shared read" locks are usually harmless but they can interfere with commands such as CLRPFM (clear physical file member).

If a timeout parameter existed then I would consider using persistent connections again.

Alan
aseiden
 
Posts: 870
Joined: Thu Apr 09, 2009 5:45 pm

Re: questions about db2_pconnect and QSQSRVR job

Postby yiyujia on Tue Jan 18, 2011 3:36 pm

Hi Alan,

Thanks a lot for sharing your experience with us! I thought table or row lock would be released once the SQL query is committed or Cursor is released. But, according to your description, pconnet has problem with this. I dont know what happen in your system. But, it is possible that db2 extension does not handle the transaction and resource properly. In fact, I saw there are special code for handling commit in the db2 extension. Here is its source code link, http://www.php.net/manual/en/ibm-db2.configuration.php .

So, according to your description, I will think that you failure on using db2 persistent connection in your product environment. Let's expecting Zend engineers will give us some insightful info about db2_pconnect, zend resource register and QSQSRVR job.

thanks and regars,

Yiyu Jia
yiyujia
 
Posts: 59
Joined: Tue May 18, 2010 3:02 pm

Re: questions about db2_pconnect and QSQSRVR job

Postby yiyujia on Tue Jan 18, 2011 8:47 pm

The following three posts could be related to this post,

viewtopic.php?f=64&t=4259
viewtopic.php?f=77&t=7589
viewtopic.php?f=64&t=4674

After reading the above posts, I made a simple diagram to describe my question. I wish somebody will give me a description on it. Here is the diagram pic,
db2ConnectHangs.PNG
A simple diagram describing possible db2 persistent connection problem in zend server for i5 OS
db2ConnectHangs.PNG (20.29 KiB) Viewed 12859 times

Note: this post assume zend server and DB2 are running on same machine.

Thanks and regards,

Yiyu Jia
yiyujia
 
Posts: 59
Joined: Tue May 18, 2010 3:02 pm

Re: questions about db2_pconnect and QSQSRVR job

Postby rangercairns on Thu Jan 27, 2011 11:00 pm

Alan's note correction CLRPFM ... db2_commit/db2_rollback/db2_free_result probably needed.

When using ibm_db2 persistent connections a php script writer needs to remember to use db2_commit/db2_rollback/db2_free_result, which "close cursors" allowing "many" operator actions. Try the following PHP script and comments on/off to see effect ...

Code: Select all
<?php
$conn=db2_pconnect("*LOCAL","DB2","NICE2DB2", array('i5_lib'=>'DB2'));

$stmt=db2_exec($conn,"drop table ZONEY");
$rc=db2_free_stmt($stmt);

$stmt=db2_exec($conn,"CREATE TABLE ZONEY (FLD1 NUMERIC (12,2) NOT NULL WITH DEFAULT)");
$rc=db2_free_stmt($stmt);

for ($i=1;$i<10;$i++) {
  $stmt=db2_exec($conn,"INSERT INTO ZONEY (FLD1) VALUES($i.$i$i)");
  $rc=db2_free_stmt($stmt);
}

$stmt=db2_exec($conn,"SELECT * FROM ZONEY");
echo "\n<br>select before CLRPFM ".db2_stmt_error()." ".db2_stmt_errormsg();
while($row=db2_fetch_array($stmt)) {
  echo "\n<br>";
  var_dump($row);
}

// Close cursor associated with Select to allow CLRPFM
// db2_commit($conn);
// db2_rollback($conn);
// ... or free result will work ...
// db2_free_result ($stmt);
// ... this will not work ...
db2_free_stmt ($stmt);

$sql = 'CLRPFM FILE(DB2/ZONEY)';
$len = strlen($sql);
$stmt=db2_exec($conn,"CALL QSYS2.QCMDEXC('$sql',$len)");
echo "\n<br>$sql ".db2_stmt_error()." ".db2_stmt_errormsg();

$stmt=db2_exec($conn,"SELECT * FROM ZONEY");
echo "\n<br>select after CLRPFM ".db2_stmt_error()." ".db2_stmt_errormsg();
while($row=db2_fetch_array($stmt)) {
  echo "\n<br>";
  var_dump($row);
}
db2_free_result ($stmt);
?>


Note 1:
Browser calling any script multiple times doing selects you see read locks WRKOBJLCK (2nd time+, db2 ini setting) ...
QSQSRVR QUSER *SHRRD HELD *JOB
... however, IBM i recognizes "pseudo close" state and operator/operation will be able to cancel this lock and complete an operation like CLRPFM (See: http://wiki.midrange.com/index.php/SQL# ... t_18874457 ).

Note 2:
To force pseudo close to hard close from CL try ALCOBJ OBJ((QCUSTCDT *FILE *EXCL)) CONFLICT(*RQSRLS)

whew!
Last edited by rangercairns on Mon Apr 25, 2011 8:01 pm, edited 15 times in total.
rangercairns
 
Posts: 222
Joined: Fri Jul 24, 2009 6:28 pm

Re: questions about db2_pconnect and QSQSRVR job

Postby rangercairns on Thu Jan 27, 2011 11:38 pm

On the topic of operator "time out" of persistent connections ... we may need something "more" in this space for IBM i operator-like operations ... maybe Alan will volunteer for "guinea pig" duty.

The original ibm_db2 code base was designed to run essentially 2-tier(ish) over DB2 Connect (PC<-to/from->IBM i among others) ... often handled by TCP/IP ping "are you there?" ... however running on IBM i, the code is not really expecting "time outs", and perhaps just does not have enough administrative settings/knobs to keep all operator(ish) functions related to restart/suspend/kill happy. I will look into this general area and see if something is possible in the code base.

last ...

Philosophically, ibm_db2 on IBM i wants you to use persistent connections (db2_pconnect), because persistent connections provide your web users a much better browser experience selecting/updating DB2 tables by avoiding open/close (db2_connect). You will have to get the db2_commit's in the correct places using persistent connections (or autocommit), but this seems a normal activity for any application web or not.

BTW -- I sympathize if porting mysql applications to DB2 persistent connection drives you crazy, because nearly all mysql applications rely completely on full/open close to get the transaction states correct. Converting to DB2 persistent connections is a really big part of fixing a "too slow" PHP application that started in mysql (sorry).
Last edited by rangercairns on Mon Apr 25, 2011 6:48 pm, edited 1 time in total.
rangercairns
 
Posts: 222
Joined: Fri Jul 24, 2009 6:28 pm

Re: questions about db2_pconnect and QSQSRVR job

Postby rangercairns on Fri Jan 28, 2011 12:36 am

Alan and Tony's ibm_db2.ini operator tricks for ibm_db2 ...

There are some interesting "operator" settings Zend Server ibm_db2.ini file that can greatly change ibm_db2 behavior running on IBM i.

Code: Select all
call qp2term
> cat /usr/local/zendsvr/etc/conf.d/ibm_db2.ini
extension=ibm_db2.so
ibm_db2.i5_allow_commit=1
ibm_db2.i5_all_pconnect=0
ibm_db2.i5_ignore_userid=0


Alan's favorite trick ...
1) ibm_db2.i5_ignore_userid=1 - forces ALL db2_(p)connect(x,x,x) to db2_(p)connect(null,null,null) without any changes to your PHP script.
> Good
This setting avoids QSQSRVR jobs and calls DB2 directly from inside the php-cgi process, which greatly speeds up full open close of db2_connect (non-persistent connections) ... -- BUT --- ... there is a 'catch' (no free lunch).
> Bad(ish)
The 'catch' is that only the active Apache/Fastcgi profile will be used for all DB2 actions (usually qtmhhttp, lowly *USER without sign-on capabilities), which may be just fine for many folks not interested in "precision" DB2 auditing (and so on), BUT you will likely have to GRTOBJAUT qtmhhttp to all manner of things on your system to get a "bigger" PHP applications to work.
> REALLY Bad(ish)
Please understand that the setting that causes QSQSRVR jobs to be used is DB2 environment level scope (not DB2 connect level, and not DB2 statement level, see SQL_ATTR_SERVER_MODE), therefore if ANY other non-ibm_db2 PHP script running in the process "manages" to use a Db2 connection that switches user profile (or simulates switch profile), then the entire process will switch to using "server mode" and start acquiring a QSQSRVR job for each profile used, including db2_(p)connect("","",""). For example: QSQSRVR occurs if you use both PHP odbc/pdo_ibm scripts along with ibm_db2 scripts on the site ... there are other variations even calling things, but you get the idea (i hope).
> Good
This setting helps greatly with applications ported to DB2 originating from MySql, because the MySql applications almost always depend on full open/close to get commit boundaries correct (see my previous post).

Tony/Ranger's favorite trick ...
2) ibm_db2.i5_all_pconnect=1 - forces ALL db2_connect(x,x,x) to db2_pconnect(x,x,x) without any changes to your PHP script.
>Good
Typically for a database-related PHP application, you get an instant 30-40% performance improvement in your PHP application.
>Bad
If not using autocommit=on, and original author of the PHP script does not have the db2_commit's in the correct places (usually missing entirely), all the transactions ROLLBACK (warned you). Note: on ibm_db2 version 1.9+ a rollback occurs on script end, 1.8- occurs on Apache end.
>Good
You can see how fast a bad db2_connet(x,x,x) application can run without bothering the PHP programmer ... well at least until you can rub his/her nose in the performance difference with a live demo (i got tired of arguing with programmers that said it ain't so).

3) ibm_db2.i5_allow_commit=1 - allow commit to occur in ibm_db2 applications.
> Good
You probably already turned commit on from the Zend Server console, but if not you probably need it (especially if you are playing with LOBs).
> Bad
Use to be shipped by Zend as off by default (maybe still is), because we were worried that all those legacy CRTLIB libraries that are not commit capable would drive 1st time users crazy when nothing would work.
> Good
Most IBM i people recognize the value of "create schema mylib", which comes with all the commit/journal trimmings for modern professional databases.
> Bad(ish)/Good(ish)
This setting off sometimes helps in porting MySql PHP applications to DB2 ... for a while until you cab get the db2_commit's in the right places for persistent connections (hint).

Whew!
Last edited by rangercairns on Mon Apr 25, 2011 6:11 pm, edited 1 time in total.
rangercairns
 
Posts: 222
Joined: Fri Jul 24, 2009 6:28 pm

Re: questions about db2_pconnect and QSQSRVR job

Postby rangercairns on Fri Jan 28, 2011 1:02 am

The Zend guys poked me with an email to look at this forum area today ... so there are things not looked into yet ...

1) i wrote down your "bad library / not found" may "hang" Apache/FastCGI/php-cgi assert from the picture ... mmm ... i doubt this happens, but i have been wrong MANY times before (i will try).

2) Also, I don't remember "hanging" any resources Zend/Db2 because Apache/PHP timed out during an DB2 operation ... but i will see if i can simulate.

... to make things much easier ...

Do you have simple PHP examples/tests of this misbehaving ibm_db2 (1 and/or 2), it is always tough to guess what any given PHP test may do?
rangercairns
 
Posts: 222
Joined: Fri Jul 24, 2009 6:28 pm

Re: questions about db2_pconnect and QSQSRVR job

Postby aseiden on Mon Jan 31, 2011 7:37 am

Tony,

Thanks for the db2_commit() demonstration. I am intrigued by its ability to "sort of" release locks on tables. Normally in PHP I use autocommit. Does db2_commit() have super-powers that autocommit does not?

Alan
aseiden
 
Posts: 870
Joined: Thu Apr 09, 2009 5:45 pm

Re: questions about db2_pconnect and QSQSRVR job

Postby yiyujia on Mon Jan 31, 2011 3:26 pm

Hi rangercairns and Alan,

Thanks you for such detailed replying message.

About the sample code which will hangs Apache server, it is difficult to have that because the stored procedure is created by another team, which uses RPG as stored procedure programming language. But, the basic situation is similar as this post drescribed, viewtopic.php?f=64&t=4674 . I saw a MSGW status of QSQSRVR job when my php code call a RPG stored procedure, which failure to find a extra library needed by this stored procedure. The worse thing is that our apache server stops response any further request if this happens. I even can not restart apache server through zend command menu when this happen. But, apache server comes back when we manually killed the job under MSGW status. I guess there is some kind dead lock happens in the system. Probably we can avoid it by doing some system configuration. But, I do not know how. Or, Zend can handle it by using something like timeout check.

The above MSGW issue is not about db2_connect or db2_pconnect. I asked question about db2_pconnect here because 1) I want to use persistent connection in my product environment. I will feel very uncomfortable if I can not use persistent connection in a product environment for enterprise application. I saw there are some code in db2 extension to specially handle persistent connection and auto commit for persistent connection in PASE platform. I decided to ask if anybody has been able to use persistent connection in their product environment. I want to be a pioneer on this. But, i do not want to be a victim also. 2) Since the MSGW status issue happens. I am afraid that using pconnect will make thing more complicate than using normal db2_connect and db2_close functions because Zend takes more control over db2_pconnect() than it does on db2_connect. And, it happens in a black box where we can not have a look.

Thanks and regards,

Yiyu Jia
yiyujia
 
Posts: 59
Joined: Tue May 18, 2010 3:02 pm

Next

Return to Zend Core for i5/OS

Who is online

Users browsing this forum: No registered users and 1 guest

cron