Meditations on programming, startups, and technology
New Relic

Essential guide to the Ruby driver for DB2

This article is obsolete. Please refer to the following articles for up do date instructions: Ruby/Rails and DB2 | Python/Django and DB2. Thank you!

This guide is a basic tutorial that’ll quickly get you started with the ibm_db Ruby driver. You’ll be querying DB2 from Ruby in no time, but please bear in mind that this document is not meant to be thorough (use the API reference for this purpose). Also, this is a guide for the IBM provided driver and not the ruby-db2 community provided one, and it introduces you to the Ruby driver only not the Rails adapter.

I strongly encourage you to use the IBM one since, while still open source, it is constantly developed and maintained by IBM and it has an active community of people using it in production. The ruby-db2 driver provided by the community hasn’t been updated for over 2 years and I’m not sure whether it’s abandoned or not.

The latest release of ibm_db is only 2 months old (at the time of writing); it’s very stable and fast, and utilizes the common API adopted by IBM for dynamic language drivers (such as PHP, Perl and Python). This means that if you learn one, you’ll easily be able to adapt your skills to another language. My advice is to stick with IBM’s driver.

Please follow these instructions for installing DB2 and IBM’s Ruby driver. Should you encounter any issues with the installation process, please refer to the DB2 Express-C forum and the Rubyforge forum.

When facing difficulties with the installation of DB2, use the former, while if you have problems with the ibm_db driver, use the latter which is monitored by, besides a group of helpful members of the community, myself, Alex Pitigoi (who is the current maintainer of the Rails adapter) and some other members of the team who developed the driver. We’ll carefully listen to your questions and usually provide help or a resolution in a timely fashion.

Before we get started let me clarify one point about the name of the driver. It was formerly known as IBM_DB2, but IBM decided to drop the 2 at the end, in order to reflect the intention to provide a driver that not only works with DB2, but also with other selected IBM Data Servers, like Informix Dynamic Server version 11 (Cheetha), IBM Cloudscape or Apache Derby.

Loading the IBM_DB driver

In order to connect to DB2 LUW from Ruby, we first need to “require” the driver. Linux and Unix users can run:

require 'rubygems'
require 'ibm_db'

On Windows, use:

require 'rubygems'
require 'mswin32/ibm_db'

Please note that you won’t need the first line if you haven’t installed the driver through RubyGems or if you already have the RUBYOPT environment variable set for rubygems. The execution of the second line will return true if the driver has been successfully “required”, false if it was already loaded, and it will raise an exception otherwise. From now on I’ll use ‘ibm_db’, so if you are using Windows, change it accordingly to ‘mswin32/ibm_db’.

Connecting to a DB2 database

The IBM_DB module contains a series of methods and classes that can be used to interact with DB2 from Ruby. The first method that we’ll use is IBM_DB.connect. This allows us to connect to both local and remote databases. Those who have DB2 Connect installed will even be able to connect to DB2 on iSeries and z/OS.

More technically, connecting to a local database means establishing a cataloged connection by specifying a database alias that exists in the DB2 client catalog. For the first few examples we’ll use the SAMPLE local database/alias. In case you don’t have it already, you can create it by running db2sampl.

The following snippet connects to the SAMPLE local database:

require 'rubygems'
require 'ibm_db'

conn = IBM_DB.connect("sample", "db2inst1", "mypassword")

if conn
  puts "We're connected!"
  IBM_DB.close(conn)
else
  puts "There was an error in the connection: #{IBM_DB.conn_errormsg}"
end

The following is the equivalent code for connecting through TCP/IP to a remote database (in reality it obviously connects to local databases too, but locally it’s usually better to use an alias):

require 'rubygems'
require 'ibm_db'

conn = IBM_DB.connect("DRIVER={IBM DB2 ODBC DRIVER};DATABASE=sample;\
                       HOSTNAME=localhost;PORT=50000;PROTOCOL=TCPIP;\
                       UID=db2inst1;PWD=mypassword;", "", "")
if conn
  puts "We're connected!"
  IBM_DB.close(conn)
else
  puts "There was an error in the connection: #{IBM_DB.conn_errormsg}"
