Page 1 of 1

PHP Connect to remote server - query issue

Posted: Thu May 26, 2016 3:58 pm
by rich8182
I am running Zend Server on an IBMi for PHP development. All works fine as long as I'm connecting to the local db on the IBMi

I now have a situation where I need to connect to a remote DB2 LUW database that is running on a Windows server to retrieve some data and place in a work file on the IBMi to use in some report generation.

Our IBMi has an entry for the remote db in WRKRDBDIRE, and I'm able to successfully use it to connect to the remote DB.

My issue comes when I attempt to query the remote DB.

My remote DB connection file is:

Code: Select all

$database = 'VSADB2';
$user = '.....';
$password = '......';
$wcConn = db2_connect($database, $user, $password);

if (! $wcConn) {
    echo "SQLSTATE value: " . db2_conn_error().'<br>';
    echo db2_conn_errormsg().' <br>';
    die('<br>Connection failed! <br>');
}
else{
    echo 'Remote DB2 Connection Successful! <br>';

}

Preparing my query and attempting to execute looks like this:

Code: Select all

$sql = "SELECT (MAX(DATE) - 91 DAYS) AS START_DATE, (MAX(DATE) - 365 DAYS) AS END_DATE FROM ADMINISTRATOR.TCALENDAR WHERE YEAR=2016 AND PERIOD=4"; 

// Prepare the SQL Statement
$stmt = db2_prepare($wcConn, $sql);
if (! $stmt) {
    $error = db2_stmt_errormsg();
    die("Error occurred on prepare. " . $error);
}

// Execute the SQL Statement
$result = db2_execute($stmt);
if (! $result) {
    $error = db2_stmt_errormsg();
    die("Error occured on execute. " . $error);
}

while ($row = db2_fetch_assoc($stmt)) {
...
}


When I run it I get the following error:

Error occurred on prepare. COMMIT(*NONE) valid only if relational database VSADB2 (product identification SQL10055) is DB2 for IBM i. SQLCODE=-5028

VSADB2 is a remote DB2 LUW database on a Windows server. I'm not attempting to set commitment control at all. What do I need to do to get my queries to run on the remote DB?

Thanks for the help

Re: PHP Connect to remote server - query issue

Posted: Thu May 26, 2016 5:34 pm
by scottgcampbell
You probably want to look at i5_commit and autocommit options and determine (test until you find something that works :) ) what values you want to use:

http://php.net/manual/en/function.db2-connect.php

Sorry I can't help beyond that but I don'y have anything to test with.
Scott

Re: PHP Connect to remote server - query issue

Posted: Thu May 26, 2016 9:59 pm
by rich8182
Thanks,

I added the option i5_commit => DB2_I5_TXN_SERIALIZABLE and the query works now.