Antonio Cangiano November 15th, 2007

Guide to setting up the IBM Ruby and Python drivers for DB2 on Linux (32 or 64 bit)

IBM provides the community with, among others, Ruby and Python open source drivers for DB2 (more exactly IBM databases). Ruby has a gem that packages the Rails adapter for DB2 and its prerequisite driver. As a result, the easiest way to get the Ruby driver for DB2 is to install the ibm_db gem through rubygems. The Python driver is instead currently provided as a tar.gz archive of source code. In both cases, on Linux, the installation builds the binary from source. This procedure is supposed to be very straightforward and user-friendly, and as long as you’re aware of the prerequisites and a few important steps, you can be up and running in no time. Unfortunately, if you aren’t aware of these things, as often happens with Linux, you may end up spending a good deal of time trying to figure out what’s wrong with your environment and setup procedure. This short - largely step-by-step - guide aims to resolve this, by providing you with clear instructions for setting up both the Ruby and Python drivers, respectively, for DB2 on Linux. The instructions below are tailored for Ubuntu 7.10 and its variants (including for example Kubuntu 7.10, 32 and 64 bit), but the same principles can be applied to other distros as well.

Prerequisites

Depending on which of the two drivers interests you, you will need to have Ruby or Python installed, along with a modern version of DB2 (e.g. 9.1.2 or 9.5). Please note that if you are still using DB2 Express-C 9.1, FixPack 2 or greater is required, so make sure that you grab the latest FixPack, FP4. For everyone else, you can get DB2 Express-C 9.5 from the official site for free. Please also note that if you were to run the DB2 9.5 setup on (K|X)Ubuntu 7.10 out of the box, you’d get an error similar to the one below.

ERROR:
 The required library file libstdc++.so.5 is not found on the system.
ERROR:
 The required library file libaio.so.1 is not found on the system.
 Check the following web site for the up-to-date system requirements
 of IBM DB2 9.5
  http://www.ibm.com/software/data/db2/udb/sysreqs.html
 http://www.software.ibm.com/data/db2/linux/validate
