This post originated from an RSS feed registered with Ruby Buzz by Leon Katsnelson. Original Post: Troubleshooting DB2 on Rails Feed Title: DB2 on Rails Feed URL: http://feeds.feedburner.com/DB2OnRails Feed Description: Agile development for enterprises large and small Latest Ruby Buzz Posts Latest Ruby Buzz Posts by Leon Katsnelson Latest Posts From 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.