menu

Questions & Answers

PHP to DB2 with PDO_IBM Error : SQLDriverConnect: -1390 [IBM] [CLIDRIVER] SQL10007N

In Ubuntu 22.04 I am attempting to connect PHP with a DB2 database via the driver pdo_ibm.so

I have followed this guide, along with IBM documentation: https://github.com/php/pecl-database-pdo_ibm

I've compiled version 1.5.0. into the ".so" file and placed it on the respective driver's folder.

However I am getting the following error

enter image description here

SQLDriverConnect: -1390 [IBM] [CLIDRIVER] SQL10007N Message "0" could not be retrieved. Reason code "3".

Using the following PHP code:

var_dump($dsn);
echo "". PHP_EOL;
var_dump($this->_config['username']);
var_dump($this->_config['password']);
var_dump($this->_config['driver_options']);

$this->_connection = new PDO(
$dsn,
$this->_config['username'],
$this->_config['password'],
$this->_config['driver_options']
);

/*$this->_connection = new PDO("ibm:DRIVER={IBM DB2 ODBC DRIVER};DATABASE=testdb;" .
"HOSTNAME=localhost;PORT=50000;PROTOCOL=TCPIP;", "db2inst1", "123456", array(
PDO::ATTR_PERSISTENT => FALSE, 
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION)
); */

I am using the follwing env variables, set on the ~/.bashrc file:

ODBCHOME=/home/poc-dev/etc

ODBCINI=/home/poc-dev/clidriver/system_odbc.ini

DB2INSTANCE=db2inst1

DB2HOME=/home/poc-dev/clidriver

IBM_DB_HOME=/home/poc-dev/clidriver

LD_LIBRARY_PATH=/home/poc-dev/clidriver/lib

_=/usr/bin/printenv

I have succesfully connected with DBeaver and ISql to the database

What am I missing?

Edit: The user running PHP is "www-data" image of apache envvars: enter image description here

Comments:
2023-01-07 20:30:27
This is usually caused by a configuration error. You wrote that you verified the connection with isql, but you did not write whether the account that ran the isql is the same account that runs php. Please verify that the account that runs php has two environment variables correctly defined, i.e. IBM_DB_HOME and LD_LIBRARY_PATH.
2023-01-07 20:30:27
isql connects with the main account in the machine called "poc-dev". PHP executes it's processes with "www-data" this was verified by the command "whoami" which is the apache2 user. I have configured the /etc/apache2/envvars file with every IBM DB2 variable. I'll add the image.
2023-01-07 20:30:27
Also mentioning I need to operate DB2 with the CLIDRIVER not the full version of the CLIENT or DATASERVER (which takes more space that's why I haven't configured the user and CLIDRIVER does not have db2icrt). No tutorial says something about that.
2023-01-07 20:30:27
If the www-data account cannot connect to Db2 with isql, then PHP will not connect. The corrollary should also be true. Fix that first. Next, clidriver does not use a client-side instance, but does require a valid configuration (env. vars etc). If you are connecting to a Db2-LUW database, or do a Db2-server whose databases have unicode, then I advise to export LANG=XX_YY.UTF-8 (where XX_YY matches your client side geographical territory). Determine the difference(s) between the working and failing env-vars for isql.
2023-01-07 20:30:27
isql was executed and tested with my login account "poc-dev" and "root" but not the "www-data" user. The "www-data" user is a no login account, and I confirmed it does not have the env variables, it has not inherited the ~/.bashrc ENV variables for some reason.
2023-01-07 20:30:27
As the root cause is now clear, adjust your configuration accordingly.
2023-01-07 20:30:27
I have adjusted my configuration. The ENV vars are the same now for "root" "poc-dev" and "www-data". I confirmed the variables are visible with the command-u ww-data printenv, I have also added the variables to appaches envvars. When using the connection string ibm:DRIVER={IBM DB2 ODBC DRIVER};DATABASE=testdb;" . "HOSTNAME=localhost;PORT=50000;PROTOCOL=TCPIP; with the parameters I get a SQLAllocHandle 0 error. If I attempt to use a DSN from db2cli.ini I get a SQLDriverConnect: -5005[IBM] [CLIDRIVER] SQL10007N Message "0" could not be retrieved. Reason code "3".
Answers(1) :

Apache2 uses the "www-data" user to run PHP. As that "www-data" has to have full access to the DB2 CLIDRIVER in all senses as user mao pointed out.

I have found that I had to place the exact same variables as ~/.bashrc in the etc/enviroment file (I was mistakenly thinking I made them global), after I did that I could see the enviroment variables as the "www-data" user which I verified via the command:

sudo -u www-data printenv

I confirmed the ENV-variables for the DB2 CLIDRIVER files folder: /home/poc-dev/clidriver were there, but they had 755 permissions which I needed to change to 777 permission via the command:

sudo chmod 777 -R /home/poc-dev/ 

After that I tested the ISQL command on the user "www-data" by the command (since the first time I realized I was getting a message where ISQL could not find one of the libraries, which turned out to be a permissions problem):

sudo -u www-data isql TEST db2inst1 123456

"TEST" being the registered DSN in odbc.ini, "db2inst1" being the user on the dataserver (in a virtual machine in this case)

Then I proceded to use:

$this->_connection = new PDO("ibm:DRIVER={IBM DB2 ODBC DRIVER};DATABASE=testdb;" .
"HOSTNAME=localhost;PORT=50000;PROTOCOL=TCPIP;", "db2inst1", "123456", array(
PDO::ATTR_PERSISTENT => FALSE, 
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION)
);

In PHP. Hopefully this helps someone

Comments:
2023-01-07 20:30:27
It would be more wise to unpack (and configure) clidriver in the home directory of the account that runs PHP. That isolates php from any potential changes resulting from using a different account (proc-dev) e.g. directory changes, new versions, permission changes, user/group membership changes. The clidriver is so small that having multiple copies is not a problem.