db2_connect & db2_pconnect problem

General discussion on Zend Server for IBM System i

db2_connect & db2_pconnect problem

Postby ahaithcox on Mon Feb 27, 2012 6:12 pm

Hey guys. I've got a strange problem that I can't seem to figure out. We use db2_connect and db2_pconnect in our php applications on two different iSeries servers (v5r4 & v7r1) running Zend Server CE 5.0.4 & 5.1.0 respectively. The problem we're seeing is the lose (db2_(p)connect returns false) of the connection resource on a irregular basis. Here's a sample of our db2PConnect.php (db2Connect.php is identical except for the function name):

Code: Select all
<?php

$host = "*LOCAL";
$user = "username";
$pass = "password";

$options = array('i5_naming' => DB2_I5_NAMING_ON);

$conn = db2_pconnect( $host, $user, $pass, $options);

if (db2_conn_error()) {
   echo "Oops...Error connecting with db2PConnect.php";
   echo "<br>";
        echo var_dump($conn);
        echo "<br>";
   echo "Connection failed. SQL Err: ";
   echo db2_conn_error ();
   echo "<br>";
   echo db2_conn_errormsg ();
        echo "<br>Status: ". connection_status().".";
   
   exit ();
}

?>


This configuration is currently working. I expect in a day or two of testing (or sometimes even a few hours), the connection will no longer work. To fix it, I can simply change the connection parameters to something like leaving the host, user, and password blank. Or, changing the host to blank, or the actual IP of the server, or dns name. I have actually seen it start working by changing double quoted string values to single quotes! I just can't seem to pin down a real pattern.

Has anyone experienced this problem? Allen, you seem to be the foremost expert on these things, any ideas?

Thanks in advance for the help,

Adam.
ahaithcox
 
Posts: 43
Joined: Mon Oct 03, 2011 5:22 pm

Re: db2_connect & db2_pconnect problem

Postby ahaithcox on Tue Feb 28, 2012 2:16 pm

Sorry for the name misspelling Alan. Apparently, I though you developed some handy little wrench :)
ahaithcox
 
Posts: 43
Joined: Mon Oct 03, 2011 5:22 pm

Re: db2_connect & db2_pconnect problem

Postby aseiden on Tue Feb 28, 2012 7:20 pm

Do the connection failures happen on pconnect, connect, or both?

Do you pass in any other parameters, such as library lists?

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

Re: db2_connect & db2_pconnect problem

Postby aseiden on Tue Feb 28, 2012 7:21 pm

Do you see any errors or problems in the joblogs? (QSQSRVR jobs in QSYSWRK)
aseiden
 
Posts: 804
Joined: Thu Apr 09, 2009 5:45 pm

Re: db2_connect & db2_pconnect problem

Postby ahaithcox on Wed Feb 29, 2012 5:30 pm

Thanks for the reply Alan.

The problem seems to be mainly on pconnect and no, we do not specify libraries during connect. We use the DB2_I5_NAMING_ON option and only specify the file name to utilize the library list. I am experiencing the problem this morning and trying to get the best info I can for you Alan. I changed the username and password to fix the problem this morning with pconnect. I checked that the previous username was still enabled, and it was. In fact if I change the function to connect with the same user, it works. I am going to leave the function as _connect for now and test that for a while to see if the problem is with both functions.
I couldn't find any joblogs for the QSQSRVR jobs in QSYSWRK. I did, however, find some errors in the ZENDSVR job. Below is the error that I find when the connection fails:

Job:
Code: Select all
ZENDSVR      QTMHHTTP    BCI      .0  PGM-php-cgi.bi   TIMW

Display Job Log:
Code: Select all
Connection to relational database S1044BCP does not exist.
Error occurred in SQL Call Level Interface

F1 on first job log:
Code: Select all
Message ID . . . . . . :   SQL0843       Severity . . . . . . . :   30       
Message type . . . . . :   Diagnostic                                         
Date sent  . . . . . . :   02/29/12      Time sent  . . . . . . :   10:00:27 
                                                                             
Message . . . . :   Connection to relational database S1044BCP does not exist.
Cause . . . . . :   A SET CONNECTION, RELEASE, or DISCONNECT statement or a   
  call to the SQLESETI API specified relational database name S1044BCP which 
  is not active.                                                             
Recovery  . . . :   Specify the name of a relational database which has an   
  active connection.                                                         

And the following job log:
Code: Select all
Message ID . . . . . . :   SQ99999       Severity . . . . . . . :   30       
Message type . . . . . :   Diagnostic                                       
Date sent  . . . . . . :   02/29/12      Time sent  . . . . . . :   11:21:17
                                                                             
Message . . . . :   Error occurred in SQL Call Level Interface               
Cause . . . . . :   A procedure call encountered an error.  The error code is
  17. Error codes are:                                                       
    3 -- Program type out of range.                                         
    4 -- SQL data type out of range.                                         
    9 -- Argument value not valid.                                           
    10 -- Function sequence error.                                           
    12 -- Transaction operation state not valid.                             
    13 -- Memory management problem.                                         
    14 -- Maximum number of handles allocated.                               
    15 -- No cursor name available.                                         
    16 -- Handle type for operation not valid.       
    17 -- Connection no longer available.                                     
    21 -- Descriptor or descriptor information not valid.                     
