Java Stored Procedure

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

Java codes for stored procedures(SpServer.java)

SQL script to create stored procedures(SpCreate.db2)

SQL script to drop stored procedures(SpDrop.db2)

To test the stored procedures, following steps as follows.

Example paths under windows:

DB2PATH: C:\Program Files\IBM\SQLLIB\FUNCTION

DB2 JDBC path: C:\Program Files\IBM\SQLLIB\java\db2cc.jar

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

1. Compile Java code:

javac SpServer.java

or (if CLASSPATH not set):

javac -cp "C:\Program Files\IBM\SQLLIB\java\db2cc.jar" SPServer.java

2. Package bytecode in Jar:

jar cf SpServer.jar SpServer.class

3. copy jar into DB2 function folder:

copy SpServer.jar %DB2PATH%\function

copy SpServer.jar "C:\Program Files\IBM\SQLLIB\function"

4. Clean up stored procedures if needed:

Drop stored procedures if already existing. Change the script with right login name (db2 connect to sample user XYZ@)

db2 connect to sample

db2 -td@ -vf SpDrop.db2

Remove MYJAR package from DB2:

db2 call "SQLJ.REMOVE_JAR('MYJAR')"

5. Install jar file into DB2:

db2 call "sqlj.install_jar('file:%DB2PATH%\function\SpServer.jar','MYJAR')" 

or 

db2 call "sqlj.install_jar('file:C:\Program Files\IBM\SQLLIB\function\SpServer.jar','MYJAR')"

6. Create store procedures:

db2 -td@ -vf SpCreate.db2

7. Test stored procedure:

db2 call ONE_RESULT_SET(80000)