Using Ruby with Existing IBM i DB2 Database

(Originially published via IT Jungle on 5/5/15)

Recently I came across a scenario for a customer where I was asked:

"How do we interact with our existing DDS-based, composite key, database tables?"

That's going to be a very common trait of most all IBM i shops using Ruby.  This article will cover some common situations you will come across as you use Ruby to interact with existing DB2 for i tables.

First things first, let's lay out two tables defined with DDS, as shown below. Note how the ORDDTL table has a composite key. The ibm_db Gem follows the ActiveRecord pattern and that pattern expects surrogate keys by default and actually doesn't support composite keys out of the box.  Good thing "there's a Gem for that." A Gem named composite_primary_keys.  More on that Gem later.

We are going to put these into a library created with the CRTLIB command, as shown below. The more purist and better way would be to use SQL's CREATE COLLECTION command so journaling and other things are also included. Being able to change the creation of a library (a.k.a., schema) may be another thing you can't change so that's why we are going the route of CRTLIB.

And now create both of the DDS-based tables, as shown below.

Now add some data to the tables using the tool of your choice (e.g., UPDDTA). Before we can test our newly created tables we need to install the composite_primary_keys Gem. When I am testing Gems I like to install them into a separate folder for isolation purposes. That can be accomplished by modifying GEM_HOME before issuing the gem install command, as shown below.

And now install the Gem. Note a specific version of 6.0.7 is needed with Rails 4.0.x, per the documentation. 

We are now ready to test our newly created tables from a Ruby application. Typically you'd be accessing DB2 data from a RubyOnRails (Rails for short) application, but for the purposes of testing things I find it is much quicker to use irb (Interactive Ruby Shell).  To that end, start an irb session and paste the following code into it.

If everything went as expected you should have gotten something similar to the below results that conveys the data you entered in the previous step.

That was a lot of code we just pasted into irb and it would be good to digress through it. The require statements are bringing in the necessary Ruby libraries we require for our code to run. Go ahead and start a new irb session and leave one of those require statements off to see what type of error you get.

The next portion is connecting to the database using ActiveRecord. This is normal except for one new setting, ibm_i_isolation. This is IBM i specific and necessary so the database adapter doesn't attempt to use commitment control, which in turn requires journaling--schemas created with CRTLIB don't have journaling turned on by default.

Next we define a Ruby model class to represent the ORDHDR DB2 table. The first line is declaring the actual name of the table because ActiveRecord would, by default, look for a table named ORDERHEADERS. Why? It uses the name of the Ruby class, 'OrderHeader' and pluralizes it. This is a RubyOnRails convention. Because we aren't following that default convention we need to specify the self.table_name override. We also need to override the primary key because ActiveRecord, by default, is expecting a primary key of id.

The has_many declaration is neat. It is ActiveRecord's way of relating our two DB2 tables together and makes it so we can have some pretty sweet syntax to more easily traverse the database. Below is an irb session where we obtain a row from OrderHeader and immediately subsequently retrieve the corresponding OrderDetail rows. Under the covers it is doing an SQL SELECT with a WHERE CLAUSE having ordnbr set to '1'.

The equivalent record-level-access in RPG would have entailed a CHAIN to ORDHDR followed by a SETLL, READE, DOW, and one more READE. This is one thing that I thought no other language would ever beat RPG with--database access, but in comes Ruby and does it with excellence.

The next section of code is declaring the Ruby model class that represents the ORDDTL DB2 table. Here we see the composite_primary_keys Gem come into action with the specifying of both ordnbr and linnbr on the self.primary_keys line.

The other new syntax is the belongs_to line.  This is declaring the relationship in the other direction, from OrderDetail to OrderHeader. Now you can do the following syntax to quickly obtain the ORDHDR row for a particular ORDDTL row, as shown below. Note the .find(1,1) is the composite key method call that would be similar to a CHAIN.

As you might imagine there are many more ways to massage interaction with existing DB2 tables but we will have to save those for a future article. If you'd like to learn more about ActiveRecord associations (i.e., belongs_to, has_many) then head over to this link.

Need help using Ruby to interact with your DB2 for i database tables?

Have specific questions?  Email me directly at albartell@krengeltech.com.

Click here for code snippets from this article.