PHP rollback on IBMi db2 doesn't work

Question

I have two IBMi environments

1. development

   • OS400 V7R1M0 updated with latest cumulative PTF,
   • PHP Version: 5.6.5
   • Zend Server Version: 8.0.2

2. production

   • OS400 V7R1M0 updated with latest cumulative PTF
   • PHP Version: 5.3.8
   • Zend Server Version: 5.5.0

I have an issue related with autocommit and rollback. Using the following php script on both environments I get different behaviours. The development IBMi correctly put records in the table and after the rollback it delete them. On the other hand the production one write the records to the table, but doesn't run the rollback command and the records remain on the file. Obviously I've runned, the same way, the actions needed to activate JOURNALING on the table/file.

I've noticed that the rollback command works on same file from and RPG Program!

Does anyone knows some bugs or problems related to this issue concerning ZendServer or PHP version?

Thanks in advance

Script:
<?php
ini_set('display_errors', 'On');
ini_set('max_execution_time', 0);
error_reporting(E_ALL);
$options = array(
'i5_naming' => DB2_I5_NAMING_ON,
'DB2_ATTR_CASE' => DB2_CASE_LOWER,
'autocommit' => DB2_AUTOCOMMIT_OFF,
'i5_commit' => DB2_I5_TXN_READ_COMMITTED
);
$conn = db2_connect('DBNAME', 'USER', 'PASSWORD', $options);

if ($conn) {
$stmt = db2_exec($conn, "SELECT count(*) FROM TEST9P");
$res = db2_fetch_array($stmt);
echo $res[0] . "\n";
$ac = db2_autocommit($conn);
if ($ac == 0) {
print "$ac -- AUTOCOMMIT is off.\n";
}
else {
print "$ac -- AUTOCOMMIT is on.\n";
}
// Delete all rows from TEST9P
for ($i = 0;$i < 10;$i++) {
db2_exec($conn, "INSERT INTO TEST9P (test01) VALUES (" . $i . ")");
}
$stmt = db2_exec($conn, "SELECT count(*) FROM TEST9P");
$res = db2_fetch_array($stmt);
echo $res[0] . "\n";
// Roll back the DELETE statement
db2_rollback($conn);

$stmt = db2_exec($conn, "SELECT count(*) FROM TEST9P");
$res = db2_fetch_array($stmt);
echo $res[0] . "\n";
db2_close($conn);
}

Answer 1

From ZEND CUSTOMER SUPPORT:

CREATE SCHEMA vs. CRTLIB
The default for Zend Product for IBMi is: ibm_db2.i5_allow_commit =0 (no commit processing allowed).

This was done as the ZS For IBMi default because many IBMi libraries are created with CRTLIB which does not enable the journaling needed for commit processing (Which results in ALL PHP db2 scripts failing if i5_allow_commit =1).

So you basically have a php.ini default choice here...

1. ibm_db2.i5_allow_commit =0 which allows the most inexperienced user to write PHP ibm_db2 scripts that will "work" by disabling commit in any type of schema (CREATE SCEMA or CRTLIB)
2. ibm_db2.i5_allow_commit =1 which expert i5/OS database developers would know to use in only CREATE SCEMA journal enabled containers and let all the non-expert PHP script developers FAIL.

You should use schemas (libraries), created with the SQL statement CREATE SCHEMA over CRTLIB to enable journaling.

The ibm_db2 commit APIs will not function without journal enabled in the schema (library). In addition, some ibm_db2 BLOB/CLOB scenarios require journal enabled.

• http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp

Commit/Rollback
To enable all ibm_db2 commit functions, the entry ibm_db2.i5_allow_commit option must be set in the php.ini file

php.ini entry ibm_db2.i5_allow_commit (integer)
This option controls the commit mode used for i5 schema collections in the PHP application.

• 0 no commit (see i5_commit for override)
• 1 allow commit (see i5_commit for override)

i5_commit
The i5_commit attribute should be set before the db2_connect().

If the value is changed after the connection has been established, and the connection is to a remote data source, the change does not take effect until the next successful db2_connect() for the connection handle.

Note the php.ini setting ibm_db2.i5_allow_commit=1 overridden with the i5_commit options:

• DB2_I5_TXN_NO_COMMIT - Default setting Commitment control is not used.
• DB2_I5_TXN_READ_UNCOMMITTED - Dirty reads, non-repeatable reads, and phantoms

are possible.

• DB2_I5_TXN_READ_COMMITTED - Dirty reads are not possible, Non-repeatable reads and phantoms are possible.

• DB2_I5_TXN_REPEATABLE_READ - Dirty reads and non-repeatable reads are not possible. Phantoms are possible.

• DB2_I5_TXN_SERIALIZABLE - Transactions are serialize-able. Dirty reads, non-repeatable reads, and phantoms are not possible

Example:
$options = array("i5_commit"=>DB2_I5_TXN_NO_COMMIT);
$i5 = db2_connect($i5localhost, $i5user, $i5password, $options);