From YiPs Wiki (i powered)

XMLSERVICE: XMLSERVICE Ruby Tony Work

(click to open)

Quick Page Table of Contents

Scanning…

Ruby Toolkit Tony …

Goto Main Page
Goto Documents
Goto Ruby Page

New PowerRuby meeting agreement i am updating YIPS with the latest xmlservice or ibm_db gem.

Changelogs moved to main ruby page links

Samples moved to individual pages

A few irb sessions

Helper for irb. 

IBM_DB session:
irb(main):002:0> require 'active_support'
irb(main):004:0> require 'ibm_db'
irb(main):006:0> conn = IBM_DB.connect "*LOCAL","DB2","secret"
irb(main):007:0> stmt = IBM_DB.exec conn,'select * from animals'
irb(main):008:0> IBM_DB.fetch_assoc stmt

IBM_DB ActiveRecord session:
2.0.0p247 :001 > require 'active_support'
2.0.0p247 :005 > require 'active_record'
2.0.0p247 :002 > require 'ibm_db'
2.0.0p247 :006 > ActiveRecord::Base.establish_connection adapter: 'ibm_db', database: 'LP0364D', username: 'DB2',password: 'secret'
2.0.0p247 :010 > class AnimalActiveRecord < ActiveRecord::Base; self.table_name = "ANIMALS"; end;
2.0.0p247 :013 > AnimalActiveRecord.order('id').select("id, breed").take(2).each{ |row| puts row }

XMLSERVICE REST session:
2.0.0p247 :001 > require 'xmlservice'
2.0.0p247 :002 > ActiveXMLService::Base.establish_connection connection: "http://myibmi/cgi-bin/xmlcgi.pgm",database: '*LOCAL', username: 'DB2',password: 'secret'
2.0.0p247 :003 > cmd = XMLService::I_SH.new("system -i 'WRKSYSVAL SYSVAL(QTIME) OUTPUT(*PRINT)'")
2.0.0p247 :004 > cmd.xmlservice
2.0.0p247 :008 > cmd.out_xml

XMLSERVICE ActiveRecord session:
2.0.0p247 :001 > require 'active_support'
2.0.0p247 :005 > require 'active_record'
2.0.0p247 :002 > require 'ibm_db'
2.0.0p247 :006 > ActiveRecord::Base.establish_connection adapter: 'ibm_db', database: 'LP0364D', username: 'DB2',password: 'secret'
2.0.0p247 :014 > require 'xmlservice'
2.0.0p247 :015 > ActiveXMLService::Base.establish_connection connection: 'ActiveRecord'
2.0.0p247 :016 > cmd = XMLService::I_SH.new("system -i 'WRKSYSVAL SYSVAL(QTIME) OUTPUT(*PRINT)'")
2.0.0p247 :017 > cmd.xmlservice
2.0.0p247 :018 > puts cmd.out_xml

ibm_db gem — “system naming” vs. “sql naming”

When using ibm_db gem, IBM i has two modes of running SQL operations though CLI (ODBC super set). As of DB2 Connect version 10.5, Linux, Unix, Windows support BOTH ‘SQL naming’ and ‘system naming’, therefore PowerRuby project is modifying ibm_db gem to work both on IBM i and remote to IBM i.

ibm_db gem — UPPER and lower case names

Many names in ibm_db gem are forced to lower case as convention. Lower case is fine when interacting with DB2, IBM i is case insensitive for all DB2 names UNLESS you specify in double quotes. PASE products like Ruby operate case insensitive, unless the product is installed under root directory /QOpenSys (IFS case sensitive file system root).

ibm_db gem — Ruby GIL thread locking

Ruby provides a global interpreter lock (GIL or GVL), whereby only one Ruby script thread is running at a time. However in c code gems, a Ruby c code function rb_thread_blocking_region is provided for releasing GVL, this method is used by ibm_db gem driver (ibm_db.c).

According to Aaron research Rails is connect thread safe …

A connection pool synchronizes thread access to a limited number of database connections. The basic idea is that each thread checks out a database connection from the pool, uses that connection, and checks the connection back in. ConnectionPool is completely thread-safe, and will ensure that a connection cannot be used by two threads at the same time, as long as ConnectionPool’s contract is correctly followed. It will also handle cases in which there are more threads than connections: if all connections have been checked out, and a thread tries to checkout a connection anyway, then ConnectionPool will wait until some other thread has checked in a connection.

ILE documentation vs. scripting language …
We are running like Java with Ruby in “server mode”, which is completely different than threaded ILE documentation examples carefully crafted to demonstrate declare/open/fetch child threads (below).

