はじめに
はじめてJavaでDb2ユーザー定義関数(UDF)というものを作ってみたので、開発手順を備忘録として残しておこうと思います。手順はストアード・プロシージャーとほぼ同じで、Javaで記述するコードとCREATEステートメントが少々違うくらいです。
環境
本投稿内容は以下の環境で動作確認しています。
| ソフトウェア | バージョン | 備考 |
|---|---|---|
| Windows 11 Pro | 23H2 | |
| Db2 for Windows (x64) | 11.5.8 | Db2 Community Edition |
| Java | 1.8.0_331 | Db2同梱のJava |
Db2ユーザー定義関数(UDF)の開発手順
JavaによるDb2ユーザー定義関数(UDF)の開発手順は以下のとおりです。
- Javaでユーザー定義関数(UDF)を記述
- 記述したユーザー定義関数(UDF)をコンパイルしてjarにパッケージング
- Db2に付属のストアードプロシージャー(sqlj.install_jar)を使用してjarをインストール
- CREATE FUNCTIONでユーザー定義関数(UDF)を定義
- 実行ユーザーにユーザー定義関数(UDF)の実行権限をGRANT
上記手順の具体的な作業を以下に説明します。
1. Javaでユーザー定義関数(UDF)を記述
以下のようなJavaコードでユーザー定義関数(UDF)を実装。
サンプルとして文字列の先頭を大文字に、2文字目以降を小文字に変換するユーザー定義関数を作成しました。
package com.example.db2udf;
public class SampleUDF {
public static String doFunc(String s) {
if (s == null || s.length() == 0) {
return s;
}
return s.substring(0,1).toUpperCase() + s.toLowerCase().substring(1);
}
}
2. 記述したストアード・プロシージャーをコンパイルしてjarにパッケージング
前記Javaコードをjavacコマンドでコンパイルして、jarコマンドでjarにパッケージングします。自分はmavenを利用しましたが、お好みの方法で。
jarにパッケージングした方が管理は楽かと思いますが、jarを作成しない方法（※）も可能です。
※「補足 3) jarファイルを使用しない方法」を参照。
3. Db2に付属のストアードプロシージャー(sqlj.install_jar)を使用してjarをインストール
db2cmdコマンドでオープンしたCLP対応Db2ウィンドウから以下を実行。
db2udf-0.0.1-SNAPSHOT.jarは上記2で作成したjarファイル名です（適宜読み替えてください）。SampleUDFJARはDb2に登録するjar IDで、この後のCREATE FUNCTIONで指定します。
db2 CALL sqlj.install_jar('file:db2udf-0.0.1-SNAPSHOT.jar', 'SampleUDFJAR')
4. CREATE FUNCTIONでユーザー定義関数(UDF)を定義
CREATE FUNCTIONでユーザー定義関数(UDF)を定義します。Javaのメソッドにマッピングするため EXTERNAL NAMEで上記3にてインストールしたjarのID、上記1にて記述したJavaクラスのFQCNとメソッド名を指定します。
CREATE FUNCTIONの詳細はDb2オンライン・マニュアル参照（参照資料[3])。
CREATE FUNCTION SAMPLE_UDF (IN P1 VARCHAR(15))
RETURNS VARCHAR(15)
NOT DETERMINISTIC
LANGUAGE JAVA
EXTERNAL NAME 'SampleUDFJAR:com.example.db2udf.SampleUDF.doFunc'
FENCED
THREADSAFE
PARAMETER STYLE JAVA
NO SQL
;
5. 実行ユーザーにユーザー定義関数(UDF)の実行権限をGRANT
db2cmdコマンドでオープンしたCLP対応Db2ウィンドウから以下を実行。DB2USERはユーザー定義関数(UDF)を実行するユーザーIDで読み替えてください。スキーマTSUNOもユーザー定義関数のスキーマで読み替えてください。
db2 GRANT EXECUTE ON PROCEDURE TSUNO.SAMPLE_SP TO DB2USER
動作確認
Db2に付属のSAMPLEデータベースに接続して以下のselect文を実行してみます。
db2 select empno, firstnme, TSUNO.SAMPLE_UDF(firstnme) from employee
以下の結果が得られます。ユーザー定義関数によってFIRSTNMEの1文字目が大文字に変換されていることが確認できます。
EMPNO FIRSTNME 3
------ ------------ ---------------
000010 CHRISTINE Christine
000020 MICHAEL Michael
000030 SALLY Sally
000050 JOHN John
000060 IRVING Irving
000070 EVA Eva
000090 EILEEN Eileen
000100 THEODORE Theodore
000110 VINCENZO Vincenzo
000120 SEAN Sean
000130 DELORES Delores
000140 HEATHER Heather
000150 BRUCE Bruce
000160 ELIZABETH Elizabeth
000170 MASATOSHI Masatoshi
000180 MARILYN Marilyn
000190 JAMES James
000200 DAVID David
000210 WILLIAM William
000220 JENNIFER Jennifer
000230 JAMES James
000240 SALVATORE Salvatore
000250 DANIEL Daniel
000260 SYBIL Sybil
000270 MARIA Maria
000280 ETHEL Ethel
000290 JOHN John
000300 PHILIP Philip
000310 MAUDE Maude
000320 RAMLAL Ramlal
000330 WING Wing
000340 JASON Jason
200010 DIAN Dian
200120 GREG Greg
200140 KIM Kim
200170 KIYOSHI Kiyoshi
200220 REBA Reba
200240 ROBERT Robert
200280 EILEEN Eileen
200310 MICHELLE Michelle
200330 HELENA Helena
200340 ROY Roy
42 レコードが選択されました。
補足
1) jarファイルの置換
Javaコードを修正した場合は以下のコマンドでjarファイルを置換します（参照資料[1]）。
db2 CALL sqlj.replace_jar('file:db2sp-0.0.1-SNAPSHOT.jar', 'SampleSPJAR')
2) jarファイルの削除
jarを削除する場合は以下のコマンドを実行します（参照資料[1]）。
注）事前に当該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 FUNCTION (外部スカラー) ステートメント