end

For an “uncataloged” connection to a database through TCP/IP, the complete connection string is passed to the connect method. Beside the usual arguments (database, username and password), in this case we also need to specify the hostname or IP address of the dataserver, and the port number (defaulted to 50000 on Linux/Unix).

In both cases (local or remote), the connect method returns a connection handle resource when the connection is successfully established, while it returns false for any failed attempts. This implies a couple of important things. The connection outcome should be verified with a conditional if statement, because it won’t raise any exceptions for you if there are problems connecting. Luckily you can use IBM_DB.conn_errormsg to obtain a string containing the error message returned by DB2. For example, in our snippet, if we were to use the wrong password, we would receive an error message similar to the following:

There was an error in the connection: [IBM][CLI Driver] SQL30082N Security processing failed with reason "24" ("USERNAME AND/OR PASSWORD INVALID"). SQLSTATE=08001 SQLCODE=-30082"

Error messages are usually descriptive enough to let you understand what the problem is. Should you find yourself stuck though, you can first try searching through the Information Center, then Google it and if you still can’t solve the issue, ask directly in the DB2 forum.

The following is a common error with a message that is rather puzzling:

"[IBM][CLI Driver] CLI0133E Option type out of range. SQLSTATE=HY092 SQLCODE=-99999"

If you get this error, it usually means that you are using a version of DB2 that is too old. Install the latest FixPack or the latest version of DB2 (currently 9.5) to resolve the problem.

Please note that while it’s a good idea to use the method IBM_DB.close to close connections that are no longer needed, the driver would have closed the connection automatically for you upon finishing the execution of the script.

The IBM_DB.connect method also accepts an optional parameter that affects the behavior of the connection. For example it’s possible to turn on or off the autocommit, specify the case for the column names in the resultset, or even define a scrollable cursor. As usual, the API reference has all the details, but here is an example that turns off autocommit and specifies the option for retrieving lowercase columns:

require 'rubygems'
require 'ibm_db'

conn = IBM_DB.connect("sample", "db2inst1", "mypassword",
                      {IBM_DB.SQL_ATTR_AUTOCOMMIT => IBM_DB.SQL_AUTOCOMMIT_OFF, 
                       IBM_DB.ATTR_CASE => IBM_DB.CASE_LOWER})
if conn
  puts "We're connected!"
  IBM_DB.close(conn)
else
  puts "There was an error in the connection: #{IBM_DB.conn_errormsg}"
end

Finally, before moving on, note that there is a similar method called IBM_DB.pconnect that establishes persistent connections.

Executing queries

The following snippet executes a query and prints the retrieved records:

require 'rubygems'
require 'ibm_db'

if conn = IBM_DB.connect("sample", "db2inst1", "mypassword")
  sql = "SELECT * FROM EMPLOYEE"
  begin
    if stmt = IBM_DB.exec(conn, sql)
      while row = IBM_DB.fetch_assoc(stmt)
        puts "#{row['FIRSTNME']} #{row['LASTNAME']}: #{row['EMPNO']}"
      end
      IBM_DB.free_result(stmt)
    else
      puts "Statement execution failed: #{IBM_DB.stmt_errormsg}"
    end
  ensure
    IBM_DB.close(conn)
  end
else
  puts "Connection failed: #{IBM_DB.conn_errormsg}"
end

It may seem complicated because we added some extra “padding” to the code, but executing queries with the driver is very straightforward. The reason why we have these extra conditions and error checks, is because both the connect and the exec methods don’t raise errors when they fail. Let’s analyze in detail the code above.

We first attempt to establish a connection, placing the result in the conn variable. The returned value can either be a valid connection handle or false. If it’s a false value, it means that the connection attempt failed, therefore we print the error message retrieved with IBM_DB.conn_errormsg.

If the connection was successful we then proceed to execute a query through the method IBM_DB.exec, which accepts two arguments: the connection handle stored in conn, and a string representing the SQL query. This, just like the method connect, will return false if DB2 returns an error and therefore the execution of the query has failed. In that case we print the error message with the IBM_DB:stmt_errormsg method. If the SQL statement was issued successfully the exec method returns a statement resource that we use to fetch the records (of course, for SELECT queries).