Recovery  . . . :   Refer to the DB2 for i5/OS SQL Call Level Interface (ODBC)
  topic collection in the Database category in the IBM i Information Center   
  book for a complete description of the error. Specifically, look at the     
  procedure that sent the error.                 


I'm sorry I can't find any more useful diagnostics. If you can maybe point me in another direction that may lead to the culprit, that'd be great. I really have to get this problem fixed. We are within weeks of deploying our latest product that uses these connections, and I'll be up the creek if it's not fixed.

Thanks so much for your help.
ahaithcox
 
Posts: 43
Joined: Mon Oct 03, 2011 5:22 pm

Re: db2_connect & db2_pconnect problem

Postby ahaithcox on Wed Feb 29, 2012 5:46 pm

Additional info:
Also had the problem with i5_connect and i5_pconnect this morning. Changing the hostname from "*LOCAL" to "" fixed the problem for both i5_connect and i5_pconnect. I tried changing the username as I did with db2_pconnect and it still failed. ??????????
ahaithcox
 
Posts: 43
Joined: Mon Oct 03, 2011 5:22 pm

Re: db2_connect & db2_pconnect problem

Postby aseiden on Wed Feb 29, 2012 9:58 pm

For i5_connect and i5_pconnect, the hosts '' (empty string) or 'localhost' or '127.0.0.1' are correct, not '*LOCAL'. The '*LOCAL' value is for database (db2_ ) calls, not i5 toolkit (or CW) calls.
aseiden
 
Posts: 804
Joined: Thu Apr 09, 2009 5:45 pm

Re: db2_connect & db2_pconnect problem

Postby ahaithcox on Wed Feb 29, 2012 10:05 pm

Ok. Well that explains the i5 connects not taking the *LOCAL parameter. The db2_connect seems to be holding so far. No failures since this morning. I'll keep you updated on the results. In the meantime, if you've got any ideas of other ways to diagnose the pconnect problem, I'd sure appreciate it.

Thanks again,
Adam
ahaithcox
 
Posts: 43
Joined: Mon Oct 03, 2011 5:22 pm

Re: db2_connect & db2_pconnect problem

Postby aseiden on Thu Mar 01, 2012 6:49 pm

Adam,

I think some of your database jobs (QSQSRVR jobs) are dying, and therefore can't be accessed when Apache looks for them. Are you using stored procedures or doing anything unusual that could cause some of these jobs to end abnormally?

Out of curiosity, do you experience this problem on both machines (old and new) equally?

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

Re: db2_connect & db2_pconnect problem

Postby ahaithcox on Fri Mar 02, 2012 4:57 pm

No, no stored procedures on the php side. We may be using stored procedures in some of our core business logic with RPG and/or COBOL. Are you concerned about that side of it, or just the php/Zend side? All of our Zend use is for retreiving live data via sql with db2_pconnect and _connect. The only thing we use i5_connect for is retrieving user spooled files and some i5_command statements.

The problem did seem to become more frequent once we started developing on the newer Power7 box. Although, I went to double check that the v5r4 was working correctly this morning and, in fact, i5_connect and _pconnect aren't working. The db2_connects on the same box work fine. I've tried everything I can to get them to connect with no luck including restarting the Zend server twice. I can't find any QSQSRVR jobs under the user defined in the connects. I wish I could find some kind of definitive message somewhere that would tell me what's going on. The only descent message I can see is from i5_error() :

Code: Select all
Oops...Error connecting with i5PConnect.php
Connection failed.
Error Number: 32
Error Category: 6
Error Message: TCP/IP error 32
Error Description: There is no process to read data written to a pipe. on 127.0.0.1:6079


"There is no process to read data written to a pipe" ?? Does that mean anything to you?

This is the code that produced it:
Code: Select all
<?php

$host = "";
$user = "zendphpusr";
$pass = "l0gin.p@ss";

$options = array('i5_naming' => DB2_I5_NAMING_ON);

$conn = i5_connect( $host, $user, $pass, $options);
//$conn = i5_connect( $host, $user, $pass, $options);

if (! is_resource($conn)) {
        $arr = i5_error();
   
   echo 'Oops...Error connecting with i5PConnect.php</br>';
   echo 'Connection failed.</br>';
        echo 'Error Number:  ' . i5_errno() . '</br>';
        echo 'Error Category:  ' . $arr['cat']. '</br>';
        echo 'Error Message:  ' . i5_errormsg() . '</br>';
        echo 'Error Description:  ' . $arr['desc'] . '</br>';
   
   exit ();
}

?>
ahaithcox
 
Posts: 43
Joined: Mon Oct 03, 2011 5:22 pm

Next

Return to Zend Server for IBM i

Who is online

Users browsing this forum: No registered users and 2 guests