Problem with db2_fetch_both() and DISPLAY_JOURNAL table function

[adrotate group=”9″]

In a previous post we mentioned that we had problems with the new DISPLAY_JOURNAL table function shipped in a recent DB2 update PTF for IBMi. We found the reason for that problem was a requirement to add commitment control around the SQL request due to CLOB data being returned. A working example of that code can be found here

We are now trying to get the same output using the db2_exec() function and the db2_fetch_both() function. Here is the updated code which should return the same data we are able to retrieve using the i5_toolkit functions.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=iso-8859-1" http-equiv=Content-Type></HEAD>
<BODY>
<?php
// connect to the i5
$options = array("i5_lib"=>"chlib","cursor"=>DB2_SCROLLABLE,"autocommit"=>DB2_AUTOCOMMIT_ON);
$conn = db2_connect("","","",$options);
if (is_bool ( $conn ) && $conn == FALSE) {
	die ( "No Connection ");
	}
$ac = db2_autocommit($conn);
if ($ac == 0) {
    print "$ac -- AUTOCOMMIT is off.";
} else {
    print "$ac -- AUTOCOMMIT is on.";
}
$query = "SELECT *
              FROM table(
                DISPLAY_JOURNAL('HA4IJRN',
                              'HA4IJRN',
                               '',
                               '',
                               CAST(null as TIMESTAMP),
                               CAST(null as DECIMAL(21,0)),
                               '',
                               '',
                               '',
                               '',
                               '',
                               '',
                               '',
                               '',
                               '')
                       ) as X";
echo($query);
$result = db2_exec($conn,$query);
if(!$result) {
   die("Query failed ");
   }
$row = db2_fetch_both($result);
echo("Dumping the data");
var_dump($row);
db2_free_result($result);
db2_close($conn);
?>
</table>
</BODY>
</HTML>

Here is the output we get when the above script is run directly in the IBMi (The Easycom toolkit does not support db2_functions so it has to be run under the Zend Server, something that could change in the future?).

1 — AUTOCOMMIT is on.SELECT * FROM table( DISPLAY_JOURNAL(‘HA4IJRN’, ‘HA4IJRN’, ”, ”, CAST(null as TIMESTAMP), CAST(null as DECIMAL(21,0)), ”, ”, ”, ”, ”, ”, ”, ”, ”) ) as XDumping the databool(false)

This shows that the commitment control is set to on and the query runs OK, we just cannot get the data back using the db2_fetch_both() function? when we look into the logs we find the following.

[21-Dec-2011 16:34:44] PHP Warning: db2_fetch_both() [function.db2-fetch-both]: Fetch Failure in /www/phpproj/htdocs/db2test1.php on line 41

This is stating the function was unable to return the data due to a failure but no reason code for the failure? Its only a warning as well so why it fails is a mystery. I have trawled through all of the logs I can find with no success? I know this code is maintained by IBM and is shipped as part of Zend Server, but not sure if Zend would actually know what the problem is or if IBM should be approached? I did try all of the other db2_fetch functions against the result and they have the same issue. $result is set to true so the query should have worked, its just not returning the data for some reason. If I take the output above and start an SQL interactive session on the IBMi and run it it returns all of the data as expected. I also tried to turn off the auto commit but that did not work? Has anyone else managed to get this working other than in the i5_query function as I have shown in the previous post? I have also posted this problem on the IBM Developer forums just in case IBM or anyone else has any input to add.

If you have any input let me know.

Chris…