You likely need a newer version of ibm_db2 (1.9.9-zs4).
http://yips.idevcloud.com/wiki/index.php/XMLSERVICE/PHP
{ibm_db2-1.9.9-zs4-php56.zip} - PHP ibm_db2.so (1.9.9-zs4 binary - php 5.6)

bash-4.3$ cat connect_close_pconnect.php 
<?php
$username = "ADC";
$password = "xxxx";
echo("=== version ===\n");
echo("required version = 1.9.9-zs4\nactual version (this) = ".phpversion('ibm_db2')."\n");
// Verbindungsaufbau
echo("=== db2_connect ===\n");
$conn_option = array('i5_naming' => DB2_I5_NAMING_ON);
$connection = db2_connect("*LOCAL", $username, $password, $conn_option);
echo("db2_connect = ".$connection."\n");
echo("db2_conn_errormsg = ".db2_conn_errormsg()."\n");
$rc = db2_close($connection);
echo("db2_close = ".$rc."\n");
echo("db2_conn_errormsg = ".db2_conn_errormsg()."\n");
// Fehlermeldungen
if (db2_conn_errormsg()) echo("test - I am NOT working ok!\n");
else echo("test - I am working ok!\n");
if ($connection) echo("actual - I am working ok!\n");
else die("actual - I am NOT working ok!\n");
echo("=== db2_pconnect ===\n");
$connection = db2_pconnect("*LOCAL", $username, $password, $conn_option);
echo("db2_pconnect = ".$connection."\n");
echo("db2_conn_errormsg = ".db2_conn_errormsg()."\n");
// Fehlermeldungen
if (db2_conn_errormsg()) echo("test - I am NOT working ok!\n");
else echo("test - I am working ok!\n");
if ($connection) echo("I am working ok!\n");
else die("I am NOT working ok!\n");
?>
bash-4.3$ php --version                  
PHP 5.6.10 (cli) (built: Jul 12 2015 16:05:00) 
Copyright (c) 1997-2015 The PHP Group
Zend Engine v2.6.0, Copyright (c) 1998-2015 Zend Technologies
bash-4.3$ 
bash-4.3$ php connect_close_pconnect.php 
=== version ===
required version = 1.9.9-zs4
actual version (this) = 1.9.9-zs4
=== db2_connect ===
db2_connect = Resource id #4
db2_conn_errormsg = 
db2_close = 1
db2_conn_errormsg = 
test - I am working ok!
actual - I am working ok!
=== db2_pconnect ===
db2_pconnect = Resource id #5
db2_conn_errormsg = 
test - I am working ok!
I am working ok!

Hi, 
i have tested it with the new 1.9.9-zs4 source, but the problem still remains. 

When i close the connection with db2_pclose, i recieve an error when using db2_pconnect again.

The strange thing is: after the db2_pconnect, there is no error message from db2_conn_errormsg and also no error message from db2_stmt_errormsg after a db2_prepare or db2_execute. 

But the db2_fetch_assoc has no result and ends with an error.

Hey ... you are wandering, perhaps seeing bunnies in passing clouds ...

You are NOW describing db2_fetch_assoc error 'has no result and ends with an error'. I am not at all sure why you see a fetch problem as connect/close. I am not sure these are even related (seeing bunnies in passing clouds) ...

I suggest try my php script and post the results of 'it works' or 'NOT works' so we might improve the focus of this narrative (aka, you are wandering).

IF 'my test' (not yours), appears 'it works' on your machine/installation, then we possibly can take some action to attempt to understand your query/fetch issue. To that end, i would recommend using a different query beyond your data set. That is, perhaps try some database shipped from IBM and/or something simple. Also, perhaps snoop around the proxy DB2 QSQSRVR job to see if joblog information is available.

BTW -- bad programming in script ...

$query = "SELECT * from ieffect/modcfg"
$params = array();
$prepQuery = @db2_prepare($connection, $query);
$result = @db2_execute($prepQuery, $params);

There are NO parameter markers (?) in the query. 

For example: 
$query = "SELECT * from ieffect/modcfg where bunnies=?"
$params = array('clouds');
$prepQuery = @db2_prepare($connection, $query);
$result = @db2_execute($prepQuery, $params);

Another suggestion: I have a PASE side CLI trace module. The thing is easy to use (i think). Maybe would shed some light on the root issue. Please see the link below.

