Configuration of DB2 CLI/ODBC Driver

IBM provides a CLI/ODBC driver for DB2 which is a light-weight shared library providing a basic ODBC API for accessing DB2. The driver is delivered in its binary form with shared libraries and header files, and it is available from IBM’s website. I recently downloaded the following version: ibm_data_server_driver_for_odbc_cli_linuxx64_v11.1.tar.gz. This post will cover setting up the driver for use with C++ programs. The SAMPLE database provided with DB2 will be used for testing out the connectivity.

DB2 installs in /opt/ibm on Linux, so I will install the driver to the same location and give ownership to the db2inst1 user which owns the instance of DB2 that is running on my system.

$   gzip -d ibm_data_server_driver_for_odbc_cli_linuxx64_v11.1.tar.gz
$   tar -xf ibm_data_server_driver_for_odbc_cli_linuxx64_v11.1.tar
$   sudo mv clidriver /opt/ibm/
$   sudo chown -R db2inst1 /opt/ibm/clidriver
$   sudo chgrp -R db2iadm1 /opt/ibm/clidriver

For each database that will be accessed via the CLI/ODBC library, a configuration for the libraries is required in the db2cli.ini file in the /opt/ibm/clidriver/cfg directory. Use the sample provided by IBM in the cfg directory to create the configuration.

$   cd /opt/ibm/clidriver/cfg
$   cp db2cli.ini.sample db2cli.ini
$   vim db2cli.ini

For example, to access the SAMPLE database provided with DB2, add the following configuration to db2cli.ini:

[SAMPLE]
Database=SAMPLE
Protocol=TCPIP
Port=50000
uid=db2inst1
pwd=db2inst1

If this is not done, there will be errors when calling SQLConnect() in C++ code later. Also make sure to copy db2cli.cfg to the instance user’s cfg directory.

$   su db2inst1
$   cp /opt/ibm/clidriver/cfg/db2cli.ini ~/sqllib/cfg/db2cli.ini

DB2 also provides a utility to validate the configuration file: db2cli with the validate option. Use this to make sure that db2cli is using the right configuration file, and that the tool can read the configuration for the SAMPLE database.

$   cd ~/sqllib/bin
$   ./db2cli validate -dsn SAMPLE

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

Client Package Type : IBM DB2 Express-C
Client Version (level/bit): DB2 v11.1.1.1 (s1612051900/64-bit)
Client Platform : Linux/X8664
Install/Instance Path : /opt/ibm/db2/V11.1_01
DB2DSDRIVER_CFG_PATH value: <not-set>
db2dsdriver.cfg Path : /home/db2inst1/sqllib/cfg/db2dsdriver.cfg
DB2CLIINIPATH value : <not-set>
db2cli.ini Path : /home/db2inst1/sqllib/cfg/db2cli.ini
db2diag.log Path : /home/db2inst1/sqllib/db2dump/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/db2inst1/sqllib/cfg/db2dsdriver.cfg”.

===============================================================================
db2cli.ini validation for data source name “SAMPLE”:
===============================================================================

[ Keywords used for the connection ]

Keyword Value
—————————————————————————
DATABASE SAMPLE
PROTOCOL TCPIP
PORT 50000
UID db2inst1
PWD ********

===============================================================================
db2dsdriver.cfg validation for data source name “SAMPLE”:
===============================================================================

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

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

Now it is possible to test out connection to the SAMPLE database via CLI/ODBC using the same tool. [Kudos to Easysoft for this tip!]

