This test is unrealistic. No script will execute 20000 times in a single script request before returning to the browser with results. Sure, fetch can be 1000s, but not prepare/execute (or execute). If this is production code, it needs to be rewritten.

Technically, db2_free_result($stmt) was made a no-op by the LIW team. They did this because too many customers (and php) double free stmt handles. On IBM i this is especially drmatic double free becuase DB2 re-uses the handle. In fact, DB2 only allows 32K handles active in a single php/QSQSRVR pair. Therefore this test is almost at the limit of DB2 itself.

I don't believe this is work is worthy the effort. Especially as the side effects of user control over db2_free_result($stmt) seem to be so very bad. Typical production application script maybe open 1-15 handles, then return to browser. These handles are close sutomatic by php ibm_db2 dtor logic (php pecl clean-up register).

Again, I don't believe this is work is worthy the effort. Howvere feel free to make your case.

Again, not realistic. However, I mixed topics to avoid 2 unrealistic tests (expecting next also).

1) db2_execute/db2_free_result 20000 times is unrealistic. Single application have 20000 result sets to worry about in single request ... no??? The db2_free_result will clean up most of any allocation (632 bytes/per maybe temp leak). Clean-up both result and stmt occurs on PHP dtor.

2) db2_prepare/db2_execute/db2_free_stmt 20000 times is also unrealistic. Single application have 20000 statements to worry about in single request ... no??? The db2_free_stmt is a no-op (double free). The db2_free_stmt is no-op. Clean-up both result and stmt occurs on PHP dtor. Also IBM i DB2<>QSQSRVR has only 32K handles (run out).

However, beyond initial, if legitimate production problem. Please describe 20000 operations per browser request are doing. Who knows, maybe we can do something in ibm_db2, or, maybe db2sock to help.

Works for me (no leak). I can't even replicate the problem on My IBM i (V7R1). Maybe your issue is result set data specific???

===
my run works fine (no leak)
===
bash-4.3$ php --version | grep built              
PHP 7.1.2 (cli) (built: Feb 22 2017 16:06:40) ( NTS )
bash-4.3$ php -i | grep 2.0.2                     
Module release => 2.0.2-db2sock3
Phar EXT version => 2.0.2
bash-4.3$ cat loopleak.php                        
<?php
$db = db2_connect('*LOCAL', '', '');
$stmt = db2_prepare($db, 'select * from QIWS.QCUSTCDT where LSTNAM=?');
var_dump($stmt);
$cnt = 0;
foreach (range(1,20000) as $id) {
 db2_execute($stmt, [$id]);
 if (($cnt % 1000) == 0) { #Display memory usage each 1000 iterations
   echo "MEMORY: " . memory_get_usage() . "\n";
 }
 $cnt++;
 db2_free_result($stmt);
}
?>
bash-4.3$ php loopleak.php                        
resource(5) of type (DB2 Statement)
MEMORY: 1132456
MEMORY: 1132456
MEMORY: 1132456
MEMORY: 1132456
MEMORY: 1132456
MEMORY: 1132456
MEMORY: 1132456
MEMORY: 1132456
MEMORY: 1132456
MEMORY: 1132456
MEMORY: 1132456
MEMORY: 1132456
MEMORY: 1132456
MEMORY: 1132456
MEMORY: 1132456
MEMORY: 1132456
MEMORY: 1132456
MEMORY: 1132456
MEMORY: 1132456
MEMORY: 1132456
bash-4.3$ cat /usr/local/zendphp7/var/log/php.log 
star

My test works (no leak).

Wild guessing ... your test ... maybe look at the php log and see if the test is running at all??

clear the log ...
$ echo start > /usr/local/zendphp7/var/log/php.log 
$ php mytest.php
... anything in the log ???
$ cat /usr/local/zendphp7/var/log/php.log
start

Thank you for time on this, I appreciate it.

