はじめに

久しぶりに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なメソッドとして実装することがポイント。

SampleSP.java

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) ステートメント