http://yips.idevcloud.com/wiki/index.php/PASE/Service#cli

BTW -- you can just take the defaults after installing libdb400.a CLI driver. There will be a trace file in /tmp (see the link).

Hey, 

sorry but your sarcasm does not fix the problem. OK, i have not fully described the problem in my first post, but if you had tested it as i described it in my example you would see what i mean. 

Your example is "working", but: 
Please try to make any database request after it... (free choice, with or without parameters).
When you do that and compare it to a the older 1.9.7 version you will see that there is a problem related to the previous db2_pclose in all never versions.

You can also contact Shlomo Vanunu from Zend (i suppose you know him as i saw his name in your email). He can confirm that this is a problem with the changes in db2_pconnect/db2_pclose of 1.9.9.

I will try to install the trace module. Thank you.

Well ... still seems to work ok for me ... I do not have your data set, of course,so it could be something data specific. However my test would indicate problem is not connect/close (unless something weird on your system). Anyway, best course of action is likely the trace libdb400.a.


bash-4.3$ php execute_array_parms.php 
try connect first ...
array(4) {
  [0]=>
  int(0)
  [1]=>
  string(3) "cat"
  [2]=>
  string(16) "Pook            "
  [3]=>
  string(4) "3.20"
}
try pconnect second ...
array(4) {
  [0]=>
  int(0)
  [1]=>
  string(3) "cat"
  [2]=>
  string(16) "Pook            "
  [3]=>
  string(4) "3.20"
}
bash-4.3$ cat execute_array_parms.php 
<?php
require_once('connection.inc');

echo "try connect first ...\n";

$sql =  "SELECT id, breed, name, weight
    FROM $user.animals
    WHERE id = ? AND name = ?";

$conn_option = array('i5_naming' => DB2_I5_NAMING_ON);
$conn = db2_connect($database, $user, $password);

if ($conn) {
    $stmt = db2_prepare( $conn, $sql);

    if (db2_execute($stmt, array(0, 'Pook'))) {
        while ($row = db2_fetch_array($stmt)) {
            var_dump($row);
        }
    }
}
else {
    echo "Connection failed.\n";
}

$rc = db2_close($conn);

echo "try pconnect second ...\n";


$sql =  "SELECT id, breed, name, weight
    FROM $user.animals
    WHERE id = ? AND name = ?";

$conn = db2_pconnect($database, $user, $password);

if ($conn) {
    $stmt = db2_prepare( $conn, $sql);

    if (db2_execute($stmt, array(0, 'Pook'))) {
        while ($row = db2_fetch_array($stmt)) {
            var_dump($row);
        }
    }
}
else {
    echo "Connection failed.\n";
}


?>

Also seems to work with db2_fetch_assoc (closer to your data set??).

bash-4.3$ php execute_array_parms.php 
try connect first ...
array(4) {
  ["ID"]=>
  int(0)
  ["BREED"]=>
  string(3) "cat"
  ["NAME"]=>
  string(16) "Pook            "
  ["WEIGHT"]=>
  string(4) "3.20"
}
try pconnect second ...
array(4) {
  ["ID"]=>
  int(0)
  ["BREED"]=>
  string(3) "cat"
  ["NAME"]=>
  string(16) "Pook            "
  ["WEIGHT"]=>
  string(4) "3.20"
}
bash-4.3$ cat execute_array_parms.php 
<?php
require_once('connection.inc');

echo "try connect first ...\n";

$sql =  "SELECT id, breed, name, weight
    FROM $user.animals
    WHERE id = ? AND name = ?";

$conn_option = array('i5_naming' => DB2_I5_NAMING_ON);
$conn = db2_connect($database, $user, $password);

if ($conn) {
    $stmt = db2_prepare( $conn, $sql);

    if (db2_execute($stmt, array(0, 'Pook'))) {
        while ($row = db2_fetch_assoc($stmt)) {
            var_dump($row);
        }
    }
}
else {
    echo "Connection failed.\n";
}

$rc = db2_close($conn);

echo "try pconnect second ...\n";


$sql =  "SELECT id, breed, name, weight
    FROM $user.animals
    WHERE id = ? AND name = ?";

$conn = db2_pconnect($database, $user, $password);

if ($conn) {
    $stmt = db2_prepare( $conn, $sql);

    if (db2_execute($stmt, array(0, 'Pook'))) {
        while ($row = db2_fetch_assoc($stmt)) {
            var_dump($row);
        }
    }
}
else {
    echo "Connection failed.\n";
}


