The New ODBC Driver for IBM i
IBMer Mark Irish explains what ODBC is, why you would want to use it and how to install the driver on your system.
The IBM i Open-Source Software team recently ported the IBM i Access ODBC Driver to work directly on IBM i, where previously it had only been compiled for Windows and Linux systems. This meant that prior to this delivery, you could develop communicate with Db2 on i with a Windows or Linux machine using ODBC, but couldn’t transfer the code to IBM i systems as they only had access to CLI (Call Level Interface) drivers. The delivery of a Db2 for i ODBC driver for IBM i enables you to create applications using open-source technology against Db2 on i on your development machine, and then transfer those applications to your IBM i system when they are ready for production.
Being new, there some uncertainty among IBM i users as to how to install the ODBC driver on IBM i. This article will briefly describe what ODBC is and why you would want to use it, before covering the steps you need to take to install the driver on your system, how to define connection options both through DSNs and connection strings, and how to use ODBC once you have everything configured.
What is ODBC?
Before we explore how to install the ODBC driver on IBM i, it is important to know what ODBC actually is. ODBC stands for Open Database Connectivity and is a standardized API for accessing database management systems (DBMSs). With ODBC, DBMS agnostic functions send data from an ODBC driver manager to an ODBC driver that targets a particular DBMS, in this case Db2 for i. In theory, this allows you to write applications targeting multiple DBMSs through multiple drivers, as long as you ensure the correct query syntax for the right DBMS. Because most IBM i users are only targeting Db2 on i, ODBC is more useful for creating a standard interface that can be used from multiple operating systems, allowing programs to be developed on one machine or operating system and then ported to another. No longer do you have to write a set of code for connecting to Db2 on i from Windows and Linux, and another set of code for connecting directly from IBM i.
To use ODBC, you must have an ODBC driver manager for your operating system and an ODBC driver that targets the DBMS you wish to interact with. For Windows users, an ODBC driver manager comes pre-installed with the operating system. For use with Linux distributions and IBM i, you must download a driver manager called unixODBC. No matter how you get your driver manager, you will also need a driver for your targeted DBMS, which for Db2 on i can be obtained from IBM. Step-by-step instructions for downloading the driver manager and driver on IBM i are outlined below.
Why should you use ODBC?There are many reasons why you should consider using ODBC as your primary means of creating connections from both your development machine and from IBM i itself:
- Because ODBC is a technology that is used for more than just IBM i, there are many applications and technologies that are already enabled to use ODBC. Nearly all open-source programming languages (and many non-open-source languages) have some way to connect to databases through an ODBC interface, facilitating interaction with any database that has an ODBC driver (including IBM i).
- Similarly, because ODBC connectors have already been developed for so many languages and frameworks, the IBM i Open-Source Software Team doesn’t have to spend time creating specific Db2 for i connectors for every new technology we deliver on the platform. This means that we can spend more time delivering new software for you and pushing what is possible on IBM i. In the future, most of the packages we develop will require that you use ODBC connections.
- As already mentioned, ODBC is useful if you want to connect to Db2 on i from off-system. Unlike CLI-based connectors, which can only be built on IBM i, ODBC connections can be created from Windows and Linux machines as well. This means that you can develop your applications on one system and then move them to the IBM i when you are ready to deploy them. It also means that you can have the same application running on multiple different platforms that can all communicate with Db2 on i in the same way.
- Finally, there are many more connection options available for ODBC than on CLI. When you create an ODBC connection through a DSN or a connection string, there are approximately 70 different connection options that can be set. This includes everything from specifying the system, your username, or your password, to defining default libraries and schemas or whether or not stored procedures can be called. A full list of options can be found on the “Connection string keywords” page of the 7.4 documentation.
How to Install the ODBC Driver on IBM i
1. Install the Open-Source Software Environment
The first thing you will need to do is ensure that you have the open-source environment installed on your IBM i. The quickest way to do this is to check that you have yum installed on your system: it should be located in the IFS at /QOpenSys/pkgs/bin/yum. If you are using a terminal and have /QOpenSys/pkgs/bin on your PATH, you can run:
You should see that location on the screen. If not, you will have to install the open-source software environment by following the instructions on our guide to installing yum.
2. Install the ODBC Driver Manager
The ODBC driver manager used on IBM i is called unixODBC. It is the same driver manager that is used on most Linux distributions, so if you have set up ODBC there in the past, these instructions should seem familiar.
To get unixODBC, we are going to install it with yum, our package manager for installing open-source software. We are also going to need to download an extension package called unixODBC-devel that allows us to write applications using ODBC. To install both of these packages in one command, run:
yum install unixODBC unixODBC-devel
You will be shown that yum will install the two packages, which will take about 1 MB on the system. You will have to confirm the download by pressing the ‘y’ key on your keyboard, after which the packages will download and be installed on your system.
3. Install the IBM i Access ODBC Driver for IBM i
Once you have the driver manager installed, you need to install the actual driver that will allow ODBC to talk to Db2 on i. Downloading the driver is a multistage process, but is fairly straightforward.
- Go to this IBM i Access – Client Solutions webpage.
- Click Downloads for IBM i Access Client Solutions
- When prompted, log in with your IBMid (or create one if you don’t have one)
- Confirm that you agree to the license when redirected
- On Downloads page, select the Download using http tab
- Scroll down, and next to ACS PASE App Pkg, select Download now
Once you have the .zip file, you can transfer it to the IFS of your IBM i system. Because there are at least a dozen ways to transfer files to and from IBM i, I will assume that you already know how to do this part. If not, consider using the Integrated File System utility in Access Client Solutions.
With the zip file on your system, you can install unzip through yum (if you don’t already have it):
yum install unzip
You will have to confirm installation with ‘y’ when prompted. Once unzip is installed, you can run the unzip command on the .zip file that you transferred over:
This will extract instructions and a directory named 'ppc64' that are compressed inside the .zip. The RPM can be found inside the ppc64 directory, and once you have it on IBM i in the IFS, use yum to install it:
yum install ibm-iaccess-220.127.116.11-0.ibmi7.2.ppc64.rpm
This article deals exclusively with ODBC setup on IBM i. For setup instructions for Windows and Linux machines, please see our guide on ODBC setup for IBM i development.
Like installing unixODBC, you will be prompted to enter “y” to confirm the installation. When you confirm, it will install the IBM i Access ODBC Driver onto your system, as well as automatically add the driver to your list of drivers in odbcinst.ini and create a default DSN in odbc.ini for connecting to the local Db2 database. Let’s explore those more in detail.
Configuring ODBC Drivers and DSNs on IBM i
Just having the IBM i Access ODBC Driver on your system isn’t enough to use ODBC. You also need to make sure that you have your unixODBC configuration files set up correctly so that it knows how to make connections with your driver. Luckily, when you install the ibmi-iaccess RPM, it automatically configures everything you need to use your local Db2 database, but it is still important to understand the configuration files.
This file defines the ODBC drivers that you have installed on your system, and is available to all users on that system.
When you install the ibm-iaccess RPM on the system, it automatically adds the IBM i Access ODBC Driver to odbcinst.ini:
[IBM i Access ODBC Driver]
Description=IBM i Access ODBC Driver
odbc.ini and .odbc.ini
Where odbcinst.ini defines drivers, odbc.ini and .odbc.ini (note the preceding ‘.’ in the name) define datasource names (DSNs). A DSN is a set of connection options that can be used by ODBC to connect your database, and often includes information like which driver to use, the system to connect to, and the user ID and potentially password to use to connect. When you use a connection string in your ODBC-enabled applications, you can simply specify the DSN to use, and all connection options in that DSN will be added to the connection. These files define DSNs in the format:
Description=Description of the DSN
A list of all valid DSN options can be found on the “Connection string keywords” page of the 7.4 documentation. If defining options in a DSN, but sure to use the ODBC.INI key for each option.
Note that the value defined by the Driver key must match the name of a driver defined in odbcinst.ini.
When you install the ibm-iaccess RPM on the system, it automatically adds a DSN named *LOCAL to your odbc.ini:
### IBM provided DSN - do not remove this line ###
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 can use this DSN to connect to the local Db2 database on your IBM i system. The UserID of *CURRENT indicates to use the credentials of the user who is running whatever job is trying to create the ODBC connection. Note that the use of *CURRENT requires PTFs to be applied for IBM i 7.2 and 7.3 (the fixes come as part of 7.4). Those PTFs are:
unixODBC installs odbc.ini in the same IFS location as odbcinst.ini, which again is /QOpenSys/etc/ by default. Its location can also be checked if you run:
By contrast, a .odbc.ini file can be created in a user’s home directory in the IFS (usually something like /home/USERNAME).
Although these files look the same internally, there is one key difference: DSNs defined in odbc.ini are available to all users on the system, while those defined in .odbc.ini are available only to the user who owns the file (the user whose home directory it exists in). This makes .odbc.ini a great place for defining DSNs with the user ID and password for connecting to your system or other IBM i systems.
Using ODBC on IBM i
Once you have installed both the driver manager and driver and setup your configuration files (or wish to use the default ones installed with the IBM i Access ODBC Driver), it is incredibly simple to create a connection using an application that uses ODBC. When passing a connection string, simply pass the name of the DSN like so:
The application will search through your odbc.ini and .odbcini for a DSN of *LOCAL, then use all the connection options defined therein to create the connection. Because *LOCAL uses the UserID of *CURRENT, you don’t have to specify any user credentials. Similarly, if you create a DSN with UserID and Password defined, you also do not have to explicitly pass sensitive information in the connection string.
You can also extend the options defined in the DSN by appending additional connection options after the DSN , like so:
If you don’t want to use a DSN, you can pass in the driver name and additional options through the connection string, but you will lose the power of using a DSN for defining common connection string options:
“DRIVER=IBM i Access ODBC Driver;SYSTEM=localhost;UID=MIRSH;PWD=abc123def
A list of all valid connection string options can be found on the “Connection string keywords” page of the 7.4 documentation. If defining options in a connection string, but sure to use the Connection String key for each option.
ODBC: Open(-Source) Database Connectivity
By using ODBC with Db2 on i, you make it much easier to port your applications from a development machine to a production environment. This can best be seen with open-source software, because it is often 100% portable from a development machine to IBM i if the same technology is available on both systems. Prior to the release of the IBM i Access ODBC Driver for IBM i, you could move your application to your IBM i system from Windows or Linux, but you would have to change your code to use a CLI-based connector instead of an ODBC-based one, which often had completely different APIs. Now, the only change that has to be made when code is ported is potentially changing a connection string to point to a local DSN.
Although it may seem like there is a lot of steps to get ODBC configured, most of the work is actually done for you with yum and RPMs. Furthermore, after you do the setup once, you won’t have to do it again unless you wanted to define additional DSNs or change options on existing DSNs. As noted there are over 70 connection options available for you on the driver, which allows you to create connections that can be tailored for your needs, no matter how specific.
We on the IBM i Open-Source Software Team are excited by the potential this driver brings, and we hope that you feel it is as useful as we do. If you want to share your thoughts, feel free to reach out on Twitter with #IBMiOSS, or connect directly with @markdirish, @kadler_ibm, or @IBMJesseG. If you have any issues, you can post them to https://bitbucket.org/ibmi/opensource/issues.