Download Small Footprint, Wire Protocol ODBC Driver for DB2.

Using the DB2 ODBC driver with unixODBC

The unixODBC Driver Manager widens the choice of potential DB2 client applications because it enables non-DB2 CLI applications to connect to DB2 by using ODBC. To illustrate this, this article shows how to use the unixODBC Driver Manager to connect UNIX and Linux applications that support ODBC to a remote DB2 database.

DB2 Call Level Interface

DB2 Call Level Interface (DB2 CLI) is a callable SQL interface that provides a standardised set of application programming interface (API) functions to send SQL statements to DB2 for processing. DB2 CLI allows C and C++ applications (or applications written in other languages for which a C or C++ foreign function interface is available) to access DB2. DB2 CLI is based on the ODBC specification and the International Standard for SQL/CLI. This shortens the learning curve for application programmers who are already familiar with these database interfaces.

IBM provide a driver that implements the DB2 CLI. The DB2 CLI driver is a self-contained driver that provides direct access to DB2 databases for DB2 CLI applications. When loaded by an ODBC Driver Manager, the DB2 CLI driver also acts as an ODBC driver. The driver conforms to version 3.51 of the ODBC specification.

There are a number of DB2 ODBC drivers for Linux and UNIX platforms. This table lists the DB2 ODBC drivers that IBM provide by DB2 version:

DB2 8.2	DB2 9	DB2 9.5	Notes
DB2 Application Development Client	DB2 Client	DB2 Data Server Client	Contains an IBM DB2 ODBC driver. Data Server part of the name signifies support for "entire IBM data server family" (i5/OS, DB2 for z/OS, DB2 for Linux, UNIX, and Windows, and Informix Dynamic Server)
DB2 Run-Time Client	DB2 Runtime Client	DB2 Data Server Runtime Client	Contains an IBM DB2 ODBC driver. Subset of DB2 Client.
n/a	IBM DB2 Driver for ODBC and CLI	DB2 Data Server Driver for ODBC and CLI	Supports both the DB2 CLI API and the ODBC API. Smaller footprint than the DB2 Client/DB2 Run-Time Client.

Easysoft provide a DB2 ODBC driver for Linux and UNIX platforms:

Connecting to DB2 from non-Windows platforms

Unless otherwise stated, the term DB2 ODBC driver refers to either the DB2 Data Server Driver for ODBC and CLI or its predecessor the DB2 Driver for ODBC and CLI.

Install the DB2 ODBC driver

Copy the DB2 ODBC driver distribution to your Linux or UNIX machine. You can download the DB2 ODBC driver from the IBM Support Fix Central Web site:

http://www.ibm.com/support/fixcentral

The DB2 ODBC driver is also located on the DB2 installation CD.

There is no installation program for the DB2 ODBC driver. You uncompress and unpack the driver distribution in your chosen installation directory. For example:

/tmp$ gunzip v9.5fp5_linuxia32_odbc_cli.tar.gz
/tmp$ mkdir $HOME/db2
/tmp$ tar -xvf /tmp/v9.7fp5_linuxia32_odbc_cli.tar -C $HOME/db2

Create a data source

Before you can use the DB2 ODBC driver to connect to DB2, you need to create a data source. A data source is a set of CLI/ODBC configuration keywords that allows the DB2 ODBC driver to connect to a DB2 database. There are two configuration files in which you can define a data source:

db2cli.ini, which can be used with all DB2 ODBC drivers.
db2dsdriver.cfg, which was introduced in DB2 version 9.5 and can be used with the DB2 Data Server Driver for ODBC and CLI.

`db2cli.ini`

db2cli.ini is a text file, which is located in the cfg subdirectory of the DB2 ODBC driver distribution. A sample db2cli.ini file is provided (which may have to be renamed from db2cli.ini.sample to db2cli.ini) to get you started.

There is one section within db2cli.ini for each data source. Each section begins with the name of the data source name between square brackets. This section header is followed by a set of CLI/ODBC configuration keyword-value pairs. Certain keywords can be added to a COMMON section, which has the section header [COMMON]. Keywords in the COMMON section affect all database connections. For example, you enable DB2 CLI/ODBC tracing by setting keywords in the COMMON section.

