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
https://www.ibm.com/software/data/db2/udb/sysreqs.html
https://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/db2profileHaving 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 -rubygemsIf you’d like to have this set in the profile of your shell as well, you can insert within it the command:
$ export RUBYOPT=rubygemsRuby 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
endThe 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 a newer version of the Python driver for DB2. You can download the source and eggs for Linux and Windows here.
sudo -s, and then set the environment variables and run the gem install command.
the Guide is quiet ok but you are missing the information about updating the …/ruby/gems/1.8/gems/activerecord-1.15.6/lib/active_record.rb
add ibm_db at RAILS_CONNECTION_ADAPTERS = %w( mysql postgresql sqlite firebird sqlserver db2 oracle sybase openbase frontbase )
Hi Dirk,
the guide focuses on the driver, rather than the adapter for ActiveRecord/Rails. However you are correct and I should perhaps add this to the guide. 🙂
Hi!
Thanks for this great tutorial! It helps me a lot!
stummjr.
Warning, don’t upgrade DB2-Express C 9.1 with Fix Pack 2. Uninstall the previous version of DB2-Express C 9.1 then install the Fix Pack 2 version. I wasted several hours trying to figure out why my Ruby application was not working. Finally solved my problem by doing a clean install with Fix Pack 2.
Tried building with python 2.4 and python 2.5 both with no luck on RHEL4:
[root@linserv DB2]# python setup.py build
running build
running build_ext
building ‘ibm_db’ extension
gcc -pthread -fno-strict-aliasing -DNDEBUG -g -O3 -Wall -Wstrict-prototypes -fPIC -I/dicom/britsrv1/sqllib/include -I/usr/local/include/python2.4 -c ibm_db.c -o build/temp.linux-ppc64-2.4/ibm_db.o
ibm_db.c: In function `_python_ibm_db_connect_helper’:
ibm_db.c:1003: warning: passing arg 3 of `PyDict_SetItemString’ from incompatible pointer type
ibm_db.c:1038:77: warning: “/*” within comment
ibm_db.c: In function `_python_ibm_db_bind_data’:
ibm_db.c:3499: `Py_ssize_t’ undeclared (first use in this function)
ibm_db.c:3499: (Each undeclared identifier is reported only once
ibm_db.c:3499: for each function it appears in.)
ibm_db.c:3499: syntax error before “buffer_len”
ibm_db.c:3576: `buffer_len’ undeclared (first use in this function)
ibm_db.c:3576: warning: passing arg 2 of `PyObject_AsReadBuffer’ from incompatible pointer type
ibm_db.c: In function `initibm_db’:
ibm_db.c:7170: `SQL_DECFLOAT’ undeclared (first use in this function)
error: command ‘gcc’ failed with exit status 1
[root@linserv DB2]# ls
build config.py ibm_db.c ibm_db_dbi.py ibm_db.h ibm_db.README ibm_db.tar README setup.py testfunctions.py tests tests.py
[root@linserv DB2]# less ibm_db.h
[root@linserv DB2]# less ibm_db.c
[root@linserv DB2]# less ibm_db.c
[root@linserv DB2]# echo $IBM_DB_DIR
/dicom/britsrv1/sqllib
[root@linserv DB2]# echo $IBM_DB_LIB
/dicom/britsrv1/sqllib/lib
[root@linserv britsrv1]# ls sqllib
adm bin conv db2cos db2profile fm.linserv.brit.com.reg include java12 lib64 msg samples security64 tmp userprofile
adsm bnd ctrl db2cshrc db2systm function infopop lib log profile.env security sql tools
backup cfg dasfcn db2dump doc hmonCache java lib32 misc Readme security32 sqldbdir usercshrc
I’m stumped.
hgh.
Hi Henry,
could you try with the lastest version?
Thanks,
Antonio
Thank you for your guides, Antonio.
I installed DB2 driver for ruby and I now can connect to my DB2 server from web applications built on Ruby on rails.
Now I have a problem with a table that have a double data type field.
This is my problem.
Let’s say I have a table “orders” with two fields: product_id (string) and qty (double)
When I invoke the OrdersController#create method I receive this error:
ActiveRecord::StatementInvalid: [IBM][CLI Driver][DB2/LINUX] SQL0117N The number of values assigned is not the same as the number of specified or implied columns or variables. SQLSTATE=42802 SQLCODE=-117: INSERT INTO orders (product_id, qty) VALUES(‘abc’,1,0)
The problem is that the method passes two fields (product_id and qty) and three values (‘abc’,1,0).
But 1,0 is the value of qty, that is equal to 1. Because it is a float it is converted in 1,0 and then ‘0’ is seen as a third value.
The right sql should be:
INSERT INTO orders (product_id, qty) VALUES(‘abc’,1.0)
I think this happens for my language settings (Italian) that transforms 1.0 in 1,0.
I would like to do a gsub in the SQL but the SQL instruction is not made by me but is done by the driver (I think).
So, what can I do?
thank you
OK, I found a workaround.
If I run the web server with the command
LC_ALL=ex_EN.utf8 ruby script/server
instead of
ruby script/server
then all works fine
Thank You
I got happy here for a sec. Looking desperately for information wrt python bindings for IBM U2 family of rdbms.
Antonio,
Do the DB2 drivers for Ruby work with DB2 on Z/OS? Or do they only work with DB2 for Linux and DB2 for Windows?
Hi Jeff,
they work with Z/OS and iSeries too (but you’ll need DB2 Connect).
Have a similar problem to the one Henry Hollenberg Had in Jan 08.
Even after downloading the latest version : ibm_db-0.2.0.tar.gz (md5)
running build_ext
building ‘ibm_db’ extension
creating build/temp.linux-i686-2.3
gcc -pthread -fno-strict-aliasing -DNDEBUG -g -O3 -Wall -Wstrict-prototypes -fPIC -I/home/db2clnt8/sqllib/include -I/home/tart/include/python2.3 -c ibm_db.c -o build/temp.linux-i686-2.3/ibm_db.o
ibm_db.c: In function `_python_ibm_db_connect_helper’:
ibm_db.c:1003: warning: passing arg 3 of `PyDict_SetItemString’ from incompatible pointer type
ibm_db.c: In function `ibm_db_table_privileges’:
ibm_db.c:2908: `Py_RETURN_FALSE’ undeclared (first use in this function)
ibm_db.c:2908: (Each undeclared identifier is reported only once
ibm_db.c:2908: for each function it appears in.)
ibm_db.c: In function `_python_ibm_db_bind_data’:
ibm_db.c:3485: `Py_ssize_t’ undeclared (first use in this function)
ibm_db.c:3485: syntax error before “buffer_len”
ibm_db.c:3562: `buffer_len’ undeclared (first use in this function)
ibm_db.c:3562: warning: passing arg 2 of `PyObject_AsReadBuffer’ from incompatible pointer type
ibm_db.c: In function `initibm_db’:
ibm_db.c:7156: `SQL_DECFLOAT’ undeclared (first use in this function)
error: command ‘gcc’ failed with exit status 1
Thanks for your help,
Charaf
Charaf, there is now a 0.2.9.1 version available here. Try with that one. If it doesn’t work, I invite you to post your questions here.
Charaf,
Yes the SQL_DECFLOAT error when on versions of DB2 < 9 has been fixed. Please pull the latest egg
you write: select “Base application development tools” under the section “Application Development Tools”.
Maybe I am wrong (and believe me I do really hope I am), but I am not able to remove all required components and that means:
1) I have to install 417 MB of useless stuff (come on, what’s the use of java support on a simple dumb client computer?!?)
2) I got a base server installation on a client/devel workstation.
3) I have some difficulties with crap client machines.
but let’s say I do all this on a single dedicated workstation. Can I move the compiled module to other machines without all other db2 stuff? Or have I to figure out which file are to be moved from the db2 install folder together with the python module? Or do I need to install all this on every client??
I am a bit concerned.
Can I expect Fedora 9 to work with the IBM_DB gem? I’m very disappointed to see IBM supporting Red Hat but not Fedora because I thought there was a point of Express-C being free. I have Express-C DB2 9.5 installed (after I downloaded libaio-0.3.106-4.2.i386.rpm).
As root I ran:
yum install ruby
yum install rubygems
gem install rails –include-dependencies
With my environment setup, as database instance owner I ran: gem install ibm_db
It failed and I had to copy .gem files from:
/usr/lib/ruby/gems/1.8/cache to
/usr/lib/ruby/gems/1.8/gems/cache
Then I reran it’s failing with the following:
===========================
[db2iss@lc01 sqllib]$ gem install ibm_db
Building native extensions. This could take a while…
ERROR: Error installing ibm_db:
ERROR: Failed to build gem native extension.
/usr/bin/ruby extconf.rb install ibm_db
checking for SQLConnect() in -ldb2… no
checking for SQLConnect() in -ldb2… yes
creating Makefile
make
gcc -I. -I. -I/usr/lib/ruby/1.8/i386-linux -I. -I/opt/ibm/db2/V9.5/include -D_FILE_OFFSET_BITS=64 -fPIC -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector –param=ssp-buffer-size=4 -m32 -march=i386 -mtune=generic -fasynchronous-unwind-tables -Wall -fPIC -c ibm_db.c
In file included from ibm_db.c:20:
ruby_ibm_db.h:18:21: error: sqlcli1.h: No such file or directory
In file included from ibm_db.c:20:
ruby_ibm_db.h:165: error: ‘SQL_MAX_MESSAGE_LENGTH’ undeclared here (not in a function)
==============================
Question: Am I wasting my time trying to get IBM_DB gem working on Fedora 9? Or is this current error worth troubleshooting?
Greg, did you run the following before attempting
sudo gem install ibm_db?Also, please copy and paste your question on the Rubyforge forum: http://rubyforge.org/forum/forum.php?forum_id=9503. Installing on Fedora shouldn’t be a problem.
Hello Antonio,
Thanks for your quick reply. I just signed up for rubyforge too. I’m quite familiar with UDB but new to rails development.
My env is set via .bashrc of the instance owner:
set |grep -i IBM
DB2DIR=/opt/ibm/db2/V9.5
IBM_DB_DIR=/opt/ibm/db2/V9.5
IBM_DB_LIB=/opt/ibm/db2/V9.5/lib32
Not sure if many are using IBM_DB with Fedora, but there seem to be many mysql developer on Fedora. I’ll continue through the forum as you’ve suggested.
Thanks,
Greg
Hi,
I ran into a couple of issues when following your nice manual on opensuse 11 x86_64, behind a company firewall:
a) gem didn’t use the http_proxy system variable. finally I found this workaround:
sudo vi /usr/lib64/ruby/vendor_ruby/1.8/rubygems/config_file.rb
between
def [](key)
@hash[key.to_s]
end
and
private
insert:
def []=(key, value)
@hash[key.to_s]=value
end
then, as in your manual:
. /home/db2inst1/sqllib/db2profile
export IBM_DB_DIR=/home/db2inst1/sqllib
export IBM_DB_LIB=/home/db2inst1/sqllib/lib
-> sudo gem update -p “http://our.company.proxy:8081”
now works!
found at: http://elia.wordpress.com/2007/07/18/gems-through-the-proxy-or-the-undefined-method-error/
b)
sudo ln -s /opt/ibm/db2/V9.5/lib64 /opt/ibm/db2/V9.5/lib
now also this works:
sudo gem install ibm_db –include-dependencies -p “http://our.company.proxy:8081”
of course a) is not related to ibm_db but to gem – but anyway, it might help somebody else.
I can connect now to the local sample database.
regards,
Jürgen
cheers
Hi,
I would like to install this driver on an Ubuntu 8.10 64bit without GUI and with ruby1.9.
I have some issues:
1) Because it is a text only system, I installed the DB2 with db2install and not with db2setup. The DB2 server itself works fine, but I don’t know if I installed the “Base application development tools”, too. If not, where can I find they in the text only db2 installer?
2) I installed ruby1.9 and rubygems1.9. But if I do
“sudo apt-get install ruby-full rubygems”
it wants to install ruby1.8. Can the idm_db driver work with ruby1.9?
I tried to install the ibm_db gem but I had this error:
Error installing ibm_db:
Failed to build gem native extension.
extconf failure: Unable to locate DB2 libraries.
Even if I did
export IBM_DB_DIR=/opt/ibm/db2/V9.5
and
export IBM_DB_LIB=/opt/ibm/db2/V9.5/lib64
Thank You
I am trying to use db2 9.5 express-c with ruby on rails on ubuntu. I have installed db2 and db2 is running fine. I have created sample database, etc. I installed the ruby driver also as per the instructions in this web site. However I am getting following error:
1. inside irb, when I do require ‘ibm_db’
I get this error: loaderror no such file to load — ibm_db
2. for the rail application, when I do rake db:migrate
I get this error: security processing failed with reason ’42’ (ROOT CAPABILITY REQUIRED).
Thanks.
Vinod