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.
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++:
The relevant include file is “sqlcli1.h” and the primary library of interest is libdb2.so.1.
Keith Kim
And need to set up a couple of things. Run client:
$ db2
3.1. Create sample database:
db2 => create database sample
DB20000I The CREATE DATABASE command completed successfully.
3.2. Set up port 50000:
db2 => UPDATE DBM CFG USING SVCENAME 50000
DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.
SQL1362W One or more of the parameters submitted for immediate modification
were not changed dynamically. Client changes will not be effective until the
next time the application is started or the TERMINATE command has been issued.
Server changes will not be effective until the next DB2START command.
$ db2 get dbm cfg | grep SVCENAME
TCP/IP Service name (SVCENAME) =
SSL service name (SSL_SVCENAME) =
$ db2set DB2COMM=tcpip
$ db2set -all
[i] DB2COMM=TCPIP
[g] DB2_COMPATIBILITY_VECTOR=MYS
To stop and start again and test
$ db2stop
SQL1064N DB2STOP processing was successful.
$ db2start
$ db2 connect to sample user kkim using
Other helpful commands, for diagnostic output:
4. JDBC
Make sure the port is defined in /etc/services file, if not add the line:
# vi /etc/services
db2c_db2inst1 50000/tcp # db2
JDBC String,
jdbc:db2://myhost:50000/sample
Optional-Static IP setting in Ubuntu 18
$ sudo vi /etc/netplan/50-cloud-init.yaml
after editing it appropriately,
References
DB GUI clients
AFFORDABLE WEBGUI HOSTING
THE BEST SITE BUILDER EVER SEEN. AND IT IS FREE! UNBELIEVABLE? YES! HOW? SPONSORED BY OTHER ORGANIZATIONS.
Install IBM DB2 on Debian Linux or any other deb/rpm distro manually
You can try my custom software free of charge:
Attention, this script will delete /home/db2inst during create_instance option execution. Please always have backups and use it only for new installations. Please keeps your databases outside of /home/db2inst directory.
For DB2 v8.1 fp17a:
2) Only deb distro: run attached rpm2deb.sh script in above directory.
On rpm distros skip this step.
3)On deb: dpkg -i *.deb
On rpm: rpm -i *.rpm
4) Run attached script:
/utils/db2/do.sh create_instance -s wse
5) Get IBM Java SDK at:
http://www.ibm.com/developerworks/java/jdk/linux/download.html
or
https://www14.software.ibm.com/webapp/iwm/web/preLogin.do?source=lxdk
Move extracted IBMJava2-142 or IBMJava2-amd64-142 to /opt
Using above method I have successfully installed DB2 v8 fixpak 17a to Debian 32bit and Centos 64bit: db2linux2632/fixpak/FP17a_MI00283 and db2linux26AMD64/fixpak/FP17a_MI00286 respectively.
For DB2 v9.7 Express C:
1) Get your copy at: http://www-01.ibm.com/software/data/db2/express/download.html
untar it and run: db2_install
2) Run attached script:
/utils/db2/do.sh create_instance
More useful commands can be found inside do.sh script. If you have any questions please contact.