This example data source connects to a database named SAMPLE that is served by a DB2 instance on a machine named my-db2-machine. The data source makes a TCP/IP connection to a DB2 instance that is listening on port 50000. my-OS-user and my-OS-password are a valid operating system user name and password for the machine on which the DB2 instance is running. (If you're using the DB2 Data Server Driver for ODBC and CLI, you can omit the Protocol attribute. If you're using this driver and your DB2 instance is listening on the default port, 50000, you can omit the Port attribute.)

; db2cli.ini data source
[DB2_SAMPLE]
Database=SAMPLE
Protocol=TCPIP
Port=50000
Hostname=my-db2-machine
UID=my-OS-user
PWD=my-OS-password

`db2dsdriver.cfg`

db2dsdriver.cfg is an XML file, located in the cfg subdirectory of the DB2 Data Server Driver for ODBC and CLI distribution, whose contents can be validated against the supplied XML schema db2dsdriver.xsd. (You may have to rename the configuration file from db2dsdriver.cfg.sample to db2dsdriver.cfg.)

The dsncollection element in db2dsdriver.cfg contains one or more dsn elements. Each dsn element defines a data source. The dsn element's attributes (alias, name, host, and port) allow you to specify the DSN, the database, the machine on which DB2 is running, and the port on which DB2 is listening. Other connection details, such as the user name and password need to be specified with a parameter element:

<parameter name="name" value="value"/>

where name and value are valid IBM Data Server Driver configuration keywords and values. Note that the valid configuration keywords are not defined in the schema file. If the distribution for your version of the DB2 Data Server Driver for ODBC and CLI includes dbcli, you can use this program to validate the configuration keywords (and the dsn attributes). For example:

$ cd /home/myuser/db2/odbc_cli/clidriver/bin/
$ ./db2cli validate -dsn DB2_SAMPLE
.
.
.
db2dsdriver.cfg Validation :
  ---------------------------------------------------------------------------
  [ DB2DSDRIVER_CFG_PATH env var : unset ]
  [ db2dsdriver.cfg Path         : ../cfg/db2dsdriver.cfg ]
  ---------------------------------------------------------------------------
  [ Valid keywords used for DSN : DB2_SAMPLE ]
    Keyword                                      Value
    --------------------------------------------------
    DATABASE                                     SAMPLE
    HOSTNAME                                     my-db2-machine
    PORT                                         50000
    USERID                                       my-OS-user
    PASSWORD                                     *********
  ---------------------------------------------------------------------------

If a parameter element is contained in a parameters element, the configuration keyword it sets affects all data sources.

The following data source in db2dsdriver.cfg is equivalent to the one contained in db2cli.ini, described earlier.

<!--  db2dsdriver.cfg data source -->
<configuration>
   <dsncollection>
      <dsn alias="DB2_SAMPLE" name="SAMPLE" host="my-db2-machine" port="50000">
         <parameter name="UserID" value="my-db2-user"/>
         <parameter name="Password" value="my-db2-password"/>
      </dsn>
   </dsncollection>
</configuration>

Note If a data source with the same name is defined in both db2cli.ini and db2dsdriver.cfg (for example [DB2_SAMPLE] and dsn alias="DB2_SAMPLE"), the data source in db2cli.ini takes precedence.

Testing the data source with dbcli

If the distribution for your version of the DB2 Data Server Driver for ODBC and CLI includes the dbcli program, you can verify that your data source is valid by connecting to your remote DB2 instance.

dbcli is a CLI program that's linked against the DB2 Data Server Driver for ODBC and CLI. When given a data source to connect to, db2cli passes the data source name (DSN) to the DB2 Data Server Driver for ODBC and CLI. Unlike applications that link against an ODBC Driver Manager, db2cli does not rely on a Driver Manager to work out which ODBC driver the data source belongs to. The DB2 Data Server Driver for ODBC and CLI looks in its configuration file for a data source that matches the one it has been passed and attempts to connect to the DB2 database whose settings are contained in the data source.

The ODBC data source, which you will configure later, will rely on the data source you create in the DB2 ODBC driver configuration file, so unless the latter data source works, you will not be able to connect to DB2 from an ODBC application.

The following command uses db2cli to test the example data source shown earlier, by connecting to the remote DB2 instance and retrieving some data from the SAMPLE DB2 database:

$ cd /home/myuser/db2/odbc_cli/clidriver/bin
$ echo "SELECT * from staff where id = 10" | ./db2cli execsql -dsn DB2_SAMPLE

