IBM Support

HOWTO: Setup ODBC application connectivity on Linux

Question/Answer


Question

How to setup ODBC application connectivity on Linux using the IBM Data Server Driver and unixODBC?

Cause

There are no step by step instructions regarding how to install and configure the ODBC connectivity for an application.

Answer


This example assumes the application requiring ODBC connectivity will be run with the userid "db2inst1" and will connect to:

Database name: SAMPLE
Database hostname: test.ibm.com
Database port #: 50000

Root id will be required to configure the ODBC driver manager configuration files and the IBM Data Server (DS) Driver if it is installed in /opt/ibm/db2/dsdriver as per our example. The DS Driver can be installed in the user's home directory as well such as /home/db2inst1/dsdriver.


1) Install & Configure ODBC Driver Manager

(a) Install the ODBC Driver Manager as per link below:

http://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.apdv.cli.doc/doc/t0061216.html?lang=en

For step #3 in the link above, run the "odbcinst -j" command to find the location of the configuration files for SYSTEM/USER DATA SOURCES. For User Data Sources, it will look for a hidden file in the current user's home directory called .odbc.ini.

Get location of odbcinst executable:

/home/db2inst1: which odbcinst
/usr/local/bin/odbcinst


Get ODBC configuration file locations and make note of the System and User Data Sources file locations.

/home/db2inst1: odbcinst -j
unixODBC 2.3.0
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /home/db2inst1/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8


(b) Later, we will create and configure the System and User DSN files (odbc.ini and .odbc.ini) in Step #4


2) Setup the ODBC environment.

Edit .bashrc and add the lines below:

# Source DB2 profile from IBM Data Server Driver
. /opt/ibm/db2/dsdriver/db2profile

# Used for ODBC connectivity
export LD_LIBRARY_PATH=/usr/lib64/libodbc.so
export ODBCINI=/usr/local/etc/odbc.ini

Ensure /opt/ibm/db2/dsdriver/db2profile has permission to execute db2profile.

# chmod 755 /opt/ibm/db2/dsdriver/db2profile

The ODBC libraries are usually located in the path below:

/usr/lib64/libodbc.so (64-bit)
/usr/lib/libodbc.so (32-bit)

After editing .bashrc, source the profile again or exit and login again for the new environment variables to take effect.

/home/db2inst1: . ./.bashrc

Reference: Please refer to link "Setting Up ODBC Environment"


3) Install IBM Data Server (DS) Driver .

(a) Select "Data Server Driver Package" for the DB2 release.
http://www-01.ibm.com/support/docview.wss?uid=swg27016878

(b) Select the platform and download "IBM Data Server Driver Package".

(c) Install the DS driver, in this example we install it in /opt/ibm/db2/dsdriver.

(d) Create and configure db2dsdriver.cfg

cd /opt/ibm/db2/dsdriver
touch db2dsdriver.cfg

Edit db2dsdriver.cfg and place the following inside it. There are two entries for each database entry. In our example the remote DB2 database server is located on test.ibm.com which is listening on port 50000. The database name is SAMPLE.

<configuration>

<dsncollection>

<!-- Both DSN alias point to the same database called SAMPLE on test.ibm.com:50000-->

<!-- 64-bit DSN alias -->
<dsn alias="SAMPLE" name="SAMPLE" host="test.ibm.com" port="50000"> </dsn>

<!-- 32-bit DSN alias-->
<dsn alias="SAMPLE32" name="SAMPLE" host="test.ibm.com" port="50000"> </dsn>
</dsncollection>


<databases>
<database name="SAMPLE" host="test.ibm.com" port="50000">
</database>
</databases>
</configuration>


Note: There is a sample file called <DS Driver install path>/cfg/db2dsdriver.cfg.sample for reference.


(4) Configure ODBC, System and User Data Sources files.

The locations for odbcinst.ini and .odbc.ini are output from the "odbcinst -j" from step #1.

(a) Edit the ODBC driver manager configuration file (/usr/local/etc/odbcinst.ini) to point to the location of the DS Driver

[DB2]
Description = DB2 Driver
Driver = /opt/ibm/db2/dsdriver/lib/libdb2o.so
fileusage=1
dontdlclose=1

(b) Create the System Data Sources file. Root is required if the DS driver was installed in /opt/ibm/db2/dsdriver.

# touch /usr/local/etc/odbc.ini

Edit the file.

[SAMPLE]
Driver=/opt/ibm/db2/dsdriver/lib/libdb2o.so
Description=Sample DB2 ODBC Database

Our DSN is called "SAMPLE" which is the name of the database.


(c) OPTIONAL: If the SAMPLE database is not found in the System DSN, it will search for the User Data Source file. Thus this step is optional if the System DSN already contains the database name. For our purposes the User DSN's contents are identical to the System DSN.

Create the User Data Sources file.

/home/db2inst1: touch .odbc.ini

Edit the file /home/db2inst1/.odbc.ini

[SAMPLE] Driver=/opt/ibm/db2/dsdriver/lib64/libdb2.so Description=Sample 64-bit DB2 ODBC Database Optional: Add 32-bit DSN for 32-bit application [SAMPLE32] Driver=/opt/ibm/db2/dsdriver/lib32/libdb2.so Description=Sample 32-bit DB2 ODBC Database


