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)