03 - Open Source (EN)03e - Ruby on Rails (EN)

PowerRuby: access to DB2 (Part 1)

Last Updated on 3 March 2020 by Roberto De Pedrini

In the meeting organized by FAQ400 on November 21, 2019 I held a presentation on the use of PowerRuby to generate Excel sheets with data pre-loaded by IBM i. I thought of getting two short articles to publish. Today we only deal with access to DB2.

My previous contribution already exists for the installation of PowerRuby. (PowerRuby CE2 – Ed.)

We only remember here that to verify the installation it is sufficient to add the POWER_SLNG library to the list of libraries and call the DSPSFWRSC command.

Scrolling through the list of installed products you should get to the resource 1PRUBY1. 

The following options are necessary for the functions described in this article.

                        View software resources
                                                          System: XXXXX400  
  ID  
  resource Opt.   Device.  Description      
  1PRUBY1 * BASE 5001 IBM i PowerRuby (administration utilities)  
  1PRUBY1 * BASE 2924 IBM i PowerRuby (administration utilities)         
  1PRUBY1 1 5002 IBM i PowerRuby Developer Edition (irubydb) 
  1PRUBY1 6 5001 IBM i PowerRuby (Ruby 2.4 + Rails 5.1)              

The fascinating aspect of Rails is certainly the ORM (Object-Relational Mapping) engine which is made up of the component known as ActiveRecords.

Beyond the theoretical problems of coexistence between relational model and object-oriented programming, which generate the so-called object-relational impedence mismatch (see Wikipedia, class of problems that the various ORM have tried to overcome) here we are interested – very pragmatically- see what you can do, with little effort, on IBM i.

As you known, many IBM i system commands offer the OUTPUT (* OUTFILE) option which allows us to extract the resulting content to a database file.

In the Milan presentation I had considered the DSPFFD command by applying it to a particularly interesting file: the QSYS / QADBXLFI file.

First of all, we use a good command shell (lately I got excited about the Terminal support offered by the Open Source Jupyter Lab project) and we check that both ruby and rails are accessible:

bash-4.4$ /PowerRuby/prV2R4/bin/ruby -v
ruby 2.4.4p296 (2018-03-28 revision 63013)[powerpc-aix7.1] 

bash-4.4$ /PowerRuby/prV2R4/bin/rails -v 
Rails 5.1.6 

We first generate a file called CLEVER with the DSPFFD command in the current library:

bash-4.4$ system "DSPFFD FILE(QSYS/QADBXLFI) OUTPUT(*OUTFILE) OUTFILE(*CURLIB/CLEVER)"

We can now become familiar with Ruby through the interactive interpreter (Interactive RuBy = irb):

bash-4.4$ /PowerRuby/prV2R4/bin/irb
 irb(main): 001:0>  

ActiveRecord can also be used without Rails, but to request its functions it is necessary to recall its gem with the require command:

irb(main):001:0> require 'active_record'
 => true 

Now we can define a Hash variable in which to record the configuration parameters for database access:

irb(main):002:0> conf = {:adapter => "irubydb", 
irb(main):003:1 *        :database => '*LOCAL',  
irb(main):004:1 *        :default_schema => '*LIBL', 
irb(main):005:1 *        :username => '*CURRENT',  
irb(main):006:1 *        :password => ''} 
 => {:adapter => "irubydb",:database => "*LOCAL",:default_schema => "*LIBL",:username => "*CURRENT",: password => ""} 

This information will be used to interact with another gem that will be implicitly loaded (the irubydb, part of the PowerRuby project). 

With these settings, we will use the SQL inline (not the server mode) also benefiting from the list of current libraries and the permissions of the files belonging to the active user. The method we need (establish_connection) is – evidently – offered by a class (Base) of the ActiveRecord component that we successfully called in the previous step (require):

irb(main):007:0> conn = ActiveRecord::Base.establish_connection(conf) 

Now we’re going to do a double sub-classing.

First, we derive the ApplicationRecord class from the ActiveRecord :: Base, preserving its abstract nature.

Next, we derive the Description class from the ApplicationRecord, setting the name of the table to the value ‘clever’ (setting the use of the lower case).

class ApplicationRecord<ActiveRecord::Base
 self.abstract_class = true 
end 

class Description<ApplicationRecord  
 self.table_name = 'clever'    
end   

This allows the Description class to inherit all the methods provided by ActiveRecord: the surprising thing is that we can already interact with the records of the CLEVER table:

s = Description.all()

Now with the map function we can interact with the result (supported in the variable s) by filtering only some fields (the name of the field -WHFLDI- and the description -WHFTXT-). 

Of the returned array we can show only some records (here the first 3).

s.map {|h| [h.whfldi, h.whftxt]}[0..2]

In the second part of the article we will deal with the records of the CLEVER file to generate an Excel sheet with them using an additional gem that we will see how to install.

Related Posts
DB2 for i SQL – String Manipulation – POSSTR-LOCATE-LOCATE_IN_STRING (EN)

Introduction Often, in our applications, we need to work with text strings, and DB2 SQL can come in very useful Read more

DB2 for i – FAQ & Howtos (EN)

DB2 Database and SQL ... maybe the most important things on IBM i platform: here's a collection of FAQs, tips Read more

IBM i 7.4 Announcement (En)

Comes directly with the Easter egg this IBM announcement for the news of the IBM i 7.4 version, iNext version Read more

Generated Always Columns (EN)

Introduction "Generated Always Column": are columns, table fields, filled by DB2 engine: something like columns with a default value but Read more

About author

Specialized in integrating open-source Web technologies in business-application development. PowerRuby Product Architect and C programmer.

Leave a Reply

Your email address will not be published. Required fields are marked *