If you can successfully connect to your data source, you can then register it with the unixODBC Driver Manager and connect to DB2 from an ODBC application. If you get an error containing the text A communication error has been detected, DB2 is not running on the host you specified or is not listening on the port you specified.

Install the unixODBC Driver Manager

Unlike a CLI application, an ODBC application usually links against an ODBC Driver Manager rather than a particular ODBC driver. An ODBC Driver Manager is the interface between an ODBC application and an ODBC driver. At run time, the application provides a connection string that defines the ODBC data source it wants to connect to and this in turn defines the ODBC driver that will handle the connection. The Driver Manager loads the requested ODBC driver and passes all ODBC API calls on to the driver.

In the case of the DB2 ODBC driver, the ODBC application needs to supply an ODBC data source with the same name as the DB2 ODBC driver data source. The ODBC data source enables the Driver Manager to load the correct driver. The DB2 ODBC driver then checks its configuration files for a data source with the same name as the one as it has been passed.

unixODBC is a Driver Manager for non-Windows platforms that the DB2 ODBC driver is compatible with. Although unixODBC is included with most Linux distributions and some UNIX distributions, IBM recommend that you download and build unixODBC from the latest source distribution, which is available from the unixODBC web site.

The unixODBC source distribution is a gzipped tar file, which you need to uncompress and untar. For example:

$ gunzip unixODBC-2.3.1.tar.gz
$ tar -xvf unixODBC-2.3.1.tar

cd into the directory created by untarring the unixODBC source distribution. Enter ./configure --help to display the possible configuration options for unixODBC. If you want to change any configuration option from its default value, use configure to do this. For example, this configure line changes unixODBC's installation directory from its default value, /usr/local, to the home directory of the user who is building unixODBC:

$ ./configure --prefix=$HOME

Note The configure line shown in the IBM documentation turns off the building of unixODBC's GUI components and sample ODBC drivers (--enable-gui=no --enable-drivers=no). These options are no longer necessary. In unixODBC 2.3.0, the default for --enable-drivers was changed to no and the GUI components were moved into a new project.

To build and install unixODBC, enter:

make
make install

64-bit platforms