?>
bash-4.3$

Here is another test with more calls to 'errmsg' ...

bash-4.3$ php connect_close_pconnect.php 
=== version ===
required version = 1.9.9-zs4
actual version (this) = 1.9.9-zs4
=== db2_connect ===
db2_connect = Resource id #5
db2_conn_errormsg = 
db2_prepare (Resource id #6) = 
db2_execute (Resource id #6) = 
array(4) {
  ["ID"]=>
  int(0)
  ["BREED"]=>
  string(3) "cat"
  ["NAME"]=>
  string(16) "Pook            "
  ["WEIGHT"]=>
  string(4) "3.20"
}
db2_fetch_assoc = 
db2_close = 1
db2_conn_errormsg = 
test - I am working ok!
actual - I am working ok!
=== db2_pconnect ===
db2_pconnect = Resource id #7
db2_conn_errormsg = 
test - I am working ok!
I am working ok!
db2_prepare (Resource id #8) = 
db2_execute (Resource id #8) = 
array(4) {
  ["ID"]=>
  int(0)
  ["BREED"]=>
  string(3) "cat"
  ["NAME"]=>
  string(16) "Pook            "
  ["WEIGHT"]=>
  string(4) "3.20"
}
db2_fetch_assoc = 
bash-4.3$ cat connect_close_pconnect.php 
<?php
require_once('connection.inc');
echo("=== version ===\n");
echo("required version = 1.9.9-zs4\nactual version (this) = ".phpversion('ibm_db2')."\n");
// Verbindungsaufbau
echo("=== db2_connect ===\n");
$conn_option = array('i5_naming' => DB2_I5_NAMING_ON);
$conn = db2_connect("*LOCAL", $username, $password, $conn_option);
//$conn = db2_connect("*LOCAL", $username, $password);
echo("db2_connect = ".$conn."\n");
echo("db2_conn_errormsg = ".db2_conn_errormsg()."\n");
// run prepare/execute/fetch
$sql =  "SELECT id, breed, name, weight FROM $user.animals WHERE id = ? AND name = ?";
$stmt = db2_prepare( $conn, $sql);
echo("db2_prepare ($stmt) = ".db2_stmt_errormsg()."\n");
db2_execute($stmt, array(0, 'Pook'));
echo("db2_execute ($stmt) = ".db2_stmt_errormsg()."\n");
while ($row = db2_fetch_assoc($stmt)) {
  var_dump($row);
  echo("db2_fetch_assoc = ".db2_stmt_errormsg()."\n");
}
// close now please
$rc = db2_close($conn);
echo("db2_close = ".$rc."\n");
echo("db2_conn_errormsg = ".db2_conn_errormsg()."\n");
// Fehlermeldungen
if (db2_conn_errormsg()) echo("test - I am NOT working ok!\n");
else echo("test - I am working ok!\n");
if ($conn) echo("actual - I am working ok!\n");
else die("actual - I am NOT working ok!\n");
echo("=== db2_pconnect ===\n");
$conn = db2_pconnect("*LOCAL", $username, $password, $conn_option);
echo("db2_pconnect = ".$conn."\n");
echo("db2_conn_errormsg = ".db2_conn_errormsg()."\n");
// Fehlermeldungen
if (db2_conn_errormsg()) echo("test - I am NOT working ok!\n");
else echo("test - I am working ok!\n");
if ($conn) echo("I am working ok!\n");
else die("I am NOT working ok!\n");
// run prepare/execute/fetch
$sql =  "SELECT id, breed, name, weight FROM $user.animals WHERE id = ? AND name = ?";
$stmt = db2_prepare( $conn, $sql);
echo("db2_prepare ($stmt) = ".db2_stmt_errormsg()."\n");
db2_execute($stmt, array(0, 'Pook'));
echo("db2_execute ($stmt) = ".db2_stmt_errormsg()."\n");
while ($row = db2_fetch_assoc($stmt)) {
  var_dump($row);
  echo("db2_fetch_assoc = ".db2_stmt_errormsg()."\n");
}
?>
bash-4.3$

Bottom line ... 

Based on my tests (appended), appears no indication 1.9.9-zs4 connect/close is your issue. 

However, ibm_db2 1.9.9 version modifications for PHP 7 were 'substantial'. LUW owners of ibm_db2 (not IBM i), apparently confident in publishing 1.9.9 code to pecl. For my part (IBM i), I have seen many errors php5.6 and php7 after modifications. To wit, posting test versions for IBM i with ibm_db2-1.9.9-zs(n).

Therefore, using the libdb400.a trace may provide some idea of what may be wrong in your test. You already found my mail id, so if trace data is long (likely), then simply send to my mail id. Thanks.

For those lost in problem narrative ... again ... test versions can be found with increasing subversions 1.9.9-zs(4) hosted on Yips (source and test binary).

http://yips.idevcloud.com/wiki/index.php/XMLSERVICE/PHP
latest (this moment in time):
{ibm_db2-1.9.9-zs4-php56.zip} - PHP ibm_db2.so (1.9.9-zs4 binary - php 5.6)

Ops. Sorry. My fault. I see connect/close issue running from the web, but not command line. I will look into the problem and post a fix to YIPs test versions.
(Man, holiday removed my customer 'i meant to say' intuition radar). 


=== version ===
required version = 1.9.9-zs4
actual version (this) = 1.9.9-zs1
=== db2_connect ===
db2_connect = Resource id #1
db2_conn_errormsg = 
db2_prepare (Resource id #2) = 
db2_execute (Resource id #2) = 
array(4) {
  ["ID"]=>
  int(0)
  ["BREED"]=>
  string(3) "cat"
  ["NAME"]=>
  string(16) "Pook            "
  ["WEIGHT"]=>
  string(4) "3.20"
}
db2_fetch_assoc = 
db2_close = 1
db2_conn_errormsg = 
test - I am working ok!
actual - I am working ok!
=== db2_pconnect ===
db2_pconnect = Resource id #3
db2_conn_errormsg = 
test - I am working ok!
I am working ok!


Warning:  db2_prepare(): Statement Prepare Failed in /www/zendsvr6/htdocs/connect_close_pconnect.php on line 42

db2_prepare () = 


Warning:  db2_execute() expects parameter 1 to be resource, boolean given in /www/zendsvr6/htdocs/connect_close_pconnect.php on line 44

db2_execute () = 


Warning:  db2_fetch_assoc() expects parameter 1 to be resource, boolean given in /www/zendsvr6/htdocs/connect_close_pconnect.php on line 46

db2_pclose = 1
db2_pclose =

Ok. Found problem (PHP 7 port helpers ... not me). I will post a new binary 1.9.9-zs5 to yips soon.

I added the following comment to ibm_db2.c code.

#ifdef PASE /* db2_pclose - last ditch persistent close, but reuse zend hash
	* Note: Do not move this code, someone broke IBM i 1.9.9
	*/
	if (endpconnect) {
		conn_res->hdbc = 0;
		conn_res->flag_pconnect=9;
	}
#endif /* PASE */

Ok fixed. Thanks for finding. Apologies for not understanding web context (fail) vs. command line (success).

Find test fix here ...

http://yips.idevcloud.com/wiki/index.php/XMLSERVICE/PHP


Zend Server for php7 and php56 zend developers full source and test binaries

    {ibm_db2-1.9.9-zs5-full-src.zip} - PHP ibm_db2 full source (1.9.9-zs5 source - 2017–01–05)
    {ibm_db2-1.9.9-zs5-php7.zip} - PHP ibm_db2.so (1.9.9-zs5 binary - php 7)
    {ibm_db2-1.9.9-zs5-php56.zip} - PHP ibm_db2.so (1.9.9-zs5 binary - php 5.6)
        fix db2_pclose. Issue may also affect other ‘persistent’ connection close issues (maid service, etc.)

Tank you for your help. I have tested the new version and it works perfectly.

I know that my first description was not the best, sorry. But you can believe me that i nearly despaired in finding the pclose as the reason for this bug at all.

Actually, great bug find! Good job pursuing!

I aknowledge your despair. Feelings are always valid. However, beyond feelings, this case has causal analysis characteristics, aka, interesting science. Please, no offense meant, just analysis, and, questions I need ask to understand perhaps larger issues that may affect other users. Please take in that spirit. 

Root cause: php 7 ibm_db2 changes 1.9.9 have negatively affected php 5.6. Yips site is now up to zs5 in test versions. I expect more issues will be found. This is simply stating facts. Help finding bugs is good.

Test script: first, again, great to find a bug! However, test script demonstrates unexpected connect/close technique. I find myself wondering about why this script is designed in this way? That is, from a performance point of view, the script appears to use multiple bad techniques. I am concerned your production code felt need to follow these performance killing techniques. I wish to understand if larger issues are in play.

1) connect/close followed by a pconect. 

The first connect/close basically eliminates benefit of using pconnect. That is, a qsqsrvr job will be attached and then closed by the first connect/close. This technique will make the script run slow, aka, no obvious value in the second pconnect. Why would you do this?

2) db2_pclose of second connect.

The second pconnect is pclosed after a successful fetch. Again, basically eliminating benefit of using pconnect. Why would you do this?

Please, this is not personal. I am simply trying to understand what sequence of events lead to non-optional double connect/close techniques. That is to say, we may be winning the battle of this particular issue, but losing the war in terms of optional performance using pconnect. Understand my questions?

Hi, 

the test script is only for testing purposes to demonstrate the bug :-)

In my real code i have a jquery request that opens a script with a custom SQL-function that is connected to a RPG-programm (not from me). This SQL needs some time to give me a result and suddenly was not responding after some calls. 

After a long time of searching i made some test changes to our database-manager and found that it was related to the p_close/p_connect in the new ZendServer versions that had the 1.9.9 ibm_db2 module (the not persistent close and connect worked)

The strange thing was that it only happend with our custom SQL-function that had no errors when running in green screen or debugging. So i played arround with the p_close/p_connect and came tho test script i posted. It was the only way to demonstrate it, as i could not post the SQL-function (company ralated).

It was a very very long way to this point...

How very interesting ... I hope you do not mind a bit more analysis.

Part 1 "bug before time" (before this pecl bug) ...

So, original issue is a mysteriously failing custom SQL function, aka, ibm_db2 called native RPG stored procedure. Trail and error eventually lead to use of additional db2_connect/db2_close. Now, odd part is additional connect/close should have no effect. That is, script technique first connect/close attach an unused/reused 'clean' QSQSRVR job from pre-start pool. Essentially, your custom could in no way be running in a (re-)attached QSQSRVR job from the pool. 

Part 2 "trial and error coalescence?" ...

I suspect effective change was second pconnect with addition of db2_pclose. To wit, db2_pconnect/db2_pclose is essentially exactly db2_connect/db2_close. That is, 'persistent connection' is not 'persistent' at all. Where, each script request (browser touch), will actually open/close QSQSRVR jobs every time. In this case it will open/close two different QSQSRVR jobs (although you may get the same QSQSRVR job both times from the return QSQ pool). Thereby, we have masked original custom SQL problem by simply putting away the QSQSRVR job every time. 

Summary ...

In brief, we simply do not know what went wrong with custom SQL call. In fact, my scientific curiosity begs recreation of the original problem. Unfortunately, our mystery problem has lead to sequence of 'performance unhealthy' double connect/close. Also, side note, single connect/close or pconnect/pclose should do the same job (theory).

Action ...

I suspect DB2 traces are required to figure out original custom SQL call problem. More to point, custom SQL call (RPG) is occurring on the QSQSRVR side (server side), indicating best course of action is contact IBM service and run DB2 traces. 

However, always possible php ibm_db2 side (client side), Therefore we could experiment recreate the original custom SQL call problem while using libdb400.a trace. I would look at the trace and see if anything pops out from the PASE side (ibm_db2 side).

I will copy the old version to our server and send you a trace file from the SQL-function next week.

Great!

However to be clear ...

I would like you to recreate custom SQL call 'missing data' problem while using 1.9.9-zs5 (latest). That is, we already figured out pconnect/pclose error (bad php 7 port issue), so let us find out if SQL call issue still exists with new 1.9.9-zs5 code/binary. 

To set expectations, I fully expect SQL call issue remains with 1.9.9-zs5. Actually, should SQL call issue simply 'go away', while delighted, we should remain sceptical, aka, try, try, again, to make very sure gone.

Thanks again.

The pclose issues solved with temp fix found on YIPS in subversion 1.9.9-zg5.
http://yips.idevcloud.com/wiki/index.php/XMLSERVICE/PHP
When all testing completed, pecl will be updated with a new release (likely 2.0.0).

FYI -- The other issue with query function failing appears to be a user issue with the function. Suggestion for larger buffer was made, no response for number of days. We will hope for the best i guess.