はじめに
久しぶりにJavaでDb2ストアード・プロシージャーを作成してみたのですが、いろいろ忘れていたことを思い出すのに時間がかっかったので備忘録として残しておこうと思います。
環境
本投稿内容は以下の環境で動作確認しています。
| ソフトウェア | バージョン | 備考 |
|---|---|---|
| Windows 11 Pro | 23H2 | |
| Db2 for Windows (x64) | 11.5.8 | Db2 Community Edition |
| Java | 1.8.0_331 | Db2同梱のJava |
Db2ストアード・プロシージャーの開発手順
JavaによるDb2ストアード・プロシージャーの開発手順は以下のとおりです。
- Javaでストアード・プロシージャーを記述
- 記述したストアード・プロシージャーをコンパイルしてjarにパッケージング
- Db2に付属のストアードプロシージャー(sqlj.install_jar)を使用してjarをインストール
- CREATE PROCEDUREでストアード・プロシージャーを定義
- 実行ユーザーにストアード・プロシージャーの実行権限をGRANT
上記手順の具体的な作業を以下に説明します。
1. Javaでストアード・プロシージャーを記述
以下のようなJavaコードでストアード・プロシージャーを実装（Db2に付属のSAMPLEデータベースのEMPLOYEEテーブルから指定した部門コード(WORKDEPT)のレコードを取得）。staticなメソッドとして実装することがポイント。
package com.example.db2sp;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.ibm.example.util.ProcUtil;//Connection取得で使用するユーティリティー
public class SampleSP {
public static void doProc(String workdept, ResultSet[] rs) throws SQLException, ClassNotFoundException {
Connection conn = ProcUtil.getConnection();
String sql = "select * from TSUNO.EMPLOYEE where WORKDEPT=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, workdept);
rs[0] = ps.executeQuery();
}
}
2. 記述したストアード・プロシージャーをコンパイルしてjarにパッケージング
前記Javaコードをjavacコマンドでコンパイルして、jarコマンドでjarにパッケージングします。自分はmavenを利用しましたが、お好みの方法で。
jarにパッケージングした方が管理は楽かと思いますが、jarを作成しない方法（※）も可能です。
※「補足 3) jarファイルを使用しない方法」を参照。
3. Db2に付属のストアードプロシージャー(sqlj.install_jar)を使用してjarをインストール
db2cmdコマンドでオープンしたCLP対応Db2ウィンドウから以下を実行。
db2sp-0.0.1-SNAPSHOT.jarは上記2で作成したjarファイル名です（適宜読み替えてください）。SampleSPJARはDb2に登録するjar IDで、この後のCREATE PROCEDUREで指定します。
db2 CALL sqlj.install_jar('file:db2sp-0.0.1-SNAPSHOT.jar', 'SampleSPJAR');
4. CREATE PROCEDUREでストアード・プロシージャーを定義
CREATE PROCEDUREでストアード・プロシージャーを定義します。Javaのメソッドにマッピングするため EXTERNAL NAMEで上記3にてインストールしたjarのID、上記1にて記述したJavaクラスのFQCNとメソッド名を指定します。
CREATE PROCEDUREの詳細はDb2オンライン・マニュアル参照（参照資料[3])。
CREATE PROCEDURE SAMPLE_SP (IN WORKDEPT CHAR(3))
DYNAMIC RESULT SETS 1
NOT DETERMINISTIC
LANGUAGE JAVA
EXTERNAL NAME 'SampleSPJAR:com.example.SampleSP.doProc'
FENCED
THREADSAFE
PARAMETER STYLE JAVA
;
5. 実行ユーザーにストアード・プロシージャーの実行権限をGRANT
db2cmdコマンドでオープンしたCLP対応Db2ウィンドウから以下を実行。DB2USERはストアード・プロシージャーを実行するユーザーIDで読み替えてください。
db2 GRANT EXECUTE ON PROCEDURE TSUNO.SAMPLE_SP TO DB2USER
動作確認
Db2に付属のSAMPLEデータベースに接続して以下のcall文を実行してみます。
db2 call TSUNO.SAMPLE_SP('A00')
以下の結果が得られます。WORKDEPTがパラメーターで指定した'A00'とマッチする行が選択されていることが確認できます。
結果セット 1
--------------
EMPNO FIRSTNME MIDINIT LASTNAME WORKDEPT PHONENO HIREDATE JOB EDLEVEL SEX BIRTHDATE SALARY BONUS COMM
------ ------------ ------- --------------- -------- ------- ---------- -------- ------- --- ---------- ----------- ----------- -----------
000010 CHRISTINE I HAAS A00 3978 1995-01-01 PRES 18 F 1963-08-24 152750.00 1000.00 4220.00
000110 VINCENZO G LUCCHESSI A00 3490 1988-05-16 SALESREP 19 M 1959-11-05 66500.00 900.00 3720.00
000120 SEAN O'CONNELL A00 2167 1993-12-05 CLERK 14 M 1972-10-18 49250.00 600.00 2340.00
200010 DIAN J HEMMINGER A00 3978 1995-01-01 SALESREP 18 F 1973-08-14 46500.00 1000.00 4220.00
200120 GREG ORLANDO A00 2167 2002-05-05 CLERK 14 M 1972-10-18 39250.00 600.00 2340.00
5 レコードが選択されました。
リターン状況 = 0
補足
1) jarファイルの置換
Javaコードを修正した場合は以下のコマンドでjarファイルを置換します（参照資料[1]）。
db2 CALL sqlj.replace_jar('file:db2sp-0.0.1-SNAPSHOT.jar', 'SampleSPJAR')
2) jarファイルの削除
jarを削除する場合は以下のコマンドを実行します（参照資料[1]）。
注）当該jarを参照するストアード・プロシージャーが存在する場合は削除できません（削除する場合は当該jarを参照するストアード・プロシージャーを事前に削除する必要あり）。
db2 CALL sqlj.remove_jar('SampleSPJAR')
3) jarファイルを使用しない方法
jarファイルを使用しない方法も可能です。jarファイルを使用しない場合はSQLLIB/FUNCTIONディレクトリーにコンパイルした.classファイルを配置します（参照資料[2]）。CREATE PROCEDUREのEXTERNAL NAMEにはjar IDを指定しません。
参照資料
[1]データベース・サーバーでの JAR ファイル管理
[2]JDBC ルーチンの構築
[3]CREATE PROCEDURE (external) ステートメント