My example is extremely simplistic to simple show the memory usage.  The production issue that turned us on to this is much more complicated.
1)Browser app allows for excel spreadsheet upload.
2)Spreadsheet data is validated and saved in a table (possibly up to 700K rows).
3)Command line job then has to process that working table and update 1 or 2 master tables.  This is where our memory issues show up.
4)Command line script uses a PHP generator function to select the 700K rows and loop over them.  Outside of this loop, we have used db2_prepare to setup some SQL Update statements.  Inside the loop, we look at each row and depending on certain flags use db2_execute to update rows in other tables.
5)Without the db2_execute, we can loop over the 700K rows in seconds with no memory issues.  Using db2_free_result or not makes no difference.  But the db2_execute seems to increase our memory usage until we run out of resources.

I have run the exact sample code you supplied to show that I get the memory increase on my IBMi 7.3 system.

[10:02:43][dst871@ZENDTST:~]$ /usr/local/zendphp7/bin/php --version | grep built
PHP 7.1.12 (cli) (built: Nov 26 2017 15:29:15) ( NTS )
[10:03:09][dst871@ZENDTST:~]$ /usr/local/zendphp7/bin/php -i | grep 2.0.2
Module release => 2.0.2-zs1
Phar EXT version => 2.0.2
[10:03:29][dst871@ZENDTST:~]$ cat loopleak.php
<?php
$db = db2_connect('*LOCAL', '', '');
$stmt = db2_prepare($db, 'select * from QIWS.QCUSTCDT where LSTNAM=?');
var_dump($stmt);
$cnt = 0;
foreach (range(1,20000) as $id) {
 db2_execute($stmt, [$id]);
 if (($cnt % 1000) == 0) { #Display memory usage each 1000 iterations
   echo "MEMORY: " . memory_get_usage() . "\n";
 }
 $cnt++;
 db2_free_result($stmt);
}
?>
[10:03:41][dst871@ZENDTST:~]$ /usr/local/zendphp7/bin/php loopleak.php
resource(42) of type (DB2 Statement)
MEMORY: 1668040
MEMORY: 2324040
MEMORY: 2980040
MEMORY: 3636040
MEMORY: 4292040
MEMORY: 4948040
MEMORY: 5604040
MEMORY: 6260040
[10:05:03][dst871@ZENDTST:~]$ cat /usr/local/zendphp7/var/log/php.log
start

I just don't know what is wrong. Your test maybe? Data specific column? Need a lot more info for any hope to figure out. Ball in your court (davetay1021).


bash-4.3$ cat loopleak.php 
<?php
$db = db2_connect('*LOCAL', '', '');
$ret = db2_exec($db,'drop table bob');
$ret = db2_exec($db,'create table bob(id int not null, msg char(4096) not null)');
$stmt = db2_prepare($db, 'insert into bob values(?,?)');
var_dump($stmt);
$cnt = 0;
foreach (range(1,20000) as $id) {
  $msg = "bob is happy ".$id;
  db2_execute($stmt, [$id, $msg]);
 if (($cnt % 4000) == 0) {
   echo "insert ".$msg. "\n";
 }
 $cnt++;
}
$stmt = db2_prepare($db, 'select * from bob');
db2_execute($stmt);
var_dump($stmt);
$cnt = 0;
while($row=db2_fetch_array($stmt)) {
 if (($cnt % 4000) == 0) {
   echo "select ".trim($row[1]). "\n";
 }
 $cnt++;
}
$stmt = db2_prepare($db, 'select * from bob where id=?');
var_dump($stmt);
$cnt = 0;
foreach (range(1,20000) as $id) {
 db2_execute($stmt, [$id]);
 if (($cnt % 1000) == 0) { #Display memory usage each 1000 iterations
   echo "select by id=".$id;
   echo " MEMORY: " . memory_get_usage() . "\n";
 }
 $cnt++;
 db2_free_result($stmt);
}
?>
bash-4.3$ php loopleak.php 
resource(7) of type (DB2 Statement)
insert bob is happy 1
insert bob is happy 4001
insert bob is happy 8001
insert bob is happy 12001
insert bob is happy 16001
resource(8) of type (DB2 Statement)
select bob is happy 1
select bob is happy 4001
select bob is happy 8001
select bob is happy 12001
select bob is happy 16001
resource(9) of type (DB2 Statement)
select by id=1 MEMORY: 1137120
select by id=1001 MEMORY: 1137120
select by id=2001 MEMORY: 1137120
select by id=3001 MEMORY: 1137120
select by id=4001 MEMORY: 1137120
select by id=5001 MEMORY: 1137120
select by id=6001 MEMORY: 1137120
select by id=7001 MEMORY: 1137120
select by id=8001 MEMORY: 1137120
select by id=9001 MEMORY: 1137120
select by id=10001 MEMORY: 1137120
select by id=11001 MEMORY: 1137120
select by id=12001 MEMORY: 1137120
select by id=13001 MEMORY: 1137120
select by id=14001 MEMORY: 1137120
select by id=15001 MEMORY: 1137120
select by id=16001 MEMORY: 1137120
select by id=17001 MEMORY: 1137120
select by id=18001 MEMORY: 1137120
select by id=19001 MEMORY: 1137120
bash-4.3

