Scanning…
Goto Main Page
Goto Documents
Goto Ruby Page
New PowerRuby meeting agreement i am updating YIPS with the latest xmlservice or ibm_db gem.
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
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.
schema.table
TOM user, means (TOM.)MYTABLE
set schema sally
(TOM.)MYTABLE ... ignore *LIBL
master: &master adapter: ibm_db # database: "*LOCAL" database: LP0364D username: DB2 # password: MYPWD pwd_yaml: ../password.yml development: <<: *master
*libl/table
any user, (*LIBL/)MYTABLE
(*LIBL/)MYTABLE ... honor *LIBL
MYLIB/MYTABLE == MLIB.MYTABLE
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
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
MyTabLE == mytable
. However, MySql DB2 Storage Engine installed below /QOpenSys (case sensitive)
, all names become case sensitive "MyThingYuck" != MyThingYuck
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).
<========== 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.
<........ 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).
# 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.
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”.
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
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
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!)
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) :
If you read above information on ibm_db connections, you will have a fairly easy time understanding XMLSERVICE jobs. XMLSERVICE offers two standard connections:
=== 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
ctl='*here'
)
ctl='*sbmjob' ipc='/tmp/tom1'
)
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’
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>
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' )