If your application is 64-bit, you need to use a 64-bit DB2 ODBC driver and build a 64-bit version of the unixODBC Driver Manager. (If you are not sure whether your application is 64-bit, run the file command on the application binary. If the file is 64-bit, the command output will contain ELF 64-bit (or something similar such as ELF-Class64 or ELF-64.) In unixODBC 2.2.13, the default size of SQLLEN and SQLULEN types changed from 32 bits to 64 bits. This is the expected size for the 64-bit DB2 ODBC driver, which is built with 64-bit SQLLEN types.

Register the DB2 ODBC driver and data source with unixODBC

After uncompressing and unpacking the ODBC driver distribution, you need to manually install the driver under the unixODBC Driver Manager. To do this, you need to add a driver definition for the DB2 ODBC driver to odbcinst.ini. To find out where odbcinst.ini is located, use odbcinst's -j flag. For example:

$ /home/myuser/bin/odbcinst -j

In odbcinst.ini, each driver definition begins with the driver name in square brackets. The driver definition also includes the Driver attribute, which specifies the path to the ODBC driver shared object. This example data definition is for the DB2 Data Server Driver for ODBC and CLI on Linux; the driver shared object has been copied to /usr/lib.

# Example odbcinst.ini driver definition for DB2 Data Server Driver for ODBC and
# CLI
[DB2]
Description=DB2 ODBC Driver
Driver=/usr/lib/libdb2.so # Replace /usr/lib with the directory where your
                          # driver shared object is located.
Fileusage=1               # 
Dontdlclose=1             # IBM recommend setting Dontdlclose to 1, which stops
                          # unixODBC unloading the ODBC Driver on disconnect. 
                          # Note that in unixODBC 2.2.11 and later, the Driver
                          # Manager default for Dontdlclose is 1.

64-bit Platforms The 64-bit version of the DB2 ODBC driver is named libdb2o.so.

To connect an ODBC application to a database:

The ODBC Driver Manager needs to know which ODBC driver to load.
The ODBC driver needs various connection details. For example, the machine on which the database is located, the database name, authentication details, and so on.

To supply this information, the ODBC application passes a connection string to the Driver Manager. The connection string may specify:

An ODBC data source, which identifies the ODBC driver to connect to, and contains the attributes that the driver needs to establish the connection.
An ODBC data source and one or more connection attributes supplementing or overriding ones contained in the data source.
The DRIVER attribute, which identifies the ODBC driver to connect to, and the attributes that the driver needs to establish the connection.
This is known as a DSN-less connection.

The file in which an ODBC data source is defined depends on whether it is a user data source (only available to a particular user) or a system data source (available to all users). To find out where unixODBC stores ODBC data sources, use the odbcinst command:

$ odbcinst -j
unixODBC 2.3.0
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/myuser/.odbc.ini
SQLULEN Size.......: 4
SQLLEN Size........: 4
SQLSETPOSIROW Size.: 2

In the odbcinst output shown above, system data sources are stored in /etc/odbc.ini and user data sources are stored in ~/.odbc.ini.

A data source for the DB2 ODBC driver needs to contain the Driver attribute. DB2 ODBC driver attributes must be specified in a DB2 configuration file (db2dsdriver.cfg or db2cli.ini). The DB2 ODBC driver will not read attributes specified in odbc.ini or .odbc.ini. This example ODBC data source specifies the driver definition for the DB2 ODBC driver, which we added to odbcinst.ini.

[DB2_SAMPLE]
Driver=DB2

When an ODBC application attempts to connect to the data source DB2_SAMPLE:

The unixODBC driver loads the DB2 ODBC driver, because that is the driver specified in the driver definition DB2.
The DB2 ODBC driver looks for a section in db2dsdriver.cfg or db2cli.ini with the same name as that of the data source.
The DB2 ODBC driver checks that section in its configuration file for the driver attributes it needs to connect to DB2.

If your application accepts a user name and password, the user name and password can be omitted from the DB2 ODBC driver configuration file. For example, isql, the example ODBC application included with unixODBC, allows a user name and password to be specified on the command line:

$ isql -v DB2_SAMPLE my-db2-user my-db2-password

Connect to DB2 from your ODBC application

If you have configured and tested your DB2 ODBC driver data source, installed unixODBC, and registered the DB2 ODBC driver and data source with unixODBC, you are ready to connect to DB2 from your ODBC application.

In your ODBC application, specify the ODBC data source (which will have the same name as the DB2 ODBC driver data source) to initiate the connection.

These Perl scripts, to which ODBC support has been added through the use of the Perl DBI and DBD::ODBC modules, illustrate the different ways DB2 ODBC driver connection details can be specified.

#!/usr/bin/perl -w
use strict;
use DBI;
# The data source DB2_SAMPLE, which contains all the DB2 ODBC driver attributes
# needed to establish the connection, is defined in both the Driver Manager and
# ODBC driver configuration files.
my $dbh = DBI-> connect('dbi:ODBC:DSN=DB2_SAMPLE');

#!/usr/bin/perl -w
use strict;
use DBI;
# Override the Hostname attribute, which is also specified in the data source.
my $dbh = DBI-> connect('dbi:ODBC:DSN=DB2_SAMPLE;Hostname=my-db2-machine);

#!/usr/bin/perl -w
use strict;
use DBI;
# Pass the DSN, user name and password as separate arguments. (This DBI connect
# method calls the ODBC API SQLConnect. The DBI connect method in the other
# examples calls SQLDriverConnect.)
my $dbh = DBI-> connect('dbi:ODBC:DB2_SAMPLE', 'my-OS-user', 'my-OS-password');

#!/usr/bin/perl -w
use strict;
use DBI;
# Use a DSN-less connection. The ODBC/DB2 ODBC data sources are not used.
my $dbh = DBI-> connect('dbi:ODBC:Driver=DB2;Database=SAMPLE;Protocol=tcpip;
                         Hostname=my-db2-machine; Port=50000;UID=my-db2-user;
                         PWD=my-db2-password');

Unicode DB2 databases

DB2 supports Unicode through Unicode databases. Starting with DB2 9.5, the default code page (which determines what characters can be stored in the database) is Unicode. You can find out what code page your DB2 database is using by using the following command:

db2 get db cfg for database

where database is the name of your database. If the value of the Database code page parameter is 1200 or 1208, the database has a Unicode code page.

In a Unicode DB2 database, character string data (held in CHAR, VARCHAR, LONG VARCHAR, and CLOB columns) is stored in the UTF-8 encoding form and graphic string data (held in GRAPHIC, VARGRAPHIC, LONG VARGRAPHIC, and DBCLOB columns) is stored in the UCS-2 encoding form.

Unicode encoding forms use either a fixed or a variable number of bytes to represent each character. UTF-8 is a variable-width encoding where one character can be 1, 2, 3, or 4 bytes. UCS-2 is a fixed-width encoding where each character is 2 bytes in size.

The Unicode Standard specifies a numeric value (code point) and a name for each of its characters. For example, the code point U+0041 is assigned the character name "LATIN CAPITAL LETTER A." Characters in the range U+0800 through U+FFFF (for example, Chinese, Korean, and Japanese characters), which require 3 bytes of storage in UTF-8 can be stored as 2-byte UCS-2. DB2's graphic string format is a more efficient way to store such characters.

ODBC supports Unicode in the form of Unicode data types and Unicode versions of the ODBC API. The encoding form that ODBC expects for data used with Unicode API functions is UCS-2. Although it is possible to use UTF-8 data with the standard ODBC API functions, ODBC does not know about the relationship between multi-byte sequences and single characters. For example, when reporting the length of a CHAR(20) column holding UTF-8 encoded data, ODBC is unaware that the data may require up to 80 (20×4) bytes of storage space.

To work with Unicode DB2 data, you need to use a application that passes UCS-2 (or the subset of UTF-16 that is identical to UCS-2) encoded data to the Unicode ODBC APIs.

The Perl DBD::ODBC module, when built with Unicode support (the -u switch specified when configuring DBD::ODBC, perl Makefile.PL -u) is an ODBC application that uses the Unicode ODBC APIs and data types and passes data to these APIs in the encoding form that the DB2 ODBC driver expects.

Perl DBD::ODBC includes a test suite, which is used to verify that a particular ODBC driver is compatible with DBD::ODBC. (The tests are present in the t subdirectory of the directory created by unpacking the DBD::ODBC distribution.) The test 41Unicode.t inserts and then retrieves some Unicode data. To do this, the test:

Checks that the data to be inserted is a valid UTF-8 encoded Unicode Perl string.
DBD::ODBC in conjunction with Perl DBI provides an interface to ODBC databases for Perl. DBD::ODBC not only has to handle the encoding form that the unixODBC Driver Manager and DB2 ODBC driver expect but also the encoding form that the application it provides the interface for expects. During the course of the test, DBD::ODBC converts from the application encoding to the ODBC encoding and vice versa.
Creates a table with some VARGRAPHIC columns to hold the data.
Uses a parameterised insert to insert the Unicode data. The INSERT statement parameters are bound as a Unicode ODBC data type.
This shortened extract from the DB2 ODBC driver trace log, generated as a result of running this test, shows that the White Smiling Face character, which was originally UTF-8 encoded has been converted to the ODBC encoding. (263A is the Unicode code point for this character. The test was run on a little endian Linux system and so order of the code units 26, 3A is reversed.)
```
SQLExecute( hStmt=1:1 )
( Row=1, iPar=2, fCType=SQL_C_WCHAR, rgbValue="☺" - x'3A26', pcbValue=2,
  piIndicatorPtr=2 )
```
Retrieves the inserted data.
Checks that the retrieved data is a valid Unicode Perl string.

Note The Perl DBD::ODBC 1.36 test suite includes some changes for the DB2 ODBC driver. As the DBD::ODBC tests not only verify ODBC driver compatibility with the module but also provide examples of DBD::ODBC usage, you are recommended to download DBD::ODBC 1.36 or later from CPAN.

Note Passing UTF-8 encoded data to the DB2 ODBC driver's Unicode APIs (which expect UCS-2) may result in application errors, data corruption, or application exceptions. For this reason, you cannot use a Unicode ODBC application with the DB2 ODBC driver, if you are using the commercially available Driver Manager for Linux and UNIX platforms, as this Driver Manager uses UTF-8 as its Unicode encoding.

Using the DB2 ODBC driver with unixODBC

DB2 Call Level Interface

Connecting to DB2 from non-Windows platforms

Install the DB2 ODBC driver

Create a data source

`db2cli.ini`

`db2dsdriver.cfg`

Testing the data source with dbcli

Install the unixODBC Driver Manager

64-bit platforms

Register the DB2 ODBC driver and data source with unixODBC

Connect to DB2 from your ODBC application

Unicode DB2 databases

About Easysoft

Products

Services

Licensing

Support

Search

Popular Resources

Legal