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.
The Global Engineer's Notebook 