I am currently working on learning Ruby. I have always been a Perl fanatic, but a new build framework that my company is using requires me to use Ruby. Whee, a chance to learn something new! I am a geek.
Background
In Perl, I never used the DBI due to the issues that I had with getting it installed in a couple of AIX environments, and the inability in some client environments to install the DBI. However, with Ruby I don’t see that being an issue at this point, so I’m trying to make use of the tools designed for interacting with the database.
Little did I suspect that this decision would lead me to a 6-hour oddessy of simply trying to connect to the database. Granted, part of that time was due to my own ignorance of Ruby, and it won’t take a tenth as much time for future setups.
Installing the IBM_DB Gem
I started out wrong by simply installing the gem without looking for instructions. I found: sqllib/ruby64/ibm_db-2.5.11.gem, and simply used: gem install ~db2inst2/sqllib/ruby64/ibm_db-2.5.11.gem as root. Don’t make my mistake – use the proper instructions on installing the gem. That also happens to be by one of my favorite authors on technical blogging topics.
After I fixed my install mistake, a friend helped me figure out that version of the driver that comes with DB2 10.1 Fixpack 3 is not compatible with the version of Ruby I was using (2.0.0). The gem that was packaged with db2 was 2.5.11, and I needed 2.5.12. Of course, it did not tell me this, it simply did this when I tried to connect:
-bash-4.1$ irb
2.0.0-p353 :001 > require 'ibm_db'
=> true
2.0.0-p353 :002 > conn = IBM_DB.connect("WC042D02", "", "")
TypeError: no implicit conversion from nil to integer
from (irb):2:in `connect'
from (irb):2
from /usr/local/rvm/rubies/ruby-2.0.0-p353/bin/irb:12:in `'
Nice useful error message, that.
After determining that I needed to update the gem, I tried it and got this error:
[root@server ~]# gem update ibm_db Updating installed gems Updating ibm_db Building native extensions. This could take a while... ERROR: Error installing ibm_db: ERROR: Failed to build gem native extension. /usr/local/rvm/rubies/ruby-2.0.0-p353/bin/ruby extconf.rb Detected 64-bit Ruby checking for SQLConnect() in -ldb2... yes checking for gil_release_version... no checking for unicode_support_version... no creating Makefile extconf.rb:114:in `libpathflag': Use RbConfig instead of obsolete and deprecated Config. extconf.rb:114:in `libpathflag': Use RbConfig instead of obsolete and deprecated Config. make "DESTDIR=" compiling ibm_db.c In file included from ibm_db.c:27: ruby_ibm_db_cli.h:52:21: error: sqlcli1.h: No such file or directory In file included from ibm_db.c:27: ruby_ibm_db_cli.h:56: error: expected specifier-qualifier-list before ‘SQLSMALLINT’
After much digging around with the help of my friend, we found that /opt/IBM/db2/V10.1/include was nearly empty. The reason it was empty was because I had not installed the application development tools component when I installed db2. I believe db2_install includes this component, but a typical install with db2setup does not. See my blog entry on how to install a component after the rest of db2 is installed if you, too, need to add this component in.
After installing that component and updating my instances, I tried again to update the gem and got what I needed this time:
[root@server]# . /db2home/db2inst2/sqllib/db2profile [root@server]# echo $IBM_DB_INCLUDE /db2home/db2inst2/sqllib/include [root@server]# echo $IBM_DB_LIB /db2home/db2inst2/sqllib/lib [root@server]# gem update ibm_db Updating installed gems Updating ibm_db Building native extensions. This could take a while... Successfully installed ibm_db-2.5.12 Parsing documentation for ibm_db-2.5.12 unable to convert "\xB0" from ASCII-8BIT to UTF-8 for lib/ibm_db.so, skipping Installing ri documentation for ibm_db-2.5.12 Installing darkfish documentation for ibm_db-2.5.12 Gems updated: ibm_db
Finally, I again tested a simple database connection:
[db2inst2@server]$ irb
2.0.0p353 :001 > require 'ibm_db'
=> true
2.0.0p353 :002 > conn = IBM_DB.connect("SAMPLE","","")
=> #
2.0.0p353 :003 > quit
That is what a successful connection looks like.
Executing a Statement
A week later, when I finally got around to attempting to execute a statement, I got this error:
2.0.0-p353 :003 > stmt=IBM_DB.exec(conn, "select * from syscat.bufferpools") irb: symbol lookup error: /usr/local/rvm/gems/ruby-2.0.0-p353/gems/ibm_db-2.5.12/lib/ibm_db.so: undefined symbol: rb_str2cstr
With some research on http://rubyforge.org/), I figured out that in the week between getting the connection working and trying the statement, a new version of the gem had been released, that fixed this exact issue. I updated again:
[root@server ~]# . /db2home/db2inst2/sqllib/db2profile [root@server ~]# export IBM_DB_DIR=/db2home/db2inst2/sqllib [root@server ~]# gem update ibm_db Updating installed gems Updating ibm_db Building native extensions. This could take a while... Successfully installed ibm_db-2.5.14 Parsing documentation for ibm_db-2.5.14 unable to convert "\xD1" from ASCII-8BIT to UTF-8 for lib/ibm_db.so, skipping Installing ri documentation for ibm_db-2.5.14 Installing darkfish documentation for ibm_db-2.5.14 Gems updated: ibm_db
After that, I was able to execute a statement. Phew, lot of work just to get to that point.
A big thanks to @idbjorh for his extensive help with this issue. If you don’t already, you should be listening to his podcast – The Whole Package Cache
Look for more Ruby/DB2 articles from me. I write about what I do, and I expect to be working on this pretty intensively.
[…] Using the IBM_DB Ruby gem with DB2 […]
They already knew back in 1970: http://www.youtube.com/watch?v=7WY0WJZUqYw
Great article. I followed a very similar path (Perl to Ruby) a while ago, having been encouraged to look at Ruby on Rails with DB2 by Leon Katsnelson.
Found the Ruby driver developers to be very responsive to problem reports : often fixing the issue and sending me a new drop of the driver code within minutes.
Is your foray into Ruby as a general purpose language or as part of a Rails development? If the latter then Rails takes care of a lot of the complexities which you’ve described above. If the former then you really should check out (if you’ve not done so already) the Ruby books from Pragmatic Programmers (particularly the “Pickaxe book” which is virtually the Ruby language manual !!!).
I did a couple of sessions on RoR with DB2 at IDUG a few years ago. You should be able to dig these out of the IDUG archives. I’d be interested in seeing how things have progressed since then, since I’ve been so busy on other things that I’ve not really been keeping up with the Ruby world.
I’m using it without Rails. A teammate is using it for some other things, so I have to either hand some of my script development over to him or learn it myself. I like the opportunity to learn something new, and the teammate has built an interesting framework, and the OO way that he’s doing things is interesting. When using Perl, I had never really used the DBI, so that’s new to me too. The learning curve is a bit steep at the moment, but I’ll get there. We’re considering moving all of our DB maintenance scripts from Perl to Ruby, but haven’t made a final decision there.
Great writeup! It’s so nice to see people using Ruby with DB2 in 2014. If anyone’s looking for a way to use bound parameters with DB2, the syntax is like this (in this example, I’m creating a record in DB2 from a Ruby hash):
sql = “INSERT INTO SONG (ID, TITLE, ARTIST) VALUES (?, ?, ?)”
stmt = IBM_DB.prepare(conn, sql)
IBM_DB.bind_param(stmt, 1, song[‘ID’])
IBM_DB.bind_param(stmt, 2, song[‘TITLE’])
IBM_DB.bind_param(stmt, 3, song[‘ARTIST’])
if IBM_DB.execute(stmt)
puts “Inserted a row.”
else
puts IBM_DB.stmt_errormsg(stmt)
end