Originally published: 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, 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
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 => #, linnbr: #<bigdecimal:2118768c,'0.1e1',9(18)>, itmnbr: "HAT", qty: #<bigdecimal:211874c0,'0.2e1',9(18)>, prc: #<bigdecimal:21187308,'0.1234e2',18(18)>> </bigdecimal:21187308,'0.1234e2',18(18)></bigdecimal:211874c0,'0.2e1',9(18)></bigdecimal:2118768c,'0.1e1',9(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
class OrderHeader < ActiveRecord::Base self.table_name = :ORDHDR self.primary_keys = :ordnbr has_many :details, class_name: :OrderDetail, foreign_key: [:ordnbr], dependent: :destroy end
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 => #, linnbr: #<bigdecimal:213502fc,'0.1e1',9(18)>, itmnbr: "HAT", qty: #<bigdecimal:21350158,'0.2e1',9(18)>, prc: #<bigdecimal:2136178c,'0.1234e2',18(18)>>, #, linnbr: #<bigdecimal:21363d20,'0.2e1',9(18)>, itmnbr: "COAT", qty: #<bigdecimal:21363b40,'0.1e1',9(18)>, prc: #<bigdecimal:213639b0,'0.5e2',9(18)>>]> </bigdecimal:213639b0,'0.5e2',9(18)></bigdecimal:21363b40,'0.1e1',9(18)></bigdecimal:21363d20,'0.2e1',9(18)></bigdecimal:2136178c,'0.1234e2',18(18)></bigdecimal:21350158,'0.2e1',9(18)></bigdecimal:213502fc,'0.1e1',9(18)>
The equivalent record-level-access in RPG would have entailed a
ORDHDR followed by a
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
linnbr on the
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
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
irb(main):034:0> OrderDetail.find(1,1).header => #, cstnbr: #<bigdecimal:215bbcf8,'0.8888e4',9(18)>, due: "0001-01-01 00:00:00"> </bigdecimal:215bbcf8,'0.8888e4',9(18)>
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 with Ruby and DB2? We can help. Contact us.