With the introduction of Open Source Package Management, IBM has been providing more and more 64-bit versions of open source software compiled to run on IBM i. In that spirit, they have provided a unixODBC driver and driver manager that implement unixODBC, which seeks to be the definitve standard for ODBC on non MS Windows platforms. The unixODBC driver is now the preferred driver to access DB2 on IBM i, deprecating the SQL-CLI driver that has long been the DB2 driver used in PASE projects, including PHP.
Zend is providing the PDO_ODBC PHP driver for our customers who want to move to the new unixODBC driver. PDO_ODBC is available in Zend Server version 2021.1 (coming soon), and in ZendPHP. The ibm_db2 and pdo_ibm PHP extensions will continue to be available for customers not wanting to make the switch. For those customers wanting to move to unixODBC, this article provides some tips that can help you get started.
Note: The unixODBC driver is 64-bit, so we can only provide PDO_ODBC in our 64-bit packages. Zend Server 2019 and earlier versions are 32-bit, so they do not support the driver.
STEP 1: Install the Open Source Package Management, if needed.
To use PDO_ODBC on IBM i, you must first download and install the unixODBC driver and driver manager from IBM i Access. This involves downloading the package from the IBM i Access Client Solutions download page, and installing using yum. A pre-requisite is to have Open Source Package Management installed on your IBM i, which provides the yum utility.
If you are using version 2020 or newer of Zend Server, or any version of ZendPHP, then you likely already have Open Source Package Management. If you don't have it, instructions to install it are here:
http://www-01.ibm.com/support/docview.wss?uid=nas8N1022619
STEP 2: Install the unixODBC driver and driver manager, if needed.
The unixODBC driver and driver manager are bundled with the ibm-iaccess.ppc64 package. You can check in the "Installed packages" tab of Open Source Package Management to see if it is installed. You can also check for it with this PASE command:
yum list ibm-iaccess
If you do not have the ibm-iaccess package, you can find the instructions to install the unixODBC driver and driver manager here:
https://ibmi-oss-docs.readthedocs.io/en/latest/odbc/installation.html
Tip: The rpm package for ibm-iaccess is not contained in a repository. So, after you unzip the download, go into the unzipped ppc64 directory and find the file with a name following this pattern: ibm-iaccess-V.R.M.F-S.ibmi7.2.ppc64.rpm . 'V.R.M.F' will be replaced by the actual version. For example, as this is being written, the current version is '1.1.0.15-0', so the file name is: ibm-iaccess-1.1.0.15-0.ibmi7.2.ppc64.rpm. Use FTP or Navigator to copy this file into your home directory in the IFS on your IBM i. Assuming your home directory is set up to be in your path, you should then be able to install the file using a PASE command like this:
yum install ibm-iaccess-1.1.0.15-0.ibmi7.2.ppc64.rpm
(The example shows the current version as of this writing. Your file may have a newer version.)
This installs the ibm-iaccess.ppc64 package, which includes the unixODBC driver and driver manager.
STEP 3: Install and enable the PDO_ODBC extension, if needed.
Important note: The unixODBC driver creates a linking failure in PHP if any extension using the old SQL-CLI (libdb400.a) driver is also loaded. This is because there is a naming conflict between the drivers that will cause the linked SQLSetEnvAttrr to be called from the wrong driver, and PHP will fail to start. So, before you can enable the PDO_ODBC extension, you need to disable the ibm_db2 and pdo_ibm extensions. For Zend Server, you also need to disable the odbc extension, as it uses libdb400.a to maintain backward compatibility with older versions of Zend Server.
Zend Server - enable
====================
If you are using Zend Server, the PDO_ODBC extension will be provided automatically starting in version 2021.1. You can enable and disable the extension using the Zend Server User Interface, just as you can do for any extension.
Before you can use the PDO_ODBC extension in any given version of PHP, you must first disable the odbc, pdo_ibm, and ibm_db2 extensions in that version of PHP. To do it, go into the Zend Server User Interface. Navigate to PHP -> phpinfo(), and make a note of the version currently in use. If you want to use a different version of PHP, please change it at this time.
Next, navigate to PHP -> Extensions, and select all three of these extensions: ibm_db2, odbc, and pdo_ibm (you can skip any if they are already off, but they are usually all on by default). With the extensions selected, click the Disable button at the top of the table. Restart Apache for the change to take effect, by using the restart icon (circular arrow at top right of the page, should be orange because restart is due).
Once the restart is completed, go into PHP -> phpinfo() and make sure none of these three extensions are loaded.
Once again, navigate to PHP -> Extensions. Select PDO_ODBC. With the extension selected, click the Enable button at the top of the table. Restart Apache for the change to take effect, by using the restart icon (circular arrow at top right of the page, should be orange because restart is due).
Once the restart is completed, go into PHP -> phpinfo() and make sure PDO_ODBC is loaded. You are now ready to begin using PDO_ODBC.
ZendPHP - install
=================
If you are using ZendPHP, you will need to install the extension for the version of PHP you are using. For example, if you are using version 7.3 of PHP, the extension can be installed with this PASE command:
yum install php73zend-php-odbc
To install the extension for a different version of PHP, just replace the '73' to your correct version. For example, for PHP 7.4, the package would be php74zend-php-odbc. It is important that the PHP version matches. If the PHP version does not match, the extension will not load.
You may also opt to use Open Source Package Management to install the extension for ZendPHP, since you already have the repository defined from the installation of ZendPHP.
STEP 4: Test your ODBC connection using the PDO_ODBC extension.
Here is a simple example script you can use to verify your ODBC connection is working (put in valid values for user and password):
<?php/* Connect to a local DB2 database using driver invocation */
echo "<pre>Test ODBC connection<br>";
ini_set("display_errors", 1);
$dsn = 'odbc:*LOCAL';
$user = 'user';
$password = 'password';
$db = new PDO($dsn, $user, $password);
if (!$db) die("Connection failed");
if ($db) echo "Connected<br>";
$library = "QGPL";
$sql = "select * from qsys2.systables where table_schema = '$library'";
$data=$db->query($sql,PDO::FETCH_ASSOC);
foreach ($data as $row) {
print $row['TABLE_NAME'] . "<br>";
}
?>