1) ILE c example DB2 I/O operation are completely thread safe …
<========== one process/job ==========================>
ILE c -> thread 1 <same process declare/open/fetch> DB2 
      -> thread 2 <same process declare/open/fetch> DB2 
         :
      -> thread n <same process declare/open/fetch> DB2 

Attributes of single job:
- one transaction boundary == one user profile active (CLI view)
- DB2 does much mutex work for thread shared structures (slow)
- MANY exceptions mixing profiles in same job 
- MANY "one job" restrictions about connections, transactions, profile active, etc.
2) Ruby/Rails running like Java using “server mode” (idealized use picture) …
<=== job client ============>                                        <=====  job(s) server DB2 proxy (QSQ) ======>
Ruby PASE client -> thread 1 <profile Tom connection memory shared   > QSQSRVR (db2 proxy)<-.
                 -> thread 2 <profile Sally connection memory shared > QSQSRVR (db2 proxy)  | SHARE RESTRICTED
                    :                                                                       | "Rails lock control"
                 -> thread 3 <profile Tom idea "share connection"    >----------------------.
                    :
                 -> thread n <profile Lisa connection memory shared  > QSQSRVR (db2 proxy)
Attributes of "server mode" multiple jobs:
- profile ibm_db connection established a transaction boundary using an attached QSQ server job for "isolation"
- ALL threads run DB2 operations at same time (BIG data design)
- should not "share a connection" across threads
- if you want to share (not recommended), application will need to sync with lock 
Pro:
- no locks at application level runs faster, but requires "smarter" users
- QSQ can be pooled without release to QSQ pool (2x - 10x faster)
- deadlock/hang not likely
- GIL released will allow Ruby interpreter to keep on running other threads
Con:
- some administrators are "put off" by many QSQ jobs in WRKACTJOB

Optional…

3) We COULD allow user control over the “server mode” flag, allow customers opportunity to be expert (shoot self) …
<========== one process/job =========================================>
Ruby PASE client -> thread 1 <Tom same process declare/open/fetch> DB2 
                 -> thread 2 <Tom same process declare/open/fetch> DB2 
                    :
                 -> thread n <Tom same process declare/open/fetch> DB2 

Rules:
- one transaction boundary == one user profile active (CLI view)
- cannot mix "server mode" and "single mode" (once enter server mode, always in server mode -- never undo)
- ILE called programs do NOT have thread scoped threaded storage (stored procedures unsafe most part)
- IBM i Operating System has MANY "process scoped locks", so you may "hang" a job
Pro:
- match ILE examples for threading 
- GIL released will allow Ruby interpreter to keep on running other threads
Con:
- DB2 does much mutex work for thread shared structures (slow)
- Not clear that all stored procedure calls will work as expected (ILE has no thread scoped static data)
- overall slower multi-request synchronous running over "server mode"
- MANY exceptions mixing profiles in same job 
- MANY "one job" restrictions about connections, transactions, profile active, etc.

ibm_db gem — why DB2 “server mode”?

First, read GIL locking to make sure you understand Ruby use of “server mode”. Next, ibm_db gem is not fully designed exact nature of “connection pooling”, but it is fairly safe bet that DB2 “server mode” will play a prominent role.
                             <........ DB2 "Server Mode" ............>
<=== job client =============>                                       <=====  job(s) server DB2 proxy (QSQ) ======>
Ruby PASE client -> thread 1 <profile Tom connection memory shared   > QSQSRVR (db2 proxy)<-.
                 -> thread 2 <profile Sally connection memory shared > QSQSRVR (db2 proxy)  | SHARE RESTRICTED
                    :                                                                       | "Rails lock control"
                 -> thread 3 <profile Tom idea "share connection"    >----------------------.
                    :
                 -> thread n <profile Lisa connection memory shared  > QSQSRVR (db2 proxy)
Attributes of "server mode" multiple jobs:
- profile ibm_db connection established a transaction boundary using an attached QSQ server job for "isolation"
- should not "share a connection" across threads
- if you want to share (not recommended), application will need to sync with lock

There are multiple good references for DB2 “server mode”:

Ruby ibm_db gem will likely take advantage of DB2 “server mode” for reasons outlined in above articles (subset below).

Good and bad multi-threaded test case …
# bad test -- connection is shared across threads
connection = ActiveRecord::Base.connection
threads = (1..25).map do
  Thread.new do
    begin
      10.times do
        connection.execute("set schema mylib")  # WRONG
      end
      # puts "success"
    rescue => e
      puts e.message
    end
  end
end
threads.each(&:join)