$   cd ~/sqllib/bin
$   db2 activate SAMPLE                  # set the active DB in the DB2 instance
$   echo “SELECT * from staff” | ./db2cli execsql -dsn SAMPLE
IBM DATABASE 2 Interactive CLI Sample Program
(C) COPYRIGHT International Business Machines Corp. 1993,1996
All Rights Reserved
Licensed Materials – Property of IBM
US Government Users Restricted Rights – Use, duplication or
disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
> SELECT * from staff
FetchAll: Columns: 7
ID NAME DEPT JOB YEARS SALARY COMM
10, Sanders, 20, Mgr , 7, 98357.50, –
20, Pernal, 20, Sales, 8, 78171.25, 612.45
30, Marenghi, 38, Mgr , 5, 77506.75, –
40, O’Brien, 38, Sales, 6, 78006.00, 846.55
50, Hanes, 15, Mgr , 10, 80659.80, –
60, Quigley, 38, Sales, -, 66808.30, 650.25
70, Rothman, 15, Sales, 7, 76502.83, 1152.00
80, James, 20, Clerk, -, 43504.60, 128.20
90, Koonitz, 42, Sales, 6, 38001.75, 1386.70
100, Plotz, 42, Mgr , 7, 78352.80, –
110, Ngan, 15, Clerk, 5, 42508.20, 206.60
120, Naughton, 38, Clerk, -, 42954.75, 180.00
130, Yamaguchi, 42, Clerk, 6, 40505.90, 75.60
140, Fraye, 51, Mgr , 6, 91150.00, –
150, Williams, 51, Sales, 6, 79456.50, 637.65
160, Molinare, 10, Mgr , 7, 82959.20, –
170, Kermisch, 15, Clerk, 4, 42258.50, 110.10
180, Abrahams, 38, Clerk, 3, 37009.75, 236.50
190, Sneider, 20, Clerk, 8, 34252.75, 126.50
200, Scoutten, 42, Clerk, -, 41508.60, 84.20
210, Lu, 10, Mgr , 10, 90010.00, –
220, Smith, 51, Sales, 7, 87654.50, 992.80
230, Lundquist, 51, Clerk, 3, 83369.80, 189.65
240, Daniels, 10, Mgr , 5, 79260.25, –
250, Wheeler, 51, Clerk, 6, 74460.00, 513.30
260, Jones, 10, Mgr , 12, 81234.00, –
270, Lea, 66, Mgr , 9, 88555.50, –
280, Wilson, 66, Sales, 9, 78674.50, 811.50
290, Quill, 84, Mgr , 10, 89818.00, –
300, Davis, 84, Sales, 5, 65454.50, 806.10
310, Graham, 66, Sales, 13, 71000.00, 200.30
320, Gonzales, 66, Sales, 4, 76858.20, 844.00
330, Burke, 66, Clerk, 1, 49988.00, 55.50
340, Edwards, 84, Sales, 7, 67844.00, 1285.00
350, Gafney, 84, Clerk, 5, 43030.50, 188.00
FetchAll: 35 rows fetched.
>

The connection is looking good!  Finally, when building C++ software that uses the driver, be sure to link to add the following compile and link options to g++ or clang++:

-I/opt/ibm/clidriver/include
-L/opt/ibm/clidriver/lib
-ldb2

The relevant include file is “sqlcli1.h” and the primary library of interest is libdb2.so.1.

 

 

Installing DB2 on Debian/Ubuntu

IBM’s DB2 product is delivered as a binary installer for the Linux platform in a tarball format. It would be nice if IBM produced rpm or deb packages for distributing DB2, but I will complain no further. The installer has gotten *much better* since when I first battled with installing DB2 on Linux back in 2006. After downloading the binary, unpack it and kick it off!

root#   mkdir -p /opt/ibm/db2expc_installer
root#   cp v11.1_linuxx64_expc.tar.gz /opt/ibm/db2expc_installer
root#   cd /opt/ibm/db2expc_installer
root#   tar -xfz v11.1_linuxx64_expc.tar.gz
root#   cd expc
root#   ./db2_installer

This failed of course, no binary delivered in this form ever works out of the box. This was good though, as I really wanted to run db2_setup instead of the installer. The setup program is a Java GUI that makes it very simple to go through the configuration, including creating user accounts required by the DB2 RDBMS.

 Requirement not matched for DB2 database “Server” . Version: “11.1.1.1”.

Summary of prerequisites that are not met on the current system:

DBT3514W The db2prereqcheck utility failed to find the following 32-bit library file: “/lib/i386-linux-gnu/libpam.so*”.
DBT3520E The db2prereqcheck utility could not find the library file libaio.so.1.
DBT3514W The db2prereqcheck utility failed to find the following 32-bit library file: “libstdc++.so.6”.

Ugh…32-bit binaries required.

root#   dpkg –add-architecture i386
root#   apt-get update
root#   apt-get install libaio1 libstdc++6:i386 libpam0g:i386

Now it is possible to run the installer, this time with the GUI to make the job easy.

root#  ./db2setup

DBI1190I  db2setup is preparing the DB2 Setup wizard which will guide
you through th6:ie program setup process. Please wait.

The GUI setup program holds one’s hand the whole way, allowing one to setup db2inst1 and db2fenc1 users. After the installation is complete, I ran visudo to add my user account to the sudoers file for DB2 management:

bryan ALL=(db2inst1) /opt/ibm/db2/V11.1_01/bin/*
bryan ALL=(db2fenc1) /opt/ibn/db2/V11.1_01/bin/*

Finally, I usually like to create the SAMPLE database so that I can test out SQL commands and other IBM tools on a test database that actually has a good amount of data and tables.

db2inst1$   ./db2sampl

Creating database “SAMPLE”…
Connecting to database “SAMPLE”…
Creating tables and data in schema “DB2INST1″…
Creating tables with XML columns and XML data in schema “DB2INST1″…

‘db2sampl’ processing complete.

Now to install the CLI/ODBC library for C++ programs, and maybe IBM Data Studio…