5) Test the connectivity via "isql" to confirm the ODBC has been setup correctly and can connect to the remote database SAMPLE.

$isql -v SAMPLE <userid> <password>


/home/dbinst1: isql -v SAMPLE db2inst1 123456
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> select count(*) from sysibm.systables;
+------------+
| 1 |
+------------+
| 621 |
+------------+
SQLRowCount returns -1
1 rows fetched
SQL> quit

Testing 32-bit DSN
By default isql is a 64-bit binary which cannot access 32-bit libdb2.so. Attempting to run it against a 32-bit ODBC DSN will return the error below:

[01000][unixODBC][Driver Manager]Can't open lib '/home/db2inst1/sqllib/lib32/libdb2.so' : file not found
[ISQL]ERROR: Could not SQLConnect

Attached is the 32-bit binary isql32 for testing.

isql32
isql32

Alternative: Get source code and re-compile as 32-bit

a) Download source code from http://www.unixodbc.org/

b) tar -xzvf unixODBC*.tar.gz

c) Set gcc flags for 32-bit
export CFLAGS=-m32
export LDFLAGS=-m32
export CPPFLAGS=-m32

d) Configure Make files so compiled binaries are placed in /tmp/unixODBC32 so they do not overwrite current 64-bit files.

cd unixODBC*
mkdir /tmp/unixODBC32
./configure --prefix=/tmp/unixODBC32

e) make install

f) /tmp/unixODBC32/exe contains 32-bit version of isql

file isql
isql: ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.18, not stripped



6) Test connectivity via IBM DS Driver.

/opt/ibm/db2/dsdriver/bin: ./db2cli validate -dsn SAMPLE -connect -user db2inst1 -passwd 123456

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

Client Package Type : IBM Data Server Driver Package
Client Version (level/bit): DB2 v10.5.0.4 (s140804/64-bit)
Client Platform : Linux/X8664
Install/Instance Path : /opt/ibm/db2/dsdriver/.
DB2DSDRIVER_CFG_PATH value: <not-set>
db2dsdriver.cfg Path : /opt/ibm/db2/dsdriver/./cfg/db2dsdriver.cfg
DB2CLIINIPATH value : <not-set>
db2cli.ini Path : /opt/ibm/db2/dsdriver/./cfg/db2cli.ini
db2diag.log Path : /opt/ibm/db2/dsdriver/./db2dump/db2diag.log

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

Success: The schema validation completed successfully without any errors.

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

Note: The validation utility could not find the configuration file db2cli.ini.
The file is searched at "/opt/ibm/db2/dsdriver/./cfg/db2cli.ini".

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

[ Parameters used for the connection ]

Keywords Valid For Value
--------------------------------------------------------------------
DATABASE CLI,.NET,ESQL SAMPLE
HOSTNAME CLI,.NET,ESQL test.ibm.com
PORT CLI,.NET,ESQL 50000

================================
Connection attempt for data source name "SAMPLE":
================================

[SUCCESS]

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



7) Try establishing a connection via your ODBC application.


Troubleshooting

(a) isql fails to connect and there are no SQLCODES returned.

/home/db2inst1: isql -v SAMPLE db2inst1 123456
[IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified

Ensure the System and User DSNs point to the same ODBC driver.

libdb2o.so: 64-bit applications
libdb2.so: 32-bit applications.

System DSN: /usr/local/etc/odbc.ini

User DSN (if applicable): /home/ <user id>/.odbc.ini

(b) If an SQLCODE is returned please refer to the link "SQL codes" for more information regarding the message.

(c) The SQLCODE -30082 or SQL30082 was returned by isql (incorrect userid or password)

If "isql" can connect successfully using the same userid and password, then
check the following files to ensure "uid" and "pwd" have not been hard coded into the configuration files below.

/opt/ibm/db2/dsdriver/cfg/db2cli.ini (if applicable)

Please restart your application after making any changes to the files above.

(d) If testing connectivity via IBM DS driver in Step #6 fails with

Failure: Internal error while loading xml4c library. Validation cannot continue further. Contact IBM technical support to rectify the issue.

Ensure the user id running the command has sourced the db2profile, also ensure the .bashrc points to the correct location of the installation path for the IBM DS Driver.

. /opt/ibm/db2/dsdriver/db2profile

(e) If isql hangs, check db2cli.ini configuration to see if there is a hostname, id or password setting which would override the parameters sent to isql.

(f) Bit mismatch between application and driver specified in odbc.ini. Example: 64-bit application like isql will return "Can't find /home/db2inst1/sqllib/lib32/libdb2.so" if odbc.ini points to 32-bit driver.

Related information

Setting Up ODBC Environment
Installing the unixODBC driver manager
SQL codes

Document information

More support for: DB2 for Linux, UNIX and Windows

Component: Operating System / Hardware - OS Configuration

Software version: 9.5, 9.7, 10.1, 10.5, 11.1

Operating system(s): Linux

Reference #: 1692171

Modified date: 06 June 2019