Skip to content

DB2 ODBC Connection

Jump to bottom
Yohanes Oktavianus Lumentut edited this page Jul 19, 2021 · 2 revisions

Install unixODBC

For connecting Rails application to the DB2 database, we need unixODBC, an open-source ODBC driver manager for UNIX.

sudo apt-get install unixodbc-dev

ODBC Configurations

  1. Find the Db2 instance path:
$ which db2
/home/joylove/sqllib/bin/db2
  1. Use unixODBC's odbcinst command to locate the odbcinst.ini file used to defined drivers:
$ odbcinst -j
unixODBC 2.3.4
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/joylove/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
  1. Add DB2 instance path to the driver key at configuration file /etc/odbcinst.ini by replacing /bin/db2 with /lib/libdb2.so 
$ sudo vi /etc/odbcinst.ini
[DB2]
Description=DB2 Driver
Driver=/home/joylove/sqllib/lib/libdb2.so
FileUsage=1
DontDLClose=1
  1. Add DSN name (equal to your Database Name) at /etc/odbc.ini 
$ sudo vi /etc/odbc.ini
[DSN NAME >> Your Database Name]
Description=Your DSN Description
Driver=DB2
Database=Your Database Name
Hostname=LOCALHOST
  1. Add DSN at ODBC CLI configuration file by getting the path from db2cli validate command
$ db2cli validate -dsn LIBTEST

===============================================================================
Client information for the current copy:
===============================================================================

Client Package Type       : IBM DB2 Enterprise Server Edition
Client Version (level/bit): DB2 v11.5.5.0 (s2011011400/64-bit)
Client Platform           : Linux/X8664
Install/Instance Path     : /opt/ibm/db2/V11.5              
DB2DSDRIVER_CFG_PATH value: <not-set>
db2dsdriver.cfg Path      : /home/joylove/sqllib/cfg/db2dsdriver.cfg
DB2CLIINIPATH value       : <not-set>
db2cli.ini Path           : /home/joylove/sqllib/cfg/db2cli.ini         <<<<< This is the path we are looking for       
db2diag.log Path          : /home/joylove/sqllib/db2dump/DIAG0000/db2diag.log

===============================================================================
db2dsdriver.cfg schema validation for the entire file:
===============================================================================

....

$ sudo vi /home/joylove/sqllib/cfg/db2cli.ini
[DSN NAME >> Your Database Name]
Database=Your Database Name
Servicename=56789
Port=0
uid=Your Instance Owner ID
pwd=Your Instance Owner Password
  1. Validate the DSN name again
$ db2cli validate -dsn LIBTEST

===============================================================================
Client information for the current copy:
===============================================================================

Client Package Type       : IBM DB2 Enterprise Server Edition
Client Version (level/bit): DB2 v11.5.5.0 (s2011011400/64-bit)
Client Platform           : Linux/X8664
Install/Instance Path     : /opt/ibm/db2/V11.5
DB2DSDRIVER_CFG_PATH value: <not-set>
db2dsdriver.cfg Path      : /home/joylove/sqllib/cfg/db2dsdriver.cfg
DB2CLIINIPATH value       : <not-set>
db2cli.ini Path           : /home/joylove/sqllib/cfg/db2cli.ini
db2diag.log Path          : /home/joylove/sqllib/db2dump/DIAG0000/db2diag.log

===============================================================================
db2dsdriver.cfg schema validation for the entire file:
===============================================================================

Note: The validation utility could not find the configuration file 
db2dsdriver.cfg. The file is searched at 
"/home/joylove/sqllib/cfg/db2dsdriver.cfg".

===============================================================================
db2cli.ini validation for data source name "LIBTEST":
===============================================================================

[ Keywords used for the connection ]

Keyword                   Value
---------------------------------------------------------------------------
DATABASE                  LIBTEST
SERVICENAME               56789
PORT                      0
UID                       JOYLOVE
PWD                       **********

===============================================================================
db2dsdriver.cfg validation for data source name "LIBTEST":
===============================================================================

Note: The validation utility could not find the configuration file 
db2dsdriver.cfg. The file is searched at 
"/home/joylove/sqllib/cfg/db2dsdriver.cfg".

===============================================================================
The validation is completed.
===============================================================================

Verify ODBC Connection

To make sure the connection configuration, please run isql command. If the configuration is OK, then you will get a Connected! message.

isql -v LIBTEST
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>
Clone this wiki locally