How To
Summary
The 64-bit ODBC driver for the IBM i PASE environment is now available.
Objective
This driver will allow applications making 64-bit ODBC API calls to access DB2 for IBM i
Environment
PASE and QSH environments on the IBM i
Steps
The 64-bit ODBC driver for the IBM i PASE environment is available in the 'ibm-iaccess' RPM package and can be installed from the Yum package manager. For instance:
/QOpenSys/pkgs/bin/yum install ibm-iaccess
More information about installing on IBM i and other platforms can be found on the community open source documentation site.
Once installed, you can check the installed status and version with:
marquis@myIBMiName:~$ rpm -qi ibm-iaccess
Name : ibm-iaccess
Version : 1.1.0.26
Release : 0
Architecture: ppc64
Install Date: Tue Jun 14 17:06:30 2022
Group : Unspecified
Size : 45398905
License : IPLA
Signature : (none)
Source RPM : ibm-iaccess-1.1.0.26-0.src.rpm
Once the dependencies and the driver are installed, execute "odbcinst -j" to view the location of the unixODBC configuration files:
marquis@myIBMiName:ppc64$ odbcinst -j
unixODBC 2.3.9
DRIVERS............: /QOpenSys/etc/odbcinst.ini
SYSTEM DATA SOURCES: /QOpenSys/etc/odbc.ini
FILE DATA SOURCES..: /QOpenSys/etc/ODBCDataSources
USER DATA SOURCES..: /home/marquis/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
marquis@myIBMiName:ppc64$
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 ###
[*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 ###
Test the ODBC connection using the "isql" tool (part of the unixODBC product) and an SQL statement:
marquis@myIBMiName:~$ isql *LOCAL
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> select * from qiws.qcustcdt
+---------+---------+-----+--------------+-------+------+--------+-------+-------+---------+---------+
| CUSNUM | LSTNAM | INIT| STREET | CITY | STATE| ZIPCOD | CDTLMT| CHGCOD| BALDUE | CDTDUE |
+---------+---------+-----+--------------+-------+------+--------+-------+-------+---------+---------+
| 938472 | Stilt | 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 | Stilt | 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>
The "*CURRENT" USRPRF is used as the security context of the ODBC connection. However, non-"*LOCAL" connections would require user ID and password.
Additional Information
Note: This driver is 64-bit only and the driver name has changed from the name used in the Linux driver:
IBM i Access ODBC Driver 64-bit
...to:
IBM i Access ODBC Driver
64-bit odbc.ini DSNs from the Linux product (and DSN-less connections) will need to be updated to reflect the new name.
This product shares many similarities with the IBM i Access Client Solutions - Application Package for Linux and the same utilities (cwbmedic, cwbping, and cwbtrc) are available within directory /QOpenSys/pkgs/bin/ . View the complete file list with:
$ rpm -ql --filesbypkg ibm-iaccess
Troubleshooting:
An SQL.log trace is enabled by adding the following section to the end of the odbcinst.ini file:
The ODBC application to be traced must be started after you add the ODBC trace section to the odbcinst.ini file. If you are tracing an Apache application, HTTPd must be restarted for the change to take effect.
Be sure to set "Trace = No" when finished.
[ODBC]
Trace = Yes
TraceFile = /tmp/sql.log
ForceTrace = No
Pooling = No
The ODBC application to be traced must be started after you add the ODBC trace section to the odbcinst.ini file. If you are tracing an Apache application, HTTPd must be restarted for the change to take effect.
Be sure to set "Trace = No" when finished.
A "cwbtrc" trace of the driver is gathered with:
$ /QOpenSys/pkgs/bin/cwbtrc /DT:1
Note the trace status and output file location
--Recreate the issue--
$ /QOpenSys/pkgs/bin/cwbtrc /DT:0
To gather the trace output files and installation details for the Application Package, execute:
$ /QOpenSys/pkgs/bin/cwbmedic
...and submit the archive to IBM i support.
Related Information
Document Location
Worldwide
[{"Type":"MASTER","Line of Business":{"code":"LOB57","label":"Power"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z0000001gDAAAY","label":"Data Access-\u003EODBC"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions"}]
Was this topic helpful?
Document Information
Modified date:
26 August 2022
UID
ibm10885929