Connecting to DB2 with Ruby on Linux

Recently got my hands dirty with ruby, my first task. Ya I know I am quite late to work on quite popular dev tool. Anyways, task was to access +IBM DB2 from +Ruby  (without using rails). If Ruby, DB2 was not complicated enough, the added complexity factor came from Linux and that too RHEL (not ubuntu). 

Started with low hopes

1. Installed latest version of Ruby:

   curl -L https://get.rvm.io | bash -s stable –ruby

2. Installed and verified the ibm_db gem

        

3. You will need a DB2  installation – I used DB2 express 9.5 (on a separate machine)

4. Now I needed a DB2 driver on my main machine. So searched and installed “The IBM Data Server Driver for ODBC and CLI product”. Nice instructions here

5. After unzipping the driver in the previous step set the environment variable:

   export IBM_DB_HOME=path/to/odbc_cli/clidriver

      

6. Now install the gem, (Some nice pages to install/configure ibm_db gem here) from command prompt

   gem install 'ibm_db'

7. Export another variable:

   export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/path/to/odbc_cli/clidriver/lib

Verify the install and connection:

8. On Command prompt give command:

   irb require 'ibm_db'

   you should get “true” as response

9. Now give this command

   IBM_DB.connect('DRIVER={IBM DB2 ODBC=DRIVER};DATABASE=devdb;HOSTNAME=myhost;PORT=60000;PROTOCOL=TCPIP;UID=uname;PWD=password;','','')
   

   you should see something like this => ibm_db::connection:0x2dddf40>
   Note that on giving this command it will connect to your db2 on the mentioned port, if     this DB2 is not on local machine then firewall can block the inbound connection, so on the db2 machine make sure to accept the connection or configure firewall if any manually.

10. Now:

    stmt = IBM_DB.exec conn,'select * from staff'

    Here I started getting an error undefined symbol: rb_str2cstr. Searched on the web, many result for sqllite but not for db2. One thing was clear that there is some version problem. Finally raised a bug at rubyforge. Again little hopes, but was amazed by the turn around time and also the exact solution. Though it required to make some changes to the ibm_db file. (which should be fixed as part of the the gem itself.) Anyways, the solution was:

    a) cd .../gems/ibm_db-2.5.11/ext
    b)In extconf.rb, replace "if( RUBY_VERSION =~ /1.9/)" with "if(RUBY_VERSION =~ /2.0/)"
    c) ruby extconf.rb
    d) make && cp ibm_db.so ../lib/
    

    
    
    Again gave the stmt command and this time the result was “false”. No error description, nothing. How come people make such software and even distribute. Just by chance stumbled on another command “prapare”.  

    stmt = IBM_DB.prepare conn,'select * from staff'

    This time got proper error (there was some issue in qualifying the table name with proper user). Corrected the stmt exec command.

11. Final command

    IBM_DB.fetch_assoc stmt 
    

    and got the result :)