IBM Support

ODBC driver for the IBM i PASE environment

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 several locations:
The driver package has dependencies on open source packages:
  • GNU C Library
  • Red Hat Package Manager (rpm)
  •  unixODBC
For details regarding installing open source packages on the IBM i, please see:
Getting started with Open Source Package Management in IBM i ACS
If manually installing from downloaded file "IBMiAccess_v1r1_PASE_AP.zip", extract the .rpm file from the archive and copy it to any directory on the IBM i. Next, install with (version numbers may have changed):
     $ yum install ibm-iaccess-1.1.0.26-0.ibmi7.3.ppc64.rpm
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 (cwbmediccwbping, 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:
 
[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.

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"}]

Document Information

Modified date:
05 August 2022

UID

ibm10885929