【JRuby】【AS400】JRubyでAS400のDBを扱う(JDBC編)

JRubyでは、Ruby関数とJava関数を組み合わせることで、AS400といったJDBCが必須のDBアクセスが容易に行なえます。

AS400、AS/400、iSeries、System iと表記の揺らぎがありますので、このコラムではAS400に統一します。

さて、今回はRubyとJDBCについてです。

当社では今までAS400でシステムを構築していたのですが、Web系システムを採用したいと思っています。
フロント部分はDB含め外出しを考えていますが、ただ、AS400のDBシステムの安定性は捨てがたく、基幹部分はAS400で残しつつ、なんとかできないものか?と考えています。
良い方法はありませんでしょうか?

確かに、AS400へのDB接続を検索すると、多くはAS400の内部でJavaやphpその他の言語を動かすといった情報がメインで、外部サーバーのWeb言語からAS400のDBをアクセスする…という情報は見かけません。

ですので、少ない情報を頼りに、外部サーバーのWebシステムから、AS400のDBへアクセスできないか、試してみることにしましょう。

目次

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でのアクセスのためには、サブシステムQUSRWRKQZDASOINITが起動されている必要があります。

WRKACTJOBにて動作を確認していただき、OKであれば、次へ進むことにしましょう。

参考:【できるIBM i 7.4解剖】第9回 「Db2 for iのODBC/JDBCサーバージョブ QZDASOINITのパフォーマンス調整」

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\libjt400.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.jarrequireは不要です。
また、実行後にクローズもしましょう。

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の挙動は?

以下はプログラム例。

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', 'abc')")
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独自の関数。getConnectioncreateStatementexecuteUpdatecloseはJavaの方の関数です。

実行は…

jruby test_ins.rb

どのようにINSERTされたか、再度SELECTして表示してみましょう。

#TBCODETBNAME種別備考
1JD01ABC半角英大文字
2JD01abc半角英小文字
(5026ではサポートされない)
3JD01abc全角文字
4JD015026ではサポートされない文字
5JD01你好
6JD01同、サロゲートペア文字化け
7JD01同、絵文字文字化け

サロゲートペアと絵文字が取得できません…
壊れてしまったようですね。

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メソッド」を参考に、プレースホルダを使用してみましょう。

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: "abc"}) 
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独自の関数。getConnectioncreateStatementexecuteUpdateprepareStatementsetStringsetIntsetFloatcloseはJavaの方の関数です。
その中の変数や関数はRuby独自のものなので、RubyおよびJava経験者ともに、多少の違和感を感じるかもしれません。

参考までに、Rubyにはインクリメント演算子++はありません。

前章と異なり、ループしながら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方言のようですね。いつ使えなくなるかわかりませんので、やめた方が賢明だと思い、使用は避けることにします。

さて、前回同様、実行を確認したところ…

#TBCODETBNAME種別備考
1JD01ABC半角英大文字
2JD01abc半角英小文字
(5026ではサポートされない)
3JD01abc全角文字
4JD015026ではサポートされない文字
5JD01你好
6JD01𩸽同、サロゲートペア
7JD01(同、絵文字文字化け

「𩸽」が表示されることに注目。プレースホルダ経由だと絵文字以外のプレースホルダが表示されるようです。

一見してうまく行ったように見えますが、他のサロゲートペア文字、例えば「𠮷」(つちよし)ではうまくいかなかったので、プレースホルダでサロゲートペアが解決できたわけではないようです。単にラッキーなだけでした。

文字種による挙動まとめ

ここまででわかったことですが、DBアクセスは可能でしたが、JDBCドライバーでは5026文字列チェックは行いません。
なので、文字の種類によって以下の挙動となるようです。

文字種AS400更新AS400上での表示再度SELECT文字化け
5026対応文字
5026非対応
同、サロゲートペアあり(文字列の破壊)
同、絵文字あり(文字列の破壊)

表は5026ですが、5035でも同様かと思います。

キーポイントは、AS400でサポートしない文字、あるいは破壊される文字を、INSERT/UPDATE直前、つまりフロント側で除去できないか?だと思います。

AS400での表示は行わないのであれば、サロゲートペア文字のみをフロント側チェックし除去・変換を行えば良いと思いますが、Unicode文字のサロゲートペアや絵文字をチェックするのは、至難の業かもしれません。

ネット検索すると、サロゲートペア・絵文字のチェックや、文字数・バイト数計算で四苦八苦しているブログが見つかると思います。

ですので、5026/5035に変換(未サポート文字は除去、あるいは?等に変換)してから、INSERT/UPDATEが可能か?を考えたほうが賢明かと思います。

入力したままの状態は、フロントエンド側のDBで保持し、基幹側(AS400側)にはコードや数量といった情報のみを渡す…といった割り切ったシステム構築が可能であれば、より構築が容易になるかと思います。

参考:ActiveRecord(非推奨)

さらに、素のSQLではなく、ActiveRecordで操作できないか?をトライしてみました。

結論から言うと、ActiveRecordではDB2は非推奨のため、採用しないほうが賢明です。

activerecord-jdbc-adapter

(翻訳)
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 パーツは別のリポジトリに存在します
activerecord-jdbc-adapter

ドキュメントを見る限り、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という選択肢は厳しいかなとは思います。

AS400内部ですべてこなすような開発なら、何らかの打開策があるかもしれませんが、こちらは外部のWebサーバーでの接続についてのコラムですので、扱わないことにします。

参考:JRuby+Sequelのほうが楽では?

ActiveRecord以外での簡略記法には、Sequelというものもあるようですね。

こちらの記法は試してはいませんが、AS400を経験されている方は、ActiveRecordやSequelではなく、SQL文をゴリゴリ書かれたほうが、逆に楽かもしれませんね。

Ruby on Rails抜きで、ActiveRecordにこだわるのは、SQLの文法にそこまで詳しくないエンジニア向けでもあり、そうでなければSQLで記述したほうが、AS400のエンジニア的には理解が早そうです。

まとめと課題

ここまでのまとめ。

  • JのつかないRubyでは、ODBCやOLE経由でAS400と接続することも可能だが、ドライバ的にWindowsに依存する手法なので、汎用性に欠ける。
  • そこで、ibm_dbというgemを使用した手法をIBMは押しているのだが、実行にはライセンスファイルを発行すること等、手順が煩わしく、また、Windows含め、実例がネットにほとんど転がっていないのが難点ではある。

上記より、AS400との接続は、JavaベースのJDBC+JRubyが現状ではベストかと思いました。

  • JRuby+JDBC接続でSQLを発行することにより、AS400のDBにアクセスが可能

私は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変換はなんとかしたいところです。
詳細は「あわせて読みたい」を参考にしてみてください。

それでは、また。

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

コメント

コメントする

CAPTCHA

目次