You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 5 Next »

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:

Getting started with Open Source Package Management in IBM i ACS


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:

IBM i Access ODBC Installation

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.

Zend Server - enable

If you are using Zend Server, the PDO_ODBC extension will be installed 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.

You need to disable the odbc, pdo_ibm, and ibm_db2 extensions.

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 odbc, pdo_ibm, and ibm_db2 extensions.  You need to disable the odbc extension because it uses SQL-CLI instead of unixODBC.  This was done to maintain backward compatibility with older versions of Zend Server.

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.  

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 odbc, pdo_ibm, and ibm_db2 extensions.  You need to disable the odbc extension because it uses SQL-CLI instead of unixODBC.  This was done to maintain backward compatibility with older versions of Zend Server.

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):

pdo_test.php
<?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>";
}
?>

The expected output is the heading, "Connected", and a list of files (tables) in the QGPL library (schema).


STEP 5: Begin to explore the available ODBC connection settings and PDO_ODBC attributes.

odbc.ini keywords and values

You can specify many more connection options in the odbc.ini file, which is here in the IFS:

/QOpenSys/etc/odbc.ini

The contents of odbc.ini will look something like this:

### IBM provided DSN - do not remove this line ###
[*LOCAL]
Description = Default IBM i local database
Driver      = IBM i Access ODBC Driver
System      = localhost
UserID      = *CURRENT
### Start of DSN customization
### End of DSN customization
### IBM provided DSN - do not remove this line ###

You will notice there is a section in odbc.ini with the heading [*LOCAL].  This is why we can just specify the connection string ($dsn) as 'odbc:*LOCAL'.  
You will also notice these comments:

### Start of DSN customization
### End of DSN customization

You can add any additional keywords and values here.  You will notice they have already provided values for the Description, Driver, System, and UserID values, and you can simply follow the pattern of Keyword = Value to add more in the customization section.  You can find the keywords and values here:

Connection string keywords

Refrerring to the table linked above, use the keywords labeled 'ODBC.INI' when you add them to the odbc.ini file.

Back up your odbc.ini file.

Remember to keep a copy of your customized odbc.ini file someplace safe. This file is part of a distributed package, so you may need to restore your changes after an upgrade or system restore.


PDO attributes

The construct, which is the "new PDO($dsn, $user, $password);" statement in the example script pdo_test.php, can contain an array of PDO attributes.  You can see a list of PDO attributes on the PDO::setAttribute documentation page here:

PHP: PDO::setAttribute 

For example, you can set up the error mode like this:

$db = new PDO($dsn, $user, $password, array(
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,));

Attributes can also be set using the setAttribute() method:

$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

The error mode attribute defines how errors are handled.  You can learn more about that here:

PHP: PDO Errors and error handling

Also notice you can set the user and password in the construct, overriding what is in odbc.ini. 

  • No labels