はじめに

はじめて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文字目以降を小文字に変換するユーザー定義関数を作成しました。

SampleUDF.java

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 (外部スカラー) ステートメント