/home/antonio/Desktop/exp/db2/linux/install/../bin/db2usrinf:
error while loading shared libraries: libstdc++.so.5:
cannot open shared object file: No such file or directory
[: 609: 0: unexpected operator
/home/antonio/Desktop/exp/db2/linux/install/../bin/db2langdir:
error while loading shared libraries: libstdc++.so.5:
cannot open shared object file: No such file or directory
/home/antonio/Desktop/exp/db2/linux/install/../bin/db2langdir:
error while loading shared libraries: libstdc++.so.5:
cannot open shared object file: No such file or directory
DBI1055E The message file db2install.cat cannot be found.

Explanation:  The message file required by this
script is missing from the system; it may have been
deleted or the database products may have been loaded
incorrectly.

User Response:  Verify that the product option containing
the message file is installed correctly.  If there are
verification errors; reinstall the product option.

To prevent this, please install DB2 with its prerequisites:

$ sudo apt-get install libstdc++5
$ sudo apt-get install libaio-dev
$ sudo ./db2setup

When the DB2 Setup Wizard prompts you for the type of installation requested, ensure that you select “custom” and then, when prompted with the “Features” screen a couple of clicks later, select “Base application development tools” under the section “Application Development Tools” (the check box should switch from gray to white and be marked off). You will need these for building the Ruby and Python drivers during the installation. You can of course install them later, by running the setup again and choosing the “Work with existing” button in the launchpad, but if you’re installing from scratch, it’s easier to do it right the first time.

You can install Ruby or Python any way you prefer, but on Ubuntu (with the Universe repository enabled) you can install the required essential compiler tools (remember, on Linux, unlike Windows, the driver binaries are built from source), Ruby and Rubygems by running:

$ sudo apt-get install build-essential
$ sudo apt-get install ruby-full rubygems

If you are interested in Python, this comes already pre-installed on Ubuntu. Not all variations of Ubuntu however have the python2.5-dev package installed (I believe Kubuntu does), so just to be on the safe side, if you want the Python driver to be installed, get this development package by running:

$ sudo apt-get install python2.5-dev

Installing the Ruby driver for DB2

Now that you’ve ensured that your system has the proper requirements installed, the Ruby driver installation should be quite straightforward, thanks to the gem packaging system. Assuming you are installing it with an arbitrary user account (as opposed to the db2inst1 account), you will need to run the following commands, which will also take care of letting the compiler know where the current DB2 instance is located:

$ . /home/db2inst1/sqllib/db2profile
$ export IBM_DB_DIR=/home/db2inst1/sqllib
$ export IBM_DB_LIB=/home/db2inst1/sqllib/lib
$ sudo gem  update
$ sudo gem install ibm_db --include-dependencies

The last command should prompt you with a few options, please select the latest version of the ibm_db gem with a “(ruby)” next to it (usually option 1), since you are building on-the-fly rather then deploying a Win32 binary.

Installing the Python driver for DB2

In order for you to install the Python driver, you will need to grab ibm_db.tar.gz that contains the 0.1.0 version of the source code. Don’t be afraid of the version number though, despite being at a beta level, it’s a pretty solid driver which has benefited a lot from the maturity of the IBM API used by the PHP and Ruby ones (from which the Python driver was ported). Once you’ve extracted the archive in a given folder, from the shell, enter into that folder and run the following commands (do not worry about several warnings which appear during compilation).

$ . /home/db2inst1/sqllib/db2profile
$ export IBM_DB_DIR=/home/db2inst1/sqllib
$ export IBM_DB_LIB=/home/db2inst1/sqllib/lib
$ sudo python setup.py build
$ sudo python setup.py install

Connecting to the database

Now that at least one of the two drivers is installed, run a quick check to verify that the setup went fine and that you can connect to a database from Ruby or from Python. You can use any database, but if you are new to DB2, you may want to use the sample database called SAMPLE. To create it from your shell run the following:

$ sudo su db2inst1
$ bash
$ db2sampl

Inserting a couple of exit commands allows you to leave the Bash shell first, followed by the instance user db2inst1’s environment. Since you’re now back in the shell as a regular or arbitrary user (not as db2inst1), you’ll need to source the db2profile first, exactly as you’d do if you had just opened a new shell. You may want to consider inserting the following instruction in your shell profile as well if you plan to use the driver regularly.

$ . /home/db2inst1/sqllib/db2profile

Having performed this step, Python users can just run the python command to start the interactive shell, while Ruby users will have to require rubygems as well by running:

$ irb -rubygems

If you’d like to have this set in the profile of your shell as well, you can insert within it the command:

$ export RUBYOPT=rubygems

Ruby users can at this point run the following script interactively (insert one line at a time in irb):

require 'ibm_db'
conn = IBM_DB::connect("sample","db2inst1","mypassword")
sql = "SELECT * FROM SALES"
stmt = IBM_DB::exec(conn, sql)
while (row = IBM_DB::fetch_assoc(stmt))
  p row
end

The output should be a list of hashes, one for each record. Unlike in Python, in Ruby if the connection fails, the IBM_DB::connect method will just return false and not an actual error. The same is true for the IBM_DB::exec method. In such cases, you can run IBM_DB::conn_errormsg and IBM_DB::stmt_errormsg to gather further information on what caused the problem.

For those using the Python driver, you can establish a successful connection and retrieve a record from the Sales table by running the snippet below. The output will be a dictionary whose keys are the names of the columns in the table.

import ibm_db
conn = ibm_db.connect("sample","db2inst1","mypassword")
sql = "SELECT * FROM SALES"
stmt = ibm_db.exec_immediate(conn, sql)
print ibm_db.fetch_assoc(stmt)

Naturally, there is much more to the usage of this IBM API which is common amongst a few languages, but the essentials of working with it need to be part of a different guide. While I go about writing that, feel free to take a look at the PHP and DB2 reference which documents a lot of shared functionalities and naming conventions.

Update: There is now a 0.2 version of the Python driver for DB2. You can download the source and eggs for Linux and Windows here.

• Comments(14)