Using Yum to Install or Update the IBM i ODBC Driver
The IBM i ODBC driver for Db2 has become easier to install.
Formerly available only as a download requiring several steps, the driver can now be installed and updated on IBM i with Yum or Access Client Solutions (ACS), as easily as with other IBM i open source packages.
Here are instructions for installing the ODBC driver using Yum.
Before you begin
A few prerequisites must be set up before you install the ODBC driver using Yum.
- IBM i 7.3 or higher
- Open source environment must be installed
- If installing via command-line terminal, set the open source PATH
Update certificates, Yum to get new repository
The ODBC driver comes from a new repository named ibmi-release. A one-time update to ca-certificates-mozilla and yum will make this repository available to ACS and Yum.
To update these two packages from a terminal such as SSH or CALL QP2TERM, run this command:
yum update ca-certificates-mozilla yum
or update them individually via ACS Open Source Package Management’s “Updates available” tab:
Yum shown in ACS Installed Packages
Now install or update the ODBC driver
After the updates to ca-certificates-mozilla and yum, the ODBC driver will be available under the name ibm-iaccess.
To install from a terminal, run this command:
yum install ibm-iaccess
To update:
yum update ibm-iaccess
or use ACS. Once installed, the ibm-iaccess package will become visible in the ACS “Installed Packages” window:
ODBC driver application listed as ibm-iaccess in Installed Packages of ACS
Confirm that ODBC is installed and working
Test your ODBC installation by running the isql command, included with the open source components unixODBC and unixODBC-devel. Make sure these two components are installed using ACS or by running the following command, as before:
yum install unixODBC unixODBC-devel
Now you can test ODBC.
Connect to your IBM i database with the isql command:
/QOpenSys/pkgs/bin/isql -v *LOCAL
You should see a “Connected!” message followed by an SQL prompt:
|
1 2 3 4 5 6 7 8 9 |
+---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> |
Type this SQL statement and see the query results below it:
select * from qiws.qcustcdt
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
SQL> select * from qiws.qcustcdt +---------+---------+-----+--------------+-------+------+--------+-------+-------+---------+---------+ | CUSNUM | LSTNAM | INIT| STREET | CITY | STATE| ZIPCOD | CDTLMT| CHGCOD| BALDUE | CDTDUE | +---------+---------+-----+--------------+-------+------+--------+-------+-------+---------+---------+ | 938472 | Henning | G K | 4859 Elm Ave | Dallas| TX | 75217 | 5000 | 3 | 37.00 | 0 | | 839283 | Jones | B D | 21B NW 135 St| Clay | NY | 13041 | 400 | 1 | 100.00 | 0 | | 392859 | Vine | S S | PO Box 79 | Broton| VT | 5046 | 700 | 1 | 439.00 | 0 | | 938485 | Johnson | J A | 3 Alpine Way | Helen | GA | 30545 | 9999 | 2 | 3987.50 | 33.50 | | 397267 | Tyron | W E | 13 Myrtle Dr | Hector| NY | 14841 | 1000 | 1 | 0 | 0 | | 389572 | Stevens | K L | 208 Snow Pass| Denver| CO | 80226 | 400 | 1 | 58.75 | 1.50 | | 846283 | Alison | J S | 787 Lake Dr | Isle | MN | 56342 | 5000 | 3 | 10.00 | 0 | | 475938 | Doe | J W | 59 Archer Rd | Sutter| CA | 95685 | 700 | 2 | 250.00 | 100.00 | | 693829 | Thomas | A N | 3 Dove Circle| Casper| WY | 82609 | 9999 | 2 | 0 | 0 | | 593029 | Williams| E D | 485 SE 2 Ave | Dallas| TX | 75218 | 200 | 1 | 25.00 | 0 | | 192837 | Lee | F L | 5963 Oak St | Hector| NY | 14841 | 700 | 2 | 489.50 | .50 | | 583990 | Abraham | M T | 392 Mill St | Isle | MN | 56342 | 9999 | 3 | 500.00 | 0 | +---------+---------+-----+--------------+-------+------+--------+-------+-------+---------+---------+ SQLRowCount returns -1 12 rows fetched SQL> |
Now exit the isql environment using the quit command:
SQL> quit
Your ODBC installation or upgrade is complete
Use this method to update your ODBC driver as IBM releases new versions, or for hassle-free installation on new IBM i systems. Ever since IBM made ODBC available on their new repository, we have used the Yum installation technique exclusively on all our client systems. It saves a lot of time.
This seems to work. How do you use this to process thru the data returned and do something with it? Like make a csv or something….Moving this out of the shell and into a .py program? Any examples for that?
Ken, I emailed you my slide deck on ODBC, which includes a Python example.
Hello Alan,
I updated Yum to the current version using ACS but ibm-iaccess is not in the list to install.
What do you suggest?
Thanks,
Ken
Ken, you’re on IBM i 7.3 or higher, right?
Run
/qopensys/pkgs/bin/yum repolist
from SSH, QSH, or CALL QP2TERM
and look for the repo:
ibmi-release.
Do you see ibmi-release?
You may need to run
/qopensys/pkgs/bin/yum clean all
as well.
Alan, 7.3 TR10. Ran both commands. Do not see ibmi-release. I do see ibm. I get this message when I run /qopensys/pkgs/bin/yum repolist
[Errno 14] curl#60 – “SSL certificate problem: self signed certificate in certificate chain”. Not sure if that is causing a problem. It does try other mirror and succeed. Do I need to install something else?
Thanks, Ken
Hi, Ken, we discussed offline and found that you needed to update ca-certificates-mozilla. I’ve added this update in the article text.
Useful info Alan but with one small problem for me. I had previously used the old System i Access ODBC driver. Although all the instructions provided work as expected – when it comes to exiting SQL I get the error message:
SQL> exit
[37000][IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0199 – Keyword EXIT not expected. Valid tokens: ( CL END GET SET TAG CALL DROP FREE HOLD LOCK OPEN WITH ALTER.
[ISQL]ERROR: Could not SQLPrepare
To get out of SQL I have to type “quit”. So why am I connection to the old version? I started SQL with the command you gave (i.e. /QOpenSys/pkgs/bin/isql -v *LOCAL). Perhaps more importantly how do I get rid of the old one.
Jon, the
exitwas a typo on my part. I’ve corrected it to bequit. Thanks for your comment! Glad the instructions were helpful!