Ruby And Existing Databases

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, and 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.

 *----------------------------------------------------------------
 * @Name - ORDHDR
 * @Desc - Order header table.
 *-----------------------------------------------------------------
A                                      UNIQUE
A          R ORDHDRR                   TEXT('Order Header')
A            ORDNBR         9P 0       COLHDG('Order Number')
A            CSTNBR         9P 0       COLHDG('Customer Number')
A            DUE             Z         COLHDG('Due Date')
A          K ORDNBR


 *-----------------------------------------------------------------
 * @Name - ORDDTL
 * @Desc - Order detail table.     
 *-----------------------------------------------------------------
A                                      UNIQUE
A          R ORDDTLR                   TEXT('Order Detail')
A            ORDNBR         9P 0       COLHDG('Order Number')
A            LINNBR         9P 0       COLHDG('Line Number')
A            ITMNBR        10A         COLHDG('Item Number')
A            QTY            9P 0       COLHDG('Quantity')
A            PRC            9P 2       COLHDG('Price')
A          K ORDNBR
A          K LINNBR

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.

CRTLIB LIB(MYLIB)

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

CRTPF FILE(MYLIB/ORDHDR) SRCFILE(MYLIB/QDDSSRC) SRCMBR(ORDHDR)
CRTPF FILE(MYLIB/ORDDTL) SRCFILE(MYLIB/QDDSSRC) SRCMBR(ORDDTL)

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.

$ mkdir -p /home/aaron/gemsets/legacy_db2
$ export GEM_HOME=/home/aaron/gemsets/legacy_db2
$ export GEM_PATH=/home/aaron/gemsets/legacy_db2:/PowerRuby/prV2R0/lib/ruby/gems/2.0.0

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

$ gem install composite_primary_keys -v 6.0.7

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.

require 'active_record'
require 'ibm_db'
require 'composite_primary_keys'

ActiveRecord::Base.establish_connection(
  adapter: 'ibm_db', 
  database: '*LOCAL',
  username: 'MYUSR',
  password: 'MYPWD',
  schema: 'MYLIB',
  ibm_i_isolation: 'none'
)

class OrderHeader < ActiveRecord::Base 
  self.table_name = :ORDHDR
  self.primary_keys = :ordnbr
  has_many :details, class_name: :OrderDetail, foreign_key: [:ordnbr],
dependent: :destroy
end

class OrderDetail < ActiveRecord::Base
  self.table_name = :ORDDTL
  self.primary_keys = :ordnbr, :linnbr
  belongs_to :header, foreign_key: :ordnbr
end

OrderDetail.first

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.

irb(main):026:0* OrderDetail.first
=> #<OrderDetail ordnbr: #<BigDecimal:21187830,'0.1E1',9(18)>,
linnbr: #<BigDecimal:2118768c,'0.1E1',9(18)>, itmnbr: "HAT", qty:
#<BigDecimal:211874c0,'0.2E1',9(18)>, prc:
#<BigDecimal:21187308,'0.1234E2',18(18)>>

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.

require 'active_record'
require 'ibm_db'
require 'composite_primary_keys'

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.

ActiveRecord::Base.establish_connection(
  adapter: 'ibm_db', 
  database: '*LOCAL',
  username: 'MYUSR',
  password: 'MYPWD',
  schema: 'MYLIB',
  ibm_i_isolation: 'none'
)

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.

class OrderHeader < ActiveRecord::Base
  self.table_name = :ORDHDR
  self.primary_keys = :ordnbr
  has_many :details, class_name: :OrderDetail, foreign_key: [:ordnbr], dependent: :destroy
end

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’.

irb(main):029:0> OrderHeader.find(1).details
=> #<ActiveRecord::Associations::CollectionProxy [#<OrderDetail ordnbr:
#<BigDecimal:213504a0,'0.1E1',9(18)>, linnbr:
#<BigDecimal:213502fc,'0.1E1',9(18)>, itmnbr: "HAT", qty:
#<BigDecimal:21350158,'0.2E1',9(18)>, prc:
#<BigDecimal:2136178c,'0.1234E2',18(18) >>, #<OrderDetail ordnbr:
#<BigDecimal:21363ec4,'0.1E1',9(18)>, linnbr:
#<BigDecimal:21363d20,'0.2E1',9(18)>, itmnbr: "COAT", qty:
#<BigDecimal:21363b40,'0.1E1',9(18)>, prc:
#<BigDecimal:213639b0,'0.5E2',9(18)>>]>

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.

class OrderDetail < ActiveRecord::Base 
  self.table_name = :ORDDTL
  self.primary_keys = :ordnbr, :linnbr
  belongs_to :header, foreign_key: :ordnbr
end

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.

irb(main):034:0> OrderDetail.find(1,1).header
=> #<OrderHeader ordnbr: #<BigDecimal:215bbe74,'0.1E1',9(18) >, cstnbr:
#<BigDecimal:215bbcf8,'0.8888E4',9(18) >, due: "0001-01-01 00:00:00">

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.

Aaron Bartell is Director of IBM i Innovation for Krengel Technology, Inc. Aaron facilitates adoption of open source technologies on IBM i through professional services, staff training, speaking engagements, and the authoring of best practices within industry publications and www.litmis.com. With a strong background in RPG application development, Aaron covers topics that enable IBM i shops to embrace today’s leading technologies including Ruby on Rails, Node.js, Git for RPG source change management and RSpec for unit testing RPG. Aaron is a passionate advocate of vibrant technology communities and the corresponding benefits available for today’s modern application developers. Connect with Aaron via email at abartell@krengeltech.com. Aaron lives with his wife and five children in Southern Minnesota. He enjoys the vast amounts of laughter having a young family brings, along with camping and music. He believes there’s no greater purpose than to give of our life and time to help others.

RELATED STORIES

Ruby And DSLs And Blocks

IBM i Toolkit for Ruby, iit For Short

RubyGems Are The Foundation Of Success

A Ruby And RPG Conversation

Git To GitHub

Git To It

Knee-Deep In Ruby Waters

Testing The Ruby Waters

Bash Is Not A Shell Game