JRubyでは、Ruby関数とJava関数を組み合わせることで、AS400といったJDBCが必須のDBアクセスが容易に行なえます。
確かに、AS400へのDB接続を検索すると、多くはAS400の内部でJavaやphpその他の言語を動かすといったコンテンツがメインで、外部サーバーのWeb言語からAS400のDBをアクセスする…というコンテンツは見かけません。
ですので、少ないネット情報を頼りに、外部サーバーのWebシステムから、AS400のDBへアクセスできないか、試してみることにしましょう。
- AS400へのアクセスはJRuby＋JDBCで
- 準備とインストール
- SELECTの実行
- INSERTの挙動は？
- プレースホルダの使用
- 文字種による挙動まとめ
- 参考：ActiveRecord(非推奨)
- 参考：JRuby＋Sequelのほうが楽では？
- まとめと課題
AS400へのアクセスはJRuby＋JDBCで
外部サーバーからAS400のDBへアクセスする手段は、一般的にODBC経由やJDBC経由で…といったところでしょうか。
ただ、ODBCはWindowsからが前提のようで、現実的ではありません。
代替手法として、IBMはibm_dbというgemを使用した手法を推しているらしいのですが、情報に乏しく、またIBMへの申請が必要なようで、手軽に試せるといったものではなさそうです。
であれば、JDBCはいかがでしょうか？
個人的には、他の言語でJDBC経由でAS400へアクセスした経験はありますが、これはその言語がJavaベースで、JDBCと親和性が高かったことが大きいです。
一方、C言語系で実装されたWeb言語やスクリプト言語では、JDBC接続は困難かもしれません。
(例えば「php JDBC」で検索しても、期待した情報はまず見つかりません。それでもPythonではJDBC接続用のモジュールはあるようですが…)
その中の、C言語ベースのRubyでも、Javaを使うためのプラグインとしてgem rjbがあるようです。
しかし、Windows用のバイナリが存在せず、gemはソースからビルドを試みようとします。が、Linux標準のコンパイラ「gcc」がWindowsにはなく、結果、エラーとなるため、作業は頓挫しました。
しかし、Ruby系には、Java上で動作するJRubyがあり、こちらはJDBC接続が用意されているようですので、AS400のDBへのアクセスに希望が持てそうです。
結果はアクセス可能でしたが、AS400では扱える文字コードに制限があるため、クリアすべき問題もありそうです。
(こちらは、最後の章「まとめと課題」を参照ください。)
準備とインストール
それではさっそく、インストールしてみましょう。
AS400側の確認
JDBCでのアクセスのためには、サブシステムQUSRWRKにQZDASOINITが起動されている必要があります。
WRKACTJOBにて動作を確認していただき、OKであれば、次へ進むことにしましょう。
参考：【できるIBM i 7.4解剖】第9回 「Db2 for iのODBC/JDBCサーバージョブ QZDASOINITのパフォーマンス調整」
www.i-cafe.info
JDKのインストール
次にJDKのインストールです。
Javaが存在しない場合は、「OpenJDK 17 のインストールと設定（Windows 上）」を参考にJDKをインストールしておきます。
C:ドライブ直下のフォルダ(私の場合はC:\jdk-19.0.1)にインストールされ、環境変数JAVA_HOMEに同フォルダーが設定されていればOKです。
もし、最新版で失敗する場合は、アーカイブから、それよりも古いJDKをインストールしてください。
私の環境では、一番古い「9.0.4 (build 9.0.4+11)」でも動作しました。
JRubyのインストール
執筆時では最新の「JRuby バージョン 9.2 のインストール（Windows 上）」を参考にJRubyをインストールします。
(結果、C:\jruby-9.3.9.0にインストールされました。)
シェルで、
jruby -v
でバージョンが表示されれば、インストールが成功。
先程インストールしたJDKのバージョンを確認したい場合は…
jirb > ENV_JAVA['java.version'] # >は入力しない => "x.x.x_xxx"
で確認可能です。
jt400.jarの入手
「JRuby Connection to AS400 DB」によると、「JTOpen: The Open Source version」にオープンソース版があるとのこと。
また、AS400からも入手可能。私はこちらを利用しました。
(入手先は、\\(AS400のIP)\qibm\ProdData\HTTP\Public\jt400\lib)
入手した、jt400.jarはJRubyのlibフォルダへコピーしてください。
(私の環境ではC:\jruby-9.3.9.0\libにjt400.jarをコピー。)
上記のコピーを失念すると、JRuby実行時に下記のエラーが発生しますので注意。
Unhandled Java exception: java.sql.SQLException: No suitable driver found for jdbc:as400://192.168.xx.xx; java.sql.SQLException: No suitable driver found for jdbc:as400://192.168.xx.xx;
SELECTの実行
あとは、プログラムを作成し実行します。
プログラム例
今回はGemfileは不要。
テストプログラムを作成するだけです。
下記ソースは前章のもの参考にしましたが、jt400.jarはJRubyのlibフォルダへコピー済みですので、jt400.jarのrequireは不要です。
また、実行後にクローズもしましょう。
●test.rb
require 'java' #require './jtopen_11_0/lib/java8/jt400.jar' #require_relative 'jt400/lib/jt400.jar' java_import 'com.ibm.as400.access.AS400JDBCDriver' # 古いバージョンではjava_importではなくこの記述になるらしい # To use on older versions of JRuby #driverclass="com.ibm.as400.access.AS400JDBCDriver" #java.lang::Class.forName(driverclass).newInstance # 例題は # "jdbc:as400://server;naming=sql;errors=full", user = "QPGMR" pass = "QPGMR" conn = java.sql.DriverManager.getConnection( "jdbc:as400://192.168.xx.xx;transaction isolation=none;", user, pass) # SQL文を実行してみる stmt = conn.createStatement rs = stmt.executeQuery("SELECT * FROM LIBNAME.DBNAME WHERE TBTYPE = 'HOGE'") while (rs.next) do puts rs.getString("TBNAME") end # 念のため rs.close stmt.close conn.close
SELECT文は、各自のライブラリとPF/LF、フィールド名を指定してください。
実行
実行は…
jruby test.rb
シェル上にテーブルのデータが表示されれば成功です！
コマンドは、rubyではなく、jrubyであることに注目してください。
jrubyで実行する…ということは、オリジナルのRubyとJRubyが同居できることに気づくはずです。
なお、
"jdbc:as400://192.168.xx.xx;transaction isolation=none;libraries=LIB1,LIB2;",
とした場合は、ライブラリ名を省略することも可能です。
文字列はダブルクオーテーションで囲った場合のみ「#{変数名}」で置き換えることもできるので、テスト環境、本番環境でうまく差し替えることもできるはずです。
INSERTの挙動は？
プログラム例
以下はプログラム例。
●test_ins.rb
require 'java' java_import 'com.ibm.as400.access.AS400JDBCDriver' user = "QPGMR" pass = "QPGMR" conn = java.sql.DriverManager.getConnection( "jdbc:as400://192.168.xx.xx;transaction isolation=none;", user, pass) # SQL文を実行してみる stmt = conn.createStatement stmt.executeUpdate("DELETE FROM LIBNAME.DBNAME WHERE TBTYPE = 'HOGE' AND TBCODE LIKE 'JD%'") stmt.executeUpdate("INSERT INTO LIBNAME.DBNAME (TBTYPE, TBCODE, TBNAME) VALUES ('HOGE', 'JD01', 'ABC')") stmt.executeUpdate("INSERT INTO LIBNAME.DBNAME (TBTYPE, TBCODE, TBNAME) VALUES ('HOGE', 'JD02', 'abc')") stmt.executeUpdate("INSERT INTO LIBNAME.DBNAME (TBTYPE, TBCODE, TBNAME) VALUES ('HOGE', 'JD03', 'ａｂｃ')") stmt.executeUpdate("INSERT INTO LIBNAME.DBNAME (TBTYPE, TBCODE, TBNAME) VALUES ('HOGE', 'JD04', '①')") stmt.executeUpdate("INSERT INTO LIBNAME.DBNAME (TBTYPE, TBCODE, TBNAME) VALUES ('HOGE', 'JD05', '你好')") stmt.executeUpdate("INSERT INTO LIBNAME.DBNAME (TBTYPE, TBCODE, TBNAME) VALUES ('HOGE', 'JD06', '𩸽')") stmt.executeUpdate("INSERT INTO LIBNAME.DBNAME (TBTYPE, TBCODE, TBNAME) VALUES ('HOGE', 'JD07', '🍣🍺')") stmt.close conn.close
java_importはJRuby独自の関数。getConnection、createStatement、executeUpdate、closeはJavaの方の関数です。
実行
実行は…
jruby test_ins.rb
どのようにINSERTされたか、再度SELECTして表示してみましょう。
| # | TBCODE | TBNAME | 種別 | 備考 |
|---|---|---|---|---|
| 1 | JD01 | ABC | 半角英大文字 | |
| 2 | JD01 | abc | 半角英小文字 (5026ではサポートされない) |
|
| 3 | JD01 | ａｂｃ | 全角文字 | |
| 4 | JD01 | ① | 5026ではサポートされない文字 | |
| 5 | JD01 | 你好 | 同 | |
| 6 | JD01 | 同、サロゲートペア | 文字化け | |
| 7 | JD01 | 同、絵文字 | 文字化け |
サロゲートペアと絵文字が取得できません…
壊れてしまったようですね。
INSERT時の挙動をまとめると、
- JDBCを経由すると、DBのCCSIDの値に関わらず、全ての文字がINSERTされる(例外が発生しない)
(使用したDBはCCSID=5026) - サロゲートペア以外のUTF-8文字は破壊されない (ただし、AS400側から見ることはできず、外字同様に「・」となる)
- サロゲートペア文字(4バイトUTF-8)や絵文字は現状サポート外と思われ、DFUでは「検索されたレコードに正しくないデータが入っている。」メッセージが表示され、そのレコード自体を見ることができない
(値が壊れている？)
なお、照会系は、
rs = stmt.executeQuery("SELECT * FROM LIBNAME.DBNAME WHERE TBTYPE = 'HOGE'")
ですが、更新系は、
stmt.executeUpdate("INSERT INTO LIBNAME.DBNAME (TBTYPE, TBCODE, TBNAME) VALUES ('HOGE', 'JD01', 'ABC')")
と、executeUpdate()を使用することに注意。
executeQuery()でINSERT/UPDATE/DELETEすると…
Unhandled Java exception: java.sql.SQLException: Cursor state not valid.
エラーとなってしまうので、注意。
(INSERT/UPDATE/DELETEではカーソルがないので。)
プレースホルダの使用
プログラム例
前章のINSERTプログラムでは、値を直接SQL文に書き込みましたが、「prepareStatement／executeUpdateメソッド」を参考に、プレースホルダを使用してみましょう。
●test_ins2.rb
require 'java' java_import 'com.ibm.as400.access.AS400JDBCDriver' user = "QPGMR" pass = "QPGMR" conn = java.sql.DriverManager.getConnection( "jdbc:as400://192.168.xx.xx;transaction isolation=none;", user, pass) # 一旦削除 stmt = conn.createStatement stmt.executeUpdate("DELETE FROM LIBNAME.DBNAME WHERE TBTYPE = 'HOGE' AND TBCODE LIKE 'JD%'") # 追加用の配列＋Hash作成 arr = [] arr.push({key: "JD01", const: "ABC"}) arr.push({key: "JD02", const: "abc"}) arr.push({key: "JD03", const: "ａｂｃ"}) arr.push({key: "JD04", const: "①"}) arr.push({key: "JD05", const: "你好"}) arr.push({key: "JD06", const: "𩸽"}) arr.push({key: "JD07", const: "🍣🍺"}) # 追加処理 arr.each do |rcd| # https://java-code.jp/971 stmt = conn.prepareStatement("INSERT INTO LIBNAME.DBNAME (TBTYPE, TBCODE, TBNAME, TBNUM1, TBNUM2) VALUES ('HOGE', ?, ?, ?, ?)") # https://www.javadrive.jp/servlet/database/index10.html # idxを加算しつつ値をセットしていく(中間に?が挿入された場合対応) idx = 0 stmt.setString((idx += 1), rcd[:key]) stmt.setString((idx += 1), rcd[:const]) stmt.setInt((idx += 1), 1) stmt.setFloat((idx += 1), 2.34) result = stmt.executeUpdate() stmt.close # 念のため end conn.close
java_importはJRuby独自の関数。getConnection()、createStatement()、executeUpdate()、prepareStatement()、setString()、setInt()、setFloat()、closeはJavaの方の関数です。
その中の変数や関数はRuby独自のものなので、RubyおよびJava経験者ともに、多少の違和感を感じるかもしれません。
前章と異なり、ループしながらINSERTしています。
また、idxによるインクリメント処理もしています。こうすることで、中間に「?」が増えたときに、ソースを書き換えなくて済むからです。idxを使用しないとこうなります。
stmt.setString(1, rcd[:key]) stmt.setString(2, rcd[:const]) stmt.setInt(3, 1) stmt.setFloat(4, 2.34)
実行
実行は…
jruby test_ins2.rb
なお、プレースホルダでよく見かける「?」ではない「:hoge」形式については、例題は見つかりませんでした。
「PreparedStatement オブジェクトでの名前付きパラメーター・マーカーの使用」を参考に…
stmt = conn.prepareStatement("INSERT INTO LIBNAME.DBNAME (TBTYPE, TBCODE, TBNAME) VALUES ('HOGE', :code, :name)"); stmt.setJccStringAtName("code", rcd[:key]); stmt.setJccStringAtName("name", rcd[:const]);
として実行したところ、「[SQL0312] 変数CODEが定義されていないか使用可能でありません。」により実行ができませんでした。
AS400用のJDBCのgem等の何かが必要かもしれませんね。前出のサイトにこのような記述があります。
データ・サーバーのタイプやバージョンにかかわらず、名前付きパラメーターを使用するアプリケーションが正常に機能するようにするには、アプリケーションで名前付きパラメーター・マーカーを使用する前に、Connection または DataSource の enableNamedParameterMarkers プロパティーを DB2BaseDataSource.YES に設定します。 「PreparedStatement オブジェクトでの名前付きパラメーター・マーカーの使用」
どうやら、DB2方言のようですね。いつ使えなくなるかわかりませんので、やめた方が賢明だと思い、使用は避けることにします。
さて、前回同様、実行を確認したところ…
| # | TBCODE | TBNAME | 種別 | 備考 |
|---|---|---|---|---|
| 1 | JD01 | ABC | 半角英大文字 | |
| 2 | JD01 | abc | 半角英小文字 (5026ではサポートされない) |
|
| 3 | JD01 | ａｂｃ | 全角文字 | |
| 4 | JD01 | ① | 5026ではサポートされない文字 | |
| 5 | JD01 | 你好 | 同 | |
| 6 | JD01 | 𩸽 | 同、サロゲートペア | |
| 7 | JD01 | 同、絵文字 | 文字化け |
「𩸽」が表示されることに注目。プレースホルダ経由だと絵文字以外のプレースホルダが表示されるようです。
一見してうまく行ったように見えますが、他のサロゲートペア文字、例えば「𠮷」(つちよし)で試したところ、うまくいかなかったので、プレースホルダでサロゲートペアが解決できたわけではないようです。単にラッキーなだけでした。
文字種による挙動まとめ
ここまででわかったことですが、DBアクセスは可能でしたが、JDBCドライバーでは5026文字列チェックは行いません。
ですので、文字の種類によって以下の挙動となるようです。
| 文字種 | AS400更新 | AS400上での表示 | 再度SELECT | 文字化け |
|---|---|---|---|---|
| 5026対応文字 | ○ | ○ | ○ | |
| 5026非対応 | ○ | ✕ | ○ | |
| 同、サロゲートペア | ✕ | ✕ | ✕ | あり(文字列の破壊) |
| 同、絵文字 | ✕ | ✕ | ✕ | あり(文字列の破壊) |
表は5026ですが、5035でも同様かと思います。
キーポイントは、AS400でサポートしない文字、あるいは破壊される文字を、INSERT/UPDATE直前、つまりフロント側で除去できないか？だと思います。
AS400での表示は行わないのであれば、サロゲートペア文字のみをフロント側チェックし除去・変換を行えば良いと思いますが、Unicode文字のサロゲートペアや絵文字をチェックするのは、至難の業かもしれません。
ネット検索すると、サロゲートペア・絵文字のチェックや、文字数・バイト数計算で四苦八苦しているブログが見つかると思います。
ですので、5026/5035に変換(未サポート文字は除去、あるいは？等に変換)してから、INSERT/UPDATEが可能か？を考えたほうが賢明かと思います。
参考：ActiveRecord(非推奨)
さらに、素のSQLではなく、ActiveRecordで操作できないか？をトライしてみました。
結論から言うと、*ActiveRecordではDB2は非推奨のため、採用しないほうが賢明です。**
(翻訳)
ActiveRecord-JDBC-Adapter (AR-JDBC) は、 JRubyで使用できるRails のActiveRecordコンポーネントのメイン データベース アダプターです。ActiveRecord-JDBC-Adapter は、 MySQL、PostgreSQL、SQLite3およびMSSQL * (SQLServer)を完全またはほぼ完全にサポートし ます。より多くの貢献が得られない限り、より多くのアダプターをサポートすることはありません。別のアダプターを入手するために必要な作業量はそれほど大きくありませんが、アダプターが引き続き機能することを確認するために必要なテストの量は、現在持っているリソースでは実行できないことに注意してください.
- Oracleデータベース のユーザーには、https://github.com/rsim/oracle-enhanced を使用することをお勧めします
- MSSQLアダプタの gem パーツは別のリポジトリに存在します
ドキュメントを見る限り、DB2は含まれていないようですね。
一応、AS400用にアレンジした「activerecord-jdbcas400-adapter」というものも存在しますが、そのgemをインストールすると、そのgemに必要な他のgemも一緒にインストールされます。
通常はそのgemに合うバージョンの関連gemをインストールするように設計されていますが、activerecord-jdbcas400-adapterは常に最新の関連gemを持ってくるため、プログラムを実行すると(関数のパラメータ数が一致しない。変更でいない変数を変更しようとした。)といった内部エラーで異常終了します。
それを使用せずに、activerecord-jdbc-adapter単体をインストールし、実行すると、データベースに接続はできますが…
NOTE: ActiveRecord 4.2 with adapter: db2 is not (yet) fully supported by AR-JDBC, please consider helping us out.
といった、非推奨を伝える警告メッセージがが常に表示されます。
(AR-JDBCはactiverecord-jdbc-adapterの略らしい。)
また、ActiveRecordのテーブル操作、例えばUser.first(ユーザーテーブルの最初のレコードを取得)は異常終了します。
SQL文を直に実行する方法もあり、「?」ではない「:hoge」といったプレースホルダも利用できますが、先程の警告メッセージは消えません。
将来的にどうなるかわからないので、使用するのは避けたほうが賢明かもしれませんね。
ActiveRecordがダメならば、AS400ではRuby on RailsのメインDBという選択肢は厳しいかなとは思います。
参考：JRuby＋Sequelのほうが楽では？
ActiveRecord以外での簡略記法には、Sequelというものもあるようですね。
こちらの記法は試してはいませんが、AS400を経験されている方は、ActiveRecordやSequelではなく、SQL文をゴリゴリ書かれたほうが、逆に楽かもしれませんね。
まとめと課題
ここまでのまとめ。
- JのつかないRubyでは、ODBCやOLE経由でAS400と接続することも可能だが、ドライバ的にWindowsに依存する手法なので、汎用性に欠ける。
- そこで、ibm_dbというgemを使用した手法をIBMは押しているのだが、実行にはライセンスファイルを発行すること等、手順が煩わしく、また、Windows含め、実例がネットにほとんど転がっていないのが難点ではある。
上記より、AS400との接続は、JavaベースのJDBC＋JRubyが現状ではベストかと思いました。
私はWindowsで試しましたが、Javaベースなので、Linuxでも可能だと思います。他言語でも応用は利くと思います。
しかし、上記の問題があります。
- JDBCドライバーでは5026や5035の文字列チェックは行わず、変換も行なわず、エラーも返ってこない
- 結果、AS400では確認できない文字、あるいは、壊れてしまう文字がINSERT/UPDATEされる
- ActiveRecordはDB2はサポート外なので、Ruby on Railsでの開発は視野に入らない
ActiveRecordが無理であれば、フロントシステムとAPIでのやり取りが視野に入ってきますね。
フロントは(Jのつかない)Ruby(Ruby on Rails)＋PostgeSQL(フロント用DB)で、JRuby＋JDBC(AS400の基幹DB)＋Sinatra(API)とやり取りするといった形でしょうか。
あるいは、フロントはVue.jsといったJavascript系の選択肢もありそうです。
いずれにせよ、5026/5035変換はなんとかしたいところです。
解決方法については、下記リンクを参考にしてみてください。
それでは、また。