I have cut down your example to just the loop of insert statements. I still see the memory issue but also found a large difference in speed of execution using only db2_exec() vs db2_prepare()/db2_execute().

db2_exec() only:
[14:41:09][dst871@ZENDTST:/home/dst871]$ cat exec-only.php
<?php
$db = db2_connect('*LOCAL', '', '');
$ret = db2_exec($db,'drop table phptst.bob');
$ret = db2_exec($db,'create table phptst.bob(id int not null, msg char(4096) not null)');

$now = time();
$duration = 0;

$cnt = 0;
foreach (range(1,20000) as $id) {
  $msg = "bob is happy ".$id;
  db2_exec($db, "insert into phptst.bob values($id, '$msg')");
  if (($cnt % 4000) == 0) {
        $duration = (time() - $now);
        $now = time();
        echo $id . ": Duration " . $duration . " seconds\n";
        echo $id . ": Memory " . memory_get_usage() . "\n";
  }
  $cnt++;
}
[14:36:32][dst871@ZENDTST:/home/dst871]$ /usr/local/zendphp7/bin/php exec-only.php
1: Duration 0 seconds
1: Memory 1669512
4001: Duration 2 seconds
4001: Memory 3269520
8001: Duration 1 seconds
8001: Memory 4869520
12001: Duration 2 seconds
12001: Memory 6469520
16001: Duration 1 seconds
16001: Memory 8069520

db2_prepare()/db2_execute():
[14:47:13][dst871@ZENDTST:/home/dst871]$ cat test.php
<?php
$db = db2_connect('*LOCAL', '', '');
$ret = db2_exec($db,'drop table phptst.bob');
$ret = db2_exec($db,'create table phptst.bob(id int not null, msg char(4096) not null)');

$stmt = db2_prepare($db, 'insert into phptst.bob values(?,?)');

$now = time();
$duration = 0;

$cnt = 0;
foreach (range(1,20000) as $id) {
  $msg = "bob is happy ".$id;
  db2_execute($stmt, [$id, $msg]);
  if (($cnt % 4000) == 0) {
        $duration = (time() - $now);
        $now = time();
        echo $id . ": Duration " . $duration . " seconds\n";
        echo $id . ": Memory " . memory_get_usage() . "\n";
  }
  $cnt++;
}

[14:47:14][dst871@ZENDTST:/home/dst871]$ /usr/local/zendphp7/bin/php test.php
1: Duration 0 seconds
1: Memory 1670024
4001: Duration 47 seconds
4001: Memory 4485240
8001: Duration 148 seconds
8001: Memory 7301240
12001: Duration 258 seconds
12001: Memory 10117240
16001: Duration 393 seconds
16001: Memory 12933240

The time it takes db2_execute() to complete this script is more than excessive. Is it possible it is a system issue causing that big a difference between those 2 db2_ functions?

I am currently working on getting other versions of PHP on this system to test with and see if there's a difference.  

The results above are from PHP 7.1.12 and ibm_db2 version 2.0.2-zs1.
On PHP 5.6.18 and ibm_db2 version 1.9.7, this script runs in seconds with no memory issue.

After further testing, I was able to run the loop of insert statements successfully on one of our 4 LPARs.  All 4 LPARs are identical in software and hardware.  The issue persists on 3 of these LPARs.  But since we were able to successfully run this on the 4th, I agree this is not an ibm_db2 extension issue. This can be closed.

As per last comment closing the issue.