Common pitfalls when installing the ibm_db gem

Installing the ibm_db gem on Windows is as easy as running gem install ibm_db. Next to no problems have been reported, as the the installation is as smooth as it gets. On Linux things can get a bit trickier though. Having helped dozens of people resolve installation problems, I’ve gathered some insight into what the common pitfalls are when installing the ibm_db gem on *nix systems. Here I’ve put together a checklist, in case you’re experiencing issues with your own install.

First and foremost, the most common error message is a variant of the following:

$ sudo 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/ruby1.8 extconf.rb install ibm_db
checking for SQLConnect() in -ldb2... no
*** extconf.rb failed ***
Could not create Makefile due to some reason, probably lack of
necessary libraries and/or headers.  Check the mkmf.log file for more
details.  You may need configuration options.

Provided configuration options:
	--with-opt-dir
	--without-opt-dir
	--with-opt-include
	--without-opt-include=${opt-dir}/include
	--with-opt-lib
	--without-opt-lib=${opt-dir}/lib
  ...
  ...

The gem fails to install because the SQLConnect() function provided by DB2’s development headers (which are required to build the gem) cannot be found/loaded.

This brings us to our quick checklist:

1. Have you sourced the profile? This is done by running:

. /home/db2inst1/sqllib/db2profile

You may need to replace the dot, with source depending on your shell.

2. Have you specified where DB2’s directory and library are to be found? You can do so by running:

export IBM_DB_DIR=/home/db2inst1/sqllib
export IBM_DB_LIB=/home/db2inst1/sqllib/lib32

3. Did you install the required development headers during the installation? These are available by selecting “Base application development tools” under the section “Application Development Tools” in the DB2 Setup Wizard (having selected a “custom” installation beforehand).

You can double check this by running:

ls /home/db2inst1/sqllib/include

This should show you a series of .h files, including sqlcli1.h. If you don’t see them, then you need to repeat the setup procedure so as to add the development tools. Likewise, you should also see a series of .so files, including
libdb2.so when you run: ls /home/db2inst1/sqllib/lib32.

4. Are you trying to perform the installation when logged in as the db2inst1 user? If you’re doing this, it’s best not to. It doesn’t matter if db2inst1 is a suoder or not. Don’t run sudo gem install ibm_db as the instance user.

5. If nothing else works, try to install as the root user (on certain systems sudo won’t cut it). The following complete procedure works in 99% of cases:

sudo -s
. /home/db2inst1/sqllib/db2profile
export IBM_DB_DIR=/home/db2inst1/sqllib
export IBM_DB_LIB=/home/db2inst1/sqllib/lib32
gem install ibm_db

If this checklist doesn’t help you, then you can reach for the RubyForge forum, where the DB2 on Rails community and the IBM team will be happy to help you.

November 11 2008 | How-to and Tips&Tricks | 3 Comments »

Solving “LoadError: 14001″ driver errors

Yesterday, there have been a couple of reports of problems with the driver immediately after upgrades to the ibm_db gem version 0.9.0 on Microsoft Windows. The error reported is analogous to the following:

gem 'ibm_db'
require 'mswin32/ibm_db'
LoadError: 14001: The application has failed to start because its side-by-side
configuration is incorrect. Please see the application event log for more detail.

Or in other cases:

LoadError: 14001: This application has failed to start because the application
configurationis incorrect. Reinstalling the application may fix this problem.

If you haven’t received this type of error message, your installation is working and stable. For those few of you who experienced this problem, I want to let you know that this issue is actively being investigated and the proposed solution is being thoroughly tested. Since the error above does not only concern our driver (other applications are raising similar errors on Windows), it appears that the problem is related to the lack or misconfiguration of the VC8 runtime.

Those of you who received an error message, can try to install the Microsoft Visual C++ 2005 Redistributable Package (x86) and try again. This ensures that the VC8 runtime is configured in order to support the new DB2 CLI binary dependencies related to the “native assembly cache (also called WinSxS folder)”. This solved the problem in our preliminary tests. Please use the comments to tell us if this worked for you.

October 23 2007 | Tips&Tricks | 3 Comments »

Troubleshooting DB2 on Rails

DB2 on Rails provides the developer with many ways to retrieve information about errors which have occurred. Rails developers can take a look at the logs within the log folder in order to read the SQL errors returned by the datasever. But what about simple Ruby scripts? Let’s try to connect to DB2 with the wrong user credentials:

conn = IBM_DB::connect("mydb","myuser","my_wrong_pass")
#=> false

false. That doesn't really help us too much. We know that something went wrong, but we don't exactly know what. Luckily for us, the IBM_DB driver gives us all the tools that we need to properly troubleshoot problems. The example above can be rewritten in this way:

if conn = IBM_DB::connect("mydb","myuser","my_wrong_pass")
  # do something interesting
else
  # conn is false
  raise IBM_DB::conn_errormsg
end
#=> RuntimeError: [IBM][CLI Driver] SQL30082N Security processing failed with reason
"24" ("USERNAME AND/OR PASSWORD INVALID").  SQLSTATE=08001 SQLCODE=-30082

Okay, that should have us covered when it comes to failed attempts to connect to the database, but what about failed queries? Analogously, the IBM_DB driver provides us with the IBM_DB::stmt_errormsg method:

if stmt = IBM_DB::exec(conn, "SELECT * FROM WRONG_TABLE)
  # process the results
else
  # stmt is false
  raise IBM_DB::stmt_errormsg
end
#=> RuntimeError: => [IBM][CLI Driver][DB2/NT] SQL0204N " DB2ADMIN.WRONG_TABLE" is
an undefined name.  SQLSTATE=42704 SQLCODE=-204
    

DB2 error messages are usually easy to understand and with a bit of help from the DB2 Information Center you should be able to get out of trouble most of the time.

DB2 CLI Tracing

Despite the helpful error messages, there are situations in which troubleshooting can be hard because everything seems to be in the right place but your application is still acting up for some reason. The ultimate tool when it comes to troubleshooting for DB2 and Ruby/Rails application problems is to enable DB2 CLI (Call Level Interface) tracing. The CLI trace captures information about applications that access the DB2 CLI driver. Tracing gives you the ability to analyze low level calls to the C driver API with details on the input and output, to and from the database. The resulting trace is not particularly easy to understand for a DB2 novice, but it can offer a microscopic view which is invaluable for understanding problems that are hidden by the abstractions of higher level APIs, and allow you to see to a certain degree what's happening under the hood.

The two free PDFs, Call Level Interface Guide and Reference, Volume 1 and Volume 2 are the best references if you need to look up calls in your CLI trace files. Instructions on how to enable CLI level tracing for DB2 Express-C LUW (Linux/Unix/Windows) can be found here.

October 19 2007 | How-to and Tips&Tricks | 6 Comments »

IBM_DB2 or IBM_DB?

If you haven’t followed the IBM releases of DB2 on Rails very closely, you may be puzzled to find a Rubyforge project alongside the Alphaworks project. I’d like to shortly clarify what’s going on. The Stater Toolkit for DB2 on Rails is an easy, batteries included, one-click installer for Microsoft Windows and was last updated quite some time ago, when our driver and adapter were called IBM_DB2.

IBM’s vision of extending the driver and adapter not only to DB2 for LUW (Linux/Unix/Windows), but also for the other IBM databases, was clearly highlighted by the renaming of the DB2 enablement from IBM_DB2 to IBM_DB.

The new driver and adapter have been released in the Open Source on Rubyforge and they will be continuously improved and supported by IBM. Bugs, suggestions and questions should be posted directly on the homepage of the project at Rubyforge. This release allows users to install a gem or, if they prefer, to get a plugin on an individual project basis. So what will happen to the Alphaworks project?

An updated toolkit will be published very soon, and as you may expect it will automatically install the ibm_db gem along with Ruby, Rails, DB2 9 Fix Pack 2 and more. Windows users who already have several components installed, and users of other operating systems, can directly setup a DB2 development environment through the gem or the plugin, without requiring the toolkit. Others who prefer the comfort of a one click installer, will still find the starter toolkit to be an excellent solution to get everything setup including DB2 itself. If you are still uncertain about something, please feel free to use the comments to ask further questions.

May 10 2007 | Tips&Tricks | 4 Comments »

Scaffolding XML columns as text area boxes

The Rails adapter and the Ruby driver for IBM DB2 enable you to take full advantage of the exclusive pureXML capabilities. XML fields are properly handled and also recognized when using migrations. We do not bind the content of XML columns to a specific XML Ruby representation (e.g. REXML) but rather let the user decide what they’d prefer to use, in light also of the fact that mapping to a simple string is often all that is required given the fast XQuery/XPath querying features provided out of the box by DB2.

The scaffolding generator ignores XML fields though. This is due to the fact that the bult-in scaffolding generates form elements for only a few datatypes. For example :time and :binary are excluded, and foreign keys are not handled as well. Users will typically want to handle XML fields in a customized way, for instance, showing only certain elements of the XML document in their forms. However if you wish to enable by default the automatic generation of text area boxes when using scaffolding, you can edit C:\ruby\lib\ruby\gems\1.8\gems\actionpack-1.12.5\lib\action_view\helpers\active _record_helper.rb (or the equivalent on your system) by replacing the to_tag method with the following:

def to_tag(options = {})
  case column_type
    when :string
       field_type = @method_name.include?("password") ? "password" : "text"
       to_input_field_tag(field_type, options)
    when :text,  ml
       to_text_area_tag(options)
    when :integer, :float
       to_input_field_tag("text", options)
    when :date
       to_date_select_tag(options)
    when :datetime, :timestamp
       to_datetime_select_tag(options)
    when :boolean
       to_boolean_select_tag(options)
   end
end

As you can see, all we are doing here is adding the XML datatype to the list of cases which require the rendering of a text area box.

January 06 2007 | How-to and Tips&Tricks | 1 Comment »

Speeding up the ibm_db2 adapter

If you would like to speed up the current 1.1 version of the adapter included in the Starter Toolkit for DB2 on Rails, you can replace the ‘tables’ and ‘colums’ methods from within the ibm_db2_adapter.rb file, with this code here.

This change will allow you to significantly boost your performance level when working with Rails and DB2. As you may note, the crucial change here relies on the fact that a slow driver call is replaced with an ad hoc query to the SYSCAT.

Naturally this will be included with the next toolkit release, which will be out soon.

August 21 2006 | Tips&Tricks | Comments Off