# good test -- connection is acquired inside new thread  
threads = (1..25).map do
  Thread.new do
    begin
      10.times do
        ActiveRecord::Base.connection.execute("set schema mylib")  # CORRECT
      end
      # puts "success"
    rescue => e
      puts e.message
    end
  end
  ActiveRecord::Base.connection.close
  Thread.exit
end
threads.each(&:join)

Note:
-- You may find trouble with puts and threads, comment out.

ibm_db gem — pconnect vs. connect

First, read GIL locking to make sure you understand Ruby use of “server mode”. Next, ibm_db gem offers a choice for DB2 connections “full connection” and “persistent connection”.

ibm_db gem — What of QTEMP??

As you read the previous topic on DB2 “server mode”, and “connect” vs. “pconnect”, you realize QTEMP is no longer in a fixed location like it was in your RPG programming days. Therefore you may need to learn a new bag of tricks when it comes to QTEMP. The most common technique stop using QTEMP and start using fixed location tables QTEMP001. However using XMSLERVICE (xmlservice gem) you can regain QTEMP by calling RPG programs in the job with the QTEMP you desire (see xmlservice gem). 

Typical usage "proxy" Rails:
Apache<proxy>ruby Rails application 1 (thin)-> thread 1 <profile Tom connect activer record  > QSQSRVR (QTEMP)
      <proxy>ruby Rails application 2 (thin)-> thread 1 <profile Sally connect activer record> QSQSRVR (QTEMP)

Typical usage "high speed" fast cgi:
Apache<fastcgi>ruby job 1 -> thread 1 <profile Tom pconnect life of job       > QSQSRVR (QTEMP)
                          -> thread 2 <profile Sally pconnect life of job     > QSQSRVR (QTEMP)
                          :
      <fastcgi>ruby job 2 -> thread 1 <profile Tom pconnect life of job       > QSQSRVR (QTEMP)
                          -> thread 2 <profile Sally pconnect life of job     > QSQSRVR (QTEMP)
                          :
      <fastcgi>ruby job n -> thread 1 <profile Tom pconnect life of job       > QSQSRVR (QTEMP)
                          -> thread 2 <profile Sally pconnect life of job     > QSQSRVR (QTEMP)
                          :

xmlservice gem — connection

If you read above information on ibm_db connections, you will have a fairly easy time understanding XMLSERVICE jobs. XMLSERVICE offers two standard connections:

Summary of connections relative to ibm_db discussion:
                                                  ===     XMLSERVICE         ===           ===   XMLSERVICE    ===
No DB connection (REST):                          ===public job ("stateless")===           ===private job (who)===
Apache <CGI>xmlcgi.pgm RPG CLI-> <profile Tom   > QSQSRVR (proxy)----------------.
       <CGI>xmlcgi.pgm RPG CLI-> <profile Sally > QSQSRVR (XMLSERVICE '*here')   :
                                                                                 :         ===private job (tom)===
                                                                        ipc='/tmp/tom1'--> (XMLSERVICE job *sbmjob)
                                                                                 :
Typical usage "proxy" Rails (ibm_db):                                            :
Apache<proxy>Rails app1 (thin)-> thread 1 <Tom  > QSQSRVR (proxy)----------------.
      <proxy>Rails app2 (thin)-> thread 1 <Sally> QSQSRVR (XMLSERVICE '*here')   :
                                                                                 :
                                                                                 :
Typical usage "high speed" fast cgi  (ibm_db):                                   :
Apache<fastcgi>ruby1 -> thread 1 <profile Tom   > QSQSRVR (proxy)----------------:
                     -> thread 2 <profile Sally > QSQSRVR (XMLSERVICE '*here')   :
                      :                                                          :
      <fastcgi>ruby2 -> thread 1 <profile Tom   > QSQSRVR (XMLSERVICE '*here')   :
                     -> thread 2 <profile Sally > QSQSRVR (XMLSERVICE '*here')   :
                     :                                                           :
      <fastcgi>rubyn -> thread 1 <profile Tom   > QSQSRVR (proxy)----------------.
                     -> thread 2 <profile Sally > QSQSRVR (proxy)------------------.
                     :                                                             :       ===private job (sally)===
                                                                        ipc='/tmp/sal1'--> (XMLSERVICE job *sbmjob)

Note:
- QSQSRVR proxy is actually XMLSERVICE running "client mode" passing XML to XMLSERVICE "private job(s)"
- public job XMLSERVICE is running both client/server inside QSQSRVR job
Retrieved from http://youngiprofessionals.com/wiki/index.php/XMLSERVICE/XMLSERVICERubyTonyWork
Page last modified on December 02, 2015, at 12:24 PM EST