Unqualified Tables ignore i5_libl options

General discussion on Zend Server for IBM System i

Unqualified Tables ignore i5_libl options

Postby dan8246 on Wed Mar 08, 2017 4:55 pm

I'm trying to configure a local development workstation to connect to a remote IBM i DB2 server. I'm using IBM Data Server Client in conjunction with Zend Server trial (8.5.7), PHP (5.6.30) on Windows 10. I've cataloged the nodes and database in the DB2 command window and can successfully connect to the DB2 server using the DB2_* functions. The issue I'm having is when I rely on the i5_libl option which I'm passing to db2_connect() by using unqualified table names. I'm running IBM i V7R2.

I've confirmed that my library list is actually changing when I create the connection by querying QSYS2.LIBRARY_LIST_INFO. I'm not sure if there is another way I should be verifying this though.

Code: Select all
$database = '<database name>';
$user = '<user name>';
$password = '<password';
$port = <port>;

$options['i5_naming'] = DB2_I5_NAMING_ON;
$options['autocommit'] = DB2_AUTOCOMMIT_OFF;
$options['i5_libl'] = 'MYLIB YOURLIB ANYLIB';

$conn = db2_connect($database, $user, $password, $options);

if ($conn) {
    echo "Connection succeeded."; //It succeeds

}
else {
    echo db2_conn_error()." | ".db2_conn_errormsg()."<br />";
    echo "Connection failed.";
}

$sql = "SELECT * FROM QSYS2.LIBRARY_LIST_INFO";

$stmt = db2_prepare($conn, $sql);
$result = db2_execute($stmt);
if($result){
    while($row = db2_fetch_assoc($stmt)){
        echo "<pre>";
        var_dump($row);  //In addition to entries for QSYS, QSYS2, QUSRSYS and QHLPSYS I get entries for MYLIB, YOURLIB and ANYLIB.
        echo "</pre>";
    }
}else{
    echo "failed<br />";
    echo db2_stmt_error()." : ".db2_stmt_errormsg()."<br />";
}


Has anyone run into this before? Does anyone have any troubleshooting suggestions?
dan8246
 
Posts: 36
Joined: Fri Jan 23, 2015 5:53 pm

Re: Unqualified Tables ignore i5_libl options

Postby dan8246 on Thu Mar 16, 2017 1:49 pm

Just adding to my question. I realized I may not have been entirely clear in my previous post.

The PHP manual states "Unqualified files are resolved using the library list for the job." when i5_naming is enabled. This isn't happening as I am enabling i5_naming as part of my connection. It currently uses the current DB user name as the qualified library instead of the library list.

Has anyone ever enabled i5_naming when connecting to a remote DB2 server and not had unqualified names resolved using the library list? As previously posted I'm checking the current job's library list by running the query "SELECT * FROM QSYS2.LIBRARY_LIST_INFO". If this is not an accurate method to obtaining this information, can anyone provide me with steps to an alternative method? I'm not sure how to do this as the PHP script can be serviced by one of many jobs and is only active for a short period of time.

Much appreciation,
Dan
dan8246
 
Posts: 36
Joined: Fri Jan 23, 2015 5:53 pm

Re: Unqualified Tables ignore i5_libl options

Postby dan8246 on Fri Mar 24, 2017 5:09 pm

So it looks like the 'i5_libl' option is not the only one being ignored. I've realized I can only qualify tables using a '.' and not a '/'. This indicates the 'i5_naming' setting being used is 'DB2_I5_NAMING_OFF ' despite passing in a value of 'DB2_I5_NAMING_ON'.

What makes this confusing is that when I query 'QSYS2.LIBRARY_LIST_INFO' it returns the exact library list I passed into db2_connect() in the 'i5_libl' element of the $options array. I'm 110% sure the 'i5_libl' value is not the default library. I've tested with many values and no matter which variation the query accurately reflects them. Additionally, I've dumped the $options array right before calling db2_connect() and it contains exactly what I expect it to contain.

This might be worth of a PMR.
dan8246
 
Posts: 36
Joined: Fri Jan 23, 2015 5:53 pm

Re: Unqualified Tables ignore i5_libl options

Postby dan8246 on Mon Apr 03, 2017 8:50 pm

I finally solved this after opening a PMR with IBM. All I had to do was apply the latest Fix Pack for DB2 Connect Personal Edition.

Suggested Fix Packs for DB2 Connect:

http://www-01.ibm.com/support/docview.wss?rs=71&uid=swg21321001

Basically the DB2 Connect version I had was released prior to 2013. It was in 2013 IBM added two tier support by adding the `i5_naming` option. So my DB2 Connect setup was effectively ignoring the option I was passing. That explains why the other options still went through. On the DB side, since it didn't receive a value for `i5_naming` - it remained as the default.
dan8246
 
Posts: 36
Joined: Fri Jan 23, 2015 5:53 pm


Return to Zend Server for IBM i

Who is online

Users browsing this forum: No registered users and 4 guests