In order to fetch the results, we loop using the IBM_DB.fetch_assoc method that retrieves one record at a time. The loop will be interrupted when there’s no more rows left in the resultset. The IBM_DB.fetch_assoc method returns a hash whose keys correspond to the columns. By default, SQL columns are case insensitive, so unless you specified otherwise in a connection attribute, the hash will have uppercase keys.

The table EMPLOYEE has several columns, amongst others FIRSTNME (not a typo on my part), LASTNAME and EMPNO. In this snippet we print all the employees’ first and last names, along with their employee numbers. Finally, we clean up the resources associated with the resultset by executing IBM_DB.free_result. This operation, just like closing the connection, would have happened automatically if not specified, when the script ends.

A series of alternative (and similar) methods exist: fetch_array, fetch_both, fetch_object and fetch_row. Again, the API reference has all the details. They are very easy to use if you understand the manner in which we used fetch_assoc in the snippet above.

Parameterized queries

In the previous section we showed how to run an SQL query to interrogate a DB2 database from Ruby. While the approach adopted so far is perfectly acceptable, in some instances there is a better way. Chances are that sometimes you’ll find yourself repeating the same query over and over.

In some other scenarios, you may also run a series of queries which follow the same pattern except that the values provided vary from query to query. Consider for a moment the following queries:

SELECT * FROM USERS WHERE AGE > 20;
SELECT * FROM USERS WHERE AGE > 30;
SELECT * FROM USERS WHERE AGE > 40;
SELECT * FROM USERS WHERE AGE > 50;
SELECT * FROM USERS WHERE AGE > 60;
SELECT * FROM USERS WHERE AGE > 70;

These are identical except for the cut off value for the column AGE. In such cases, the best approach is to use parameterized queries. For those who aren’t too familiar with the expression, parameterized queries are queries where the SQL statement contains parameters whose values will be passed to the database without having to dynamically create the complete SQL query within your program.

This approach is not only less error prone and much more secure (e.g. in avoiding SQL Injections), but it can also drastically improve performance.

The following snippet shows an example of their usage:

require 'rubygems'
require 'ibm_db'

if conn = IBM_DB.connect("sample", "db2inst1", "mypassword")

  sql = " SELECT FIRSTNME, LASTNAME FROM EMPLOYEE WHERE SALARY BETWEEN ? AND ?"
  
  stmt = IBM_DB.prepare(conn, sql)
  values = [15000, 50000]
  
  begin
    if IBM_DB.execute(stmt, values)
      while row = IBM_DB.fetch_array(stmt)
        puts "#{row[0]} #{row[1]}"
      end
    else
      puts "Execution failed: #{IBM_DB.stmt_errormsg(stmt)}"
    end
  ensure
    IBM_DB.close(conn)
  end
else
  puts "Connection failed: #{IBM_DB.conn_errormsg}"
end

Let’s take a look at what’s going on here. We connect as usual but this time around we place question marks in the SQL query rather than the actual values. Then we prepare the statement through the method IBM_DB.prepare which accepts the connection handle and the parameterized SQL query as required arguments. The API reference for the driver has details for an optional third parameter that changes the way the cursor operates.

Behind the scenes, DB2 creates an optimized access plan for retrieving data with that statement. Subsequently issuing the prepared statement with IBM_DB.execute (not IBM_DB.exec) enables the statements to reuse that access plan and avoids the overhead of dynamically creating a new access plan for every statement we issue. Please note that we could bind the parameters explicitly by using the method IBM_DB.bind_param, which allows us to specify the parameter type (input, output, input-output or file), data type, precision, scale and so on. However this is usually only necessary for stored procedures where we intend to obtain information through output parameters.

If all the parameters are input ones, we can simply pass an array of values (aptly named ‘values’ in our snippet) to the IBM_DB.execute method. Notice that in the code above, we arbitrarily decided to use the IBM_DB.fetch_array method rather than IBM_DB.fetch_assoc as used in the previous one. Unlike the latter, with the former we use the numeric index 0 and 1 to access the values contained in the first two columns of the current record, since the method returns an array and not an associative array (a hash).

