Page 1 of 1

Unqualified Tables ignore i5_libl options

PostPosted: Wed Mar 08, 2017 4:55 pm
by dan8246
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.";


$stmt = db2_prepare($conn, $sql);
$result = db2_execute($stmt);
    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>";
    echo "failed<br />";
    echo db2_stmt_error()." : ".db2_stmt_errormsg()."<br />";

Has anyone run into this before? Does anyone have any troubleshooting suggestions?

Re: Unqualified Tables ignore i5_libl options

PostPosted: Thu Mar 16, 2017 1:49 pm
by dan8246
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,

Re: Unqualified Tables ignore i5_libl options

PostPosted: Fri Mar 24, 2017 5:09 pm
by dan8246
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.

Re: Unqualified Tables ignore i5_libl options

PostPosted: Mon Apr 03, 2017 8:50 pm
by dan8246
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:

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.