UDF in Java

The following is an example UDF in Java included in DB2 (sqllib/samples/java/jdbc).

Java codes for UDFs(UDFSrv.java)

SQL script to create UDFs(UDFCreate.db2)

SQL script to drop UDFs(UDFDrop.db2)

To test the stored procedures, following steps as follows.

1. Setup CLASSPATH to include db2jcc.jar (include DB2 JDBC driver)

2. javac UDFServ.java Util.java (compile java code)

3. jar cf UDFServ.jar SpServer.class Util.class (package bytecode in Jar)

4. copy UDFServ.jar %DB2PATH%\function (Windows) (copy jar into DB2 function folder)

5. db2 -td@ -vf UDFDrop.db2 (drop UDFs. Change the script with right login name)

6. db2 call "SQLJ.REMOVE_JAR('MYJAR1')" (remove MYJAR package from DB2)

7. db2 call "sqlj.install_jar('file:%DB2PATH%\function\SpServer.jar','MYJAR1')" (install jar file into DB2)

8. db2 -td@ -vf UDFCreate.db2 (create UDFs)

9. db2 call ONE_RESULT_SET(80000) (test a UDF)

   1. Test UDF:

10. db2 "select scratchpadScUDF() FROM sysibm.sysdummy1"

11. db2 "SELECT scratchpadScUDF(), name, job FROM staff WHERE name LIKE 'S%'"