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.

  • SQL Naming — schema.table
    • default schema same name as user profile connect TOM user, means (TOM.)MYTABLE
      • Each request changing schema from default is expensive set schema sally
    • LIBL has NO effect on unqualified SQL operations (TOM.)MYTABLE ... ignore *LIBL
    • SQL naming typical for ported Linux Rails application
    • config.yml
      master: &master
        adapter: ibm_db
        # database: "*LOCAL"
        database: LP0364D
        username: DB2
        # password: MYPWD
        pwd_yaml: ../password.yml
      development:
        <<: *master
      
  • System naming — *libl/table
    • default schema/lib follows usual curlib/libl rules any user, (*LIBL/)MYTABLE
      • Each request changing *LIBL is expensive, better to set profile *LIBL (one time connect)
    • LIBL DOES effect unqualified SQL operations (*LIBL/)MYTABLE ... honor *LIBL
    • V7R1+ both dot and slash syntax are supported MYLIB/MYTABLE == MLIB.MYTABLE
    • System naming typical for IBM i application
    • config.yml
      master: &master
        adapter: ibm_db
        # database: "*LOCAL"
        database: LP0364D
        username: DB2
        # password: MYPWD
        pwd_yaml: ../password.yml
      production:
        ibm_i_naming: system
        ibm_i_libl: BOB QTEMP
        ibm_i_curlib: BOB
        <<: *master
      

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

  • MyTabLE == mytable … any combination will work IBM i DB2
  • "MyTable" != mytable … double quotes becomes part of actual name and all characters within are significant
  • MySql installed “usually” case insensitive directories, therefore all names for DB2 Storage Engine are also case insensitive MyTabLE == mytable . However, MySql DB2 Storage Engine installed below /QOpenSys (case sensitive) , all names become case sensitive "MyThingYuck" != MyThingYuck

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

  • First, each connection can be assigned its own user profile.
  • Having separate jobs for each connection also has the benefit of keeping transactions independent from each other. This lets the CLI application process multiple connections at once and then commit or roll back one connection without affecting the others.
  • Server mode also circumvents the SQL limitation of one connection to each system, including the local system
  • The SQL CLI lets a far greater number of handles be allocated in server mode. Handles are the mechanism by which a CLI program distinguishes between all the connections and statements it is using.
  • Since the SQL is processed in another job, the amount of shared resources across connections is drastically reduced. Simply put, more work can be done all at once, since they aren’t all working with structures and memory in the same process. This allows threaded applications that use a different connection handle per thread to get much better performance, and more effectively exploit threads with SQL.
  • Server mode is also a better fit for applications that use threads. Provided each thread allocates its own connection handle, an application can use threads to process multiple usersí requests at the same time. Because each thread runs independently, using its own connection, one thread wonít interfere in any way with the handles used by another threadís connection. When not in server mode, the CLI must use mutex logic to prevent more than one thread from running SQL statements at the same time.
  • Statement handles must not be shared across threads. If threads are being used with Server Mode, each thread should allocate its own statement handles, or the application should have synch points to prevent multiple threads from working with the same handle at the same time. This is because one thread could overwrite return data or error information that another thread has yet to process.
  • The prestart jobs used for CLI server mode run in subsystem QSYSWRK. To display the subsystem description, use the DSPSBSD
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”.

  • Full connection acquires a QSQ server job, uses for life of the script, then returns QSQ to pool on exit (or close)
    Ruby PASE client -> thread 1 <profile Tom connect until script ends  > QSQSRVR (db2 proxy)
                     -> thread 2 <profile Sally connect until script ends> QSQSRVR (db2 proxy)
                     -> thread n <profile Lisa connect until script ends > 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"
    - profile ibm_db connection is closed and returned to QSQ prestart pool on script end (or ibm_db close)
    
    Typical usage "proxy" Rails:
    Apache<proxy>ruby Rails application 1 (thin)-> thread 1 <profile Tom connect activer record  > QSQSRVR (db2 proxy)
          <proxy>ruby Rails application 2 (thin)-> thread 1 <profile Sally connect activer record> QSQSRVR (db2 proxy)
    
    Attributes of "proxy" Rails:
    - Rails application is started independently of Apache (thin or webrick, etc.) 
    - Apache forwards requests to Rails application via proxy (VirtualHost, Alias, etc.)
    - Rails application tends to leave connection open for life of job started
    
  • Persistent connection acquires a QSQ server job, uses for the life of job/process, NEVER returns the QSQ job to pool (unless job ends).
    • Important to note as you read about “persistent connection” or “pconnect” … “persistent connection” does NOT imply your Browser<click>Apache will return to the same Ruby<connect>QSQ job. Apache has a completely seperate FastCGI mechanism “routing” incoming browser requests to any “available” Ruby child.
      Ruby PASE client -> thread 1 <profile Tom pconnect life of job       > QSQSRVR (db2 proxy)
                       -> thread 2 <profile Sally pconnect life of job     > QSQSRVR (db2 proxy)
                       -> thread n <profile Lisa pconnect life of job      > 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"
      - profile ibm_db connection is NEVER closed, NEVER returned to QSQ prestart pool
      - when job ends all QSQ jobs will return to the QSQ pool
      
      Typical usage "high speed" fast cgi:
      Apache<fastcgi>ruby job 1 -> thread 1 <profile Tom pconnect life of job       > QSQSRVR (db2 proxy)
                                -> thread 2 <profile Sally pconnect life of job     > QSQSRVR (db2 proxy)
                                :
            <fastcgi>ruby job 2 -> thread 1 <profile Tom pconnect life of job       > QSQSRVR (db2 proxy)
                                -> thread 2 <profile Sally pconnect life of job     > QSQSRVR (db2 proxy)
                                :
            <fastcgi>ruby job n -> thread 1 <profile Tom pconnect life of job       > QSQSRVR (db2 proxy)
                                -> thread 2 <profile Sally pconnect life of job     > QSQSRVR (db2 proxy)
                                :
      Attributes of fastcgi (persistent connection):
      - reusing Ruby active pool of "worker" jobs for each browser request (more jobs, handle more requests)
      - Ruby job does work on browser "click" request, then returns data and leaves DB connection "attached"
      - (number of QSQ jobs) = (number of "worker" Ruby jobs) * (number active profiles)
                               Assuming one thread per profile == one connection
      
    • Yes, FastCGI also works with “full connection” or “connect”, but AFTER each browser request/click is handled, Ruby will close the ibm_db connection and return the QSQ server job back to the pre-start pool (about 2x-10x slower than persistent connection).
      Apache<fastcgi>ruby job 1 -> thread 1 <profile Tom connect life of script     > QSQSRVR (db2 proxy)
                                -> thread 2 <profile Sally connect life of script   > QSQSRVR (db2 proxy)
                                :
            <fastcgi>ruby job 2 -> thread 1 <profile Tom pconnect life of script    > QSQSRVR (db2 proxy)
      
      Attributes of fastcgi (full connection):
      Ruby job does work on browser "click" request
      1) Ruby ibm_db connect attaches a QSQ server job (proxy)
      2) Ruby ibm_db completes expected ibm_db tasks (select, insert, update, call, etc.)
      3) Ruby ibm_db closes connect "automatically" script end, returns QSQ to pre-start pool 
      4) Ruby sends data back to client brower
      *) Ruby must repeat all steps each browser request/click (Whew!)
      

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:

  • db2 connection (see ibm_db above) — XML is transported over Db2 stored procedures to XMLSERVICE (xmlservice.pgm)
  • REST HTTP connection — XML is transported over normal encoded HTTP to XMLSERVICE (xmlcgi.pgm)
  • Information on both connections can be located following links:
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
  • public XMLSERVICE connection (xmlservice in QSQ job ctl='*here' )
    • xmlservice will reset to nothing end of each request (“stateless”)
  • private XMLSERVICE connection (xmlservice one job beyond QSQ job ctl='*sbmjob' ipc='/tmp/tom1' )
    • xmlservice will maintain full state for user (“state full”)
    • automatic synchronization one-at-a-time built into xmlservice (you do nothing)
    • typical RPG program needs state between calls? xmlservice “private” may be you best friend.
      • keep files open, chain, variables same, just like 5250 programming (mostly)
  • ibm_db connection follow normal Ruby/Rails
    • xmlservice with ibm_db connection (“stateless” == *here)
      ActiveXMLService::Base.establish_connection(
        connection: 'ActiveRecord'
        adapter: 'ibm_db'
        install: XMLSERVICE
        database: '*LOCAL',
        username: 'USER400'
        password: 'secret'
        ctl: '*here'
        ipc: '/none'
        size: 15000000
        head: "<?xml version='1.0'?>"
      )
      
      -- or (defaults, using already active ibm_db connection) --
      
      ActiveXMLService::Base.establish_connection connection: ĎActiveRecordí 
      
      
  • REST connection xmlcgi.pgm is included with xmlservice download
    • RPG xmlcgi uses same basic CLI as ibm_db CLI (everything just works)
    • Configuration is simple Apache ILE CGI
      ScriptAlias /cgi-bin/ /QSYS.LIB/POWER_RUBY.LIB/
      <Directory /QSYS.LIB/POWER_RUBY.LIB/>
        AllowOverride None
        order allow,deny
        allow from all
        SetHandler cgi-script
        Options +ExecCGI
      </Directory>
      
    • xmlservice with REST connection (“stateless” == *here)
      ActiveXMLService::Base.establish_connection(
        connection: "http://myibmi/cgi-bin/xmlcgi.pgm"
        install: XMLSERVICE
        database: '*LOCAL',
        username: 'USER400'
        password: 'secret'
        ctl: '*here'
        ipc: '/none'
        size: 15000000
        head: "<?xml version='1.0'?>"
      )
      
      -- or (use more defaults) --
      
      ActiveXMLService::Base.establish_connection ( 
        connection: "http://myibmi/cgi-bin/xmlcgi.pgm"
        database: '*LOCAL'
        username: 'USER400'
        password: 'secret'
      )