Preparing Java routines with SQLJ clauses to run from a JAR file

The recommended method of running Java routines with SQLJ clauses is to run them from a JAR file.

About this task

The steps in this task prepare an SQLJ routine for execution, create JAR files for the methods in the routine, define the JAR files to Db2, define the routine to Db2, and grant access on the routine to users.

Procedure

Run the sqlj command to translate the source code to produce generated Java source code and serialized profiles, and to compile the Java program to produce Java bytecodes.
Run the db2sqljcustomize command to produce serialized profiles that are customized for Db2 for z/OS® and Db2 packages.
Run the jar command to package the class files that contain the methods for your routine, and the profiles that you generated in step 2 into a JAR file. See "Creating JAR files for Java routines" for information on creating the JAR file.
Call the INSTALL_JAR stored procedure to define the JAR file to Db2.
If the installed JAR references classes in other installed JARs, call the SQLJ.ALTER_JAVA_PATH stored procedure to specify the class resolution path that the JVM searches to resolve those class references.
If another user defines the routine to Db2, execute the SQL GRANT USAGE ON JAR statement to grant the privilege to use the JAR file to that user.
Execute the SQL CREATE PROCEDURE or CREATE FUNCTION statement to define the routine to Db2. Specify the EXTERNAL NAME parameter with the name of the JAR that you defined to Db2 in step 4.
Execute the SQL GRANT statement to grant the EXECUTE privilege on the routine to the appropriate users.

Example

The following example demonstrates how to prepare a Java stored procedure that contains SQLJ clauses for execution from a JAR file.

On z/OS UNIX System Services, run the sqlj command to translate and compile the SQLJ source code.
Assume that the path for the stored procedure source program is /u/db2res3/s1/s1sal.sqlj. Change to directory /u/db2res3/s1, and issue this command:
```
sqlj s1sal.sqlj
```
After this process completes, the /u/db2res3/s1 directory contains these files:
```
s1sal.java
s1sal.class
s1sal_SJProfile0.ser
```
On z/OS UNIX System Services, run the db2sqljcustomize command to produce serialized profiles that are customized for Db2 for z/OS and to bind the Db2 packages for the stored procedure.
Change to the /u/db2res3 directory, and issue this command:
```
db2sqljcustomize -url jdbc:db2://mvs1:446/SJCEC1 \
  -user db2adm -password db2adm \
  -bindoptions "EXPLAIN YES" \
  -collection ADMCOLL \
  -rootpkgname S1SAL \
  s1sal_SJProfile0.ser
```
After this process completes, s1sal_SJProfile0.ser contains a customized serialized profile. The Db2 subsystem contains these packages:
```
S1SAL1
S1SAL2
S1SAL3
S1SAL4
```
On z/OS UNIX System Services, run the jar command to package the class files that you created in step 1 and the customized serialized profile that you created in step 2 into a JAR file.
Change to the /u/db2res3 directory, and issue this command:
```
jar -cvf s1sal.jar s1/*.class s1/*.ser
```
After this process completes, the /u/db2res3 directory contains this file:
```
s1sal.jar
```
Call the INSTALL_JAR stored procedure, which is on Db2 for z/OS, to define the JAR file to Db2.
You need to execute the CALL statement from a static SQL program or from an ODBC or JDBC program. The CALL statement looks similar to this:
```
CALL SQLJ.INSTALL_JAR('file:/u/db2res3/s1sal.jar','MYSCHEMA.S1SAL',0);
```
The exact form of the CALL statement depends on the language of the program that issues the CALL statement.

After this process completes, the Db2 catalog contains JAR file MYSCHEMA.S1SAL.
If the installed JAR references classes in other installed JARs, call the SQLJ.ALTER_JAVA_PATH stored procedure, which is on Db2 for z/OS, to specify the class resolution path that the JVM searches to resolve those class references. You need to execute the CALL statement from a static SQL program or from an ODBC or JDBC program.
If another user defines the routine to Db2, on Db2 for z/OS, execute the SQL GRANT USAGE ON JAR statement to grant the privilege to use the JAR file to that user.
Suppose that you want any user to be able to define the stored procedure to Db2. This means that all users need the USAGE privilege on JAR MYSCHEMA.S1SAL. To grant this privilege, execute this SQL statement:
```
GRANT USAGE ON JAR MYSCHEMA.S1SAL TO PUBLIC;
```

On Db2 for z/OS, execute the SQL CREATE PROCEDURE statement to define the stored procedure to Db2:

CREATE PROCEDURE SYSPROC.S1SAL
 (DECIMAL(10,2) INOUT)
  FENCED
  MODIFIES SQL DATA
  COLLID ADMCOLL
  LANGUAGE JAVA
  EXTERNAL NAME  'MYSCHEMA.S1SAL:s1.S1Sal.getSals'
  WLM ENVIRONMENT WLMIJAV
  DYNAMIC RESULT SETS 1
  PROGRAM TYPE SUB
  PARAMETER STYLE JAVA;

On Db2 for z/OS, execute the SQL GRANT EXECUTE statement to grant the privilege to run the routine to that user.
Suppose that you want any user to be able to run the routine. This means that all users need the EXECUTE privilege on SYSPROC.S1SAL. To grant this privilege, execute this SQL statement:
```
GRANT EXECUTE ON PROCEDURE SYSPROC.S1SAL TO PUBLIC;
```