Stored Procedures

The ibm_db ruby driver fully supports stored procedures. Let’s start with an extremely simple example that is technically no different than running any other query.

require 'rubygems'
require 'ibm_db'

if conn = IBM_DB.connect("books", "db2inst1", "mypassword")
  
  drop_column_sql = "ALTER TABLE USERS DROP SAMPLE_COLUMN" 
  reorg_sql = "CALL ADMIN_CMD('REORG TABLE USERS')"
  
  if IBM_DB.exec(conn, drop_column_sql)
    puts "Column dropped"
  else
    puts IBM_DB.stmt_errormsg
  end
 
  if IBM_DB.exec(conn, reorg_sql)
    puts "Reorg successful"
  else
    puts IBM_DB.stmt_errormsg
  end
  
else
  puts "Connection failed: #{IBM_DB.conn_errormsg}"
end

The snippet above drops a column from a table. After dropping a column, DB2 requires the issuing of a REORG command. Unfortunately, IBM_DB.exec can only execute valid SQL statements and not DB2 command line processor (CLP) commands. Luckily though, the stored procedure ADMIN_CMD allows us to execute certain administrative commands, including REORG.

So, as you can see, in simple cases like this we can just use pass an SQL statement in which we call a stored procedure or user function to IBM_DB.exec. In practice, things are not always this straightforward. If we are using stored procedures, chances are that we need to handle output parameters too.

Let’s assume that we have a stored procedure called SALES_BY_TITLE which requires three parameters. The first parameter is the name of the author, the second one is the title of a book. Imagine that the second parameter is an input-output one, because we can provide a partial title for the book, and the procedure will provide us with the complete title if it exists.

The third (output) parameter will tell us the number of copies sold to date. From Ruby, we can handle this scenario quite easily:

require 'rubygems'
require 'ibm_db'

conn = IBM_DB.connect("books", "db2inst1", "mypassword")

if conn
  sql = "CALL SALES_BY_TITLE(?, ?, ?)"
  stmt = IBM_DB.prepare(conn, sql)
  
  author = "Allen Ginsberg"
  title = "Cosmo%"
  copies = 0
  
  IBM_DB.bind_param(stmt, 1, "author", IBM_DB.PARAM_IN)
  IBM_DB.bind_param(stmt, 2, "title", IBM_DB.PARAM_INOUT)
  IBM_DB.bind_param(stmt, 3, "copies", IBM_DB.PARAM_OUT)
  
  if IBM_DB.execute(stmt)
    puts "Title: #{title}"
    puts "Copies: #{copies}"
  else
    puts IBM_DB.stmt_errormsg(stmt)
  end
else
  puts "Connection failed: #{IBM_DB.conn_errormsg}"
end

As shown before, we CALL the stored procedure and use question marks as placeholders for the parameters in the SQL statement. Immediately after having prepared the statement, we need to initialize the three variables author, title and copies, and bind them to the prepared statement through the IBM_DB.bind_param method. The second argument of this method is the position of the parameter (starting from 1) and the third one is the type of parameter. Again, the API reference has more details about its usage.

After executing the statement (with the IBM_DB.execute method) the two variables, title and copies, which where respectively bound as INOUT and OUT parameters, will contain the full title of the book and the number of copies which we can print for the user to see.

While the IBM_DB Ruby driver provides us with many more methods, understanding this tutorial will give you enough material to get started with using the driver. Above all, I hope it will make the process of looking up specific methods in the online documentation much easier.


If you enjoyed this post, then make sure you subscribe to my Newsletter and/or Feed.

receive my posts by email

12 Responses to “Essential guide to the Ruby driver for DB2”

  1. […] number of requests about how to use the IBM_DB Ruby driver to query DB2. So I decided to create an essential guide for using the DB2 driver from Ruby. Amongst the topics I covered, there are: connecting to the database (local or remote), executing […]

  2. Ermar says:

    Nice tutorial! I’ve learned a lot of things on how using them. I’ve feel its not complicated.

  3. Ronald says:

    Great tutorial, especially for us who are still trying to explore with Ruby. I have been having quite a hard time. Thanks.

  4. Sorry, but that code hurts my eyes.

    Any Ruby user will tell you that code above is not Ruby-ish, at all.

    It lacks one of the most distinguishable and useful features of Ruby: blocks.

    Iterations and resource management are easily written with blocks.

    Maybe you can get some ideas from the following code, written with Ruby/Informix, that does the same as your second example, but with less clutter.

    require 'informix'
    
    Informix.connect("sample", "db2inst1", "mypassword") do |conn|
      sql = "SELECT * FROM EMPLOYEE"
      conn.cursor(sql) do |cur|
        cur.open
        cur.each_hash do |row|
          puts "#{row['FIRSTNME']} #{row['LASTNAME']}: #{row['EMPNO']}"
        end
      end
    end
    

    Informix resources are automatically freed when the block ends, for any reason (normal or abnormal).

    I’ll try to write something about it in my blog soon.

  5. Gerardo, your snippet is definitely nice. The reason why the API is not particularly Ruby-ish is because it’s a common one amongst several languages. In fact, the driver was initially extracted by Sam Ruby from the PHP DB2 extension. If you use IBM’s drivers for Python or PHP, the resulting code won’t be all that different. Of course in your Ruby programs, you can extend the module IBM_DB in order to include iterators and adopt a more Ruby-like API, but this isn’t the aim of the tutorial. My aim with this tutorial was to clarify the basic usage of the exiting API.

    Also, please note that I’m not the driver implementer, but you can forward your suggestions to opendev at us dot ibm dot com.

  6. Nathan says:

    Thanks for writing this guide. I tried reading the docs and didn’t understand how to use the driver. Now I get that it’s easy. As for me I don’t care about the lack of blocks, just so long as it gets the job done.

  7. My bad Antonio, somehow I got you and rubyibm (or ibm_db) linked together in my mind.

    It’s sad that PHP’s interface was taken, since it’s ugly. But beauty is in the eye of the beholder, or so they say.

    Thanks for this article. I didn’t know how IBM’s driver looked like.

    I’m glad there’s an alternative for Informix users.

  8. No problem Gerardo, and thanks for stopping by and commenting. :)

    In associating me with the ibm_db driver you are not far off. I initially developed the adapter for Rails using the ibm_db driver and I’m active in promoting and supporting DB2 and Ruby/Rails, since I’m part of the IBM DB2 team. Searching for ibm_db, you’ll find my name all over the place. :-P However it is a different team that develops the driver from Sam Ruby’s initial contribution. The same team also takes care of the Python driver and is currently maintaining the ActiveRecord adapter.

  9. For Nathan, may I suggest you to read this:

    http://en.wikipedia.org/wiki/Resource_Acquisition_Is_Initialization

    it may change your opinion about blocks.

    Sometimes getting the job done is not as good as getting the job done in a secure way.

  10. Michele says:

    I Gerardo, do you remember:
    http://www.ruby-forum.com/topic/123595#642637

    My problem is updgrade the db2connet.
    What is “Agente” that prevent me to upgrade?

    Thank’s Michele.

    (P.S. sorry for my bad english)

  11. Frank says:

    Thank you for putting this site together.

  12. […] in mind that this document is not meant to be thorough (use the API reference for this purpose).http://antoniocangiano.com/2008/02/08/essential-guide-to-the-ruby-driver-for-db2/developerWorks : Information Management : IBM DB2 Express Forum : REORG …Get involved in the […]

  13. Jeff says:

    Antonio,

    Thanks for the examples, they are quite useful.

    In the drop column example above you drop the column twice. The second exec should be passed the reorg sql.

  14. Good catch, Jeff. Thanks. It’s fixed now.

Leave a Reply

I sincerely welcome and appreciate your comments, whether in agreement or dissenting with my article. However, trolling will not be tolerated. Comments are automatically closed 15 days after the publication of each article.

Current ye@r *

Copyright © 2005-2014 Antonio Cangiano. All rights reserved.