DBI:ibm_db connection for IBM DB2 database

Ruby
1

Hello,

I am attempting to make a DBI connection from Ruby code running on Win32
(XP) machine to an IBM DB2 v9.5 database running under Linux

On the Win32 machine, I have Ruby 1.8.6 (installed with the one-click
installer) and have successfully launched the following gem installation
commands for the IBM DB drivers and required libraries:

gem install activesupport -v 1.4.2
gem install activerecord -v 1.15.3
gem install ibm_db -v 0.8.5

I have also (as required by the IBM documentation at
http://rubyforge.org/docman/view.php/2361/967/IBM_DB-0.6.0.pdf) added
‘ibm_db’ to the list of connection adapters on line77 of
gems\1.8\gems\activerecord-1.15.3\lib\active_record.rb :

RAILS_CONNECTION_ADAPTERS = %w( mysql postgresql sqlite firebird
sqlserver db2 oracle sybase openbase frontbase ibm_db )

Here is the ruby code :

require ‘rubygems’
gem ‘ibm_db’

require ‘dbi’

Connect to a database

dbh =
DBI.connect(‘DBI:ibm_db:host=localhost;port=50000;database=MY_DB;’,
‘username’, ‘password’)

Select some data

sth = dbh.prepare(‘select * from my_table’)
sth.execute

Print out selected data

while row=sth.fetch do
p row
end

Error reported is as follows :

C:\dev\ruby\dbtest>dbtest.rb
c:/ruby/lib/ruby/site_ruby/1.8/dbi.rb:329:in load_driver': Unable to load drive r 'ibm_db' (DBI::InterfaceError) from c:/ruby/lib/ruby/site_ruby/1.8/dbi.rb:227:in _get_full_driver’
from c:/ruby/lib/ruby/site_ruby/1.8/dbi.rb:213:in `connect’
from C:/dev/ruby/dbtest/dbtest.rb:4

So it seems the IBM DB2 drivers are not found but I cannot see why

Any help is much appreciated

thanks,
Nick Quaine

2

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Nicholas Q. wrote:

gem install activerecord -v 1.15.3
gem install ibm_db -v 0.8.5

Hi Nicholas,

please note that there are newer, improved versions for all three gems
mentioned above. The new ones are recommended, but utilizing the
versions that you chose shouldn’t affect your results in most cases.

I have also (as required by the IBM documentation at
http://rubyforge.org/docman/view.php/2361/967/IBM_DB-0.6.0.pdf) added
‘ibm_db’ to the list of connection adapters on line77 of
gems\1.8\gems\activerecord-1.15.3\lib\active_record.rb :

RAILS_CONNECTION_ADAPTERS = %w( mysql postgresql sqlite firebird
sqlserver db2 oracle sybase openbase frontbase ibm_db )

This step was formerly required in order to inform ActiveRecord about
the existence of an ibm_db adapter. With newer versions of ActiveRecord
this is no longer a requirement. In either case, this was necessary only
for using ActiveRecord with the ibm_db adapter and not a requirement for
the ibm_db driver, contained in the gem too, to work.

dbh =
end
_get_full_driver' from c:/ruby/lib/ruby/site_ruby/1.8/dbi.rb:213:in connect’
from C:/dev/ruby/dbtest/dbtest.rb:4

Ruby-DBI doesn’t have an ibm_db interface. It has its own DB2 driver,
that is not created nor supported by IBM, and it hasn’t received an
update in more than 2 years. I wouldn’t recommend going for the DBI
route.

The IBM_DB Ruby driver is “high level enough” to let you interact with
DB2 with ease. I prepared a guide to get people started, and you can
find it here: http://rubyurl.com/a0tS.

Translating your snippet of code is very straightforward:

require ‘rubygems’
require ‘mswin32/ibm_db’

dbh = IBM_DB::connect(“DRIVER={IBM DB2 ODBC DRIVER};DATABASE=MY_DB;
HOSTNAME=localhost;PORT=50000;PROTOCOL=TCPIP;
UID=username;PWD=password;”, “”, “”)

sth = IBM_DB::exec(dbh, ‘select * from my_table’)

while row = IBM_DB::fetch_row(sth) do
p row
end

This is the version without a prepared statement, but following my guide
you can change it by using the prepare and execute methods if you wish.

Please let me know if you have further questions.

Cheers,
Antonio

http://antoniocangiano.com - Zen and the Art of Programming
http://stacktrace.it - Aperiodico di resistenza informatica
http://math-blog.com - Math Blog: Mathematics is wonderful!
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.8 (Darwin)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkgSyucACgkQqCqsu0qUj9QztQCghYpPag20Re01Lashv8GT6qWh
130AniEBy7h9hBw1m7kGtBDdeNTdafso
=0Uk7
-----END PGP SIGNATURE-----

3

On 4/28/08, Nicholas Q. [email protected] wrote:

Hey Nicholas,

This is good to know. Although I am still not clear on what the
difference is between a database adapter for ruby and a database driver
for ruby.

The “database driver” is a Ruby library that provides an interface for
accessing a database without any other intermediary than the vendor
library and in some cases (like MySQL and PostgreSQL) even without it,
just talking the protocol directly.

The adapter Antonio is talking about is a piece of code written on top
of the “database driver” that provides an interface expected by, in
this case, Ruby on Rails, a web development framework.

Using Informix as an example.

This is the “driver”, a Ruby library that uses the C libraries
supplied by IBM. Written in C and Ruby.

This is the adapter for Ruby on Rails, that uses the above driver,
written entirely in Ruby.
http://svn.rubyonrails.org/rails/adapters/informix/lib/active_record/connection_adapters/informix_adapter.rb

Further confusion arose in my case because there is a profusion of info
out there for DB2 connection from Ruby on Rails - which tends to drown
out the info that is available for just Ruby to DB2 without the Rails -
in the end you start to wonder whether or not it is possible without
Rails - but as you have shown me, it is indeed possible.

If you want to access DB2 outside Rails, you want the IBM driver for
Ruby, called ibm_db, that Antonio mentioned above.

4

Hello Antonio,

firstly - a big thank you for your help - my connection to DB2 from Ruby
now works perfectly. As often happens with searching for solutions via
the internet, the available published help included both deprecated
information and information pertaining to similar but not identical
circumstances - your post put everything straight.

please note that there are newer, improved versions for all three gems
mentioned above. The new ones are recommended, but utilizing the
versions that you chose shouldn’t affect your results in most cases.

I had chosen these because in my DB2 server v9.5 installation, ibm_db
v0.8.5 was bundled so I assumed it to be the most compatible - and the
versions of the other gems were the minimum required.

I have also (as required by the IBM documentation at
http://rubyforge.org/docman/view.php/2361/967/IBM_DB-0.6.0.pdf) added
‘ibm_db’ to the list of connection adapters on line77 of
gems\1.8\gems\activerecord-1.15.3\lib\active_record.rb :

RAILS_CONNECTION_ADAPTERS = %w( mysql postgresql sqlite firebird
sqlserver db2 oracle sybase openbase frontbase ibm_db )

This step was formerly required in order to inform ActiveRecord about
the existence of an ibm_db adapter. With newer versions of ActiveRecord
this is no longer a requirement. In either case, this was necessary only
for using ActiveRecord with the ibm_db adapter and not a requirement for
the ibm_db driver, contained in the gem too, to work.

This is good to know. Although I am still not clear on what the
difference is between a database adapter for ruby and a database driver
for ruby.

Ruby-DBI doesn’t have an ibm_db interface. It has its own DB2 driver,
that is not created nor supported by IBM, and it hasn’t received an
update in more than 2 years. I wouldn’t recommend going for the DBI
route.

More good info - confusion for newbies such as myself on this point
stems from a number of things : the variable intentions behind
terminology such as database interface, adapter and driver; the use of
terms like “driver for DB2 on linux” - does this mean linux is client or
server side?; developers talk of the DB2 DBI solution and use ibm_db
v0.44 which must be the one from two years ago, and then we see ibm_db
version 0.8.5 or 0.9 and think that it is a later version of the same
beast (and therefore implementable in the same way) but reading your
post, it seems that they are quite different. Note this is not a gripe -
I mention all this only to list the gotchas for anyone who is
interested.

Further confusion arose in my case because there is a profusion of info
out there for DB2 connection from Ruby on Rails - which tends to drown
out the info that is available for just Ruby to DB2 without the Rails -
in the end you start to wonder whether or not it is possible without
Rails - but as you have shown me, it is indeed possible.

The IBM_DB Ruby driver is “high level enough” to let you interact with
DB2 with ease. I prepared a guide to get people started, and you can
find it here: http://rubyurl.com/a0tS.

This is a great guide. I would add that another “driver” (we are running
out of terms here) is required : the IBM Data Server Driver for ODBC,
CLI and .Net must be installed on the client machine and afaik
interfaces ibm_db ruby module with the database server via ODBC/CLI.

thanks again for your help.

Nicholas Q.