Developing Java stored procedures

To create a Java or SQLJ stored procedure, you need the following files:

The Java source file that defines the behavior of the stored procedure. It should have a .java or .sqlj file extension.
The DDL file that creates the Java stored procedure using a CREATE PROCEDURE statement. You can create and deploy by using Db2 Developer Extension. This file should have a .sqsql or .javaspsql extension.

Working with Eclipse Java Tools

Create an Eclipse Java project to host your development source. Eclipse provides the Java perspective for the development of Java projects.
1. To open this perspective, click Window > Perspective > Open Perspective, and then select Java from the list of available perspectives.
2. In the Package Explorer, right-click and select New > Java Project.
3. You can create a separate folder to host any JAR dependencies your procedure has.
4. To configure the Java build path to include any JAR libraries that your Java stored procedures depend on right-click the project and select Build Path > Configure Build Path.
5. You can create a separate folder to host the .sql, .spsql, or .javaspsql files that contain the CREATE PROCEDURE statements used to deploy the Java stored procedure.
6. The following screen capture shows a sample Java project that contains two stored procedures:
7. If you choose to reuse any IBM Data Studio Data Development projects that you might have in a Developer for z/OS® 15.0.x workspace, you can import them into the Java Explorer view. Data Development projects are Java projects.
8. If you want to have the Db2® for z/OS Development tooling in the same perspective as your Java projects, you can add the z/OS Projects view and the Remote Systems view to the Java perspective:
  1. Open the Java perspective.
  2. From the menu bar, select Window > Show View > Other.
  3. In the Show View window, expand Remote Systems, and then select Remote Systems and click Open.
  4. From the menu bar, select Window > Show View > Other.
  5. In the Show View window, expand z/Os Project Views, and then select z/Os Projects and click Open.
For more information about developing Java projects, see the Java development user guide.

Working with Java files

If the Java class declares a package path, its location must match the package path to compile correctly.
A Java stored procedure must have the public static void method, which functions as an entry point for the stored procedure.

Working with .javasql files:

The EXTERNAL NAME clause must contain a valid external-java-routine-name that is specified in the following format:

jar-name:package-id...class-id.method-id(method-signature)

The package-id must match the package declare statement in the Java source. The class-id must match the Java class name. The method-id must match the Java method name. The method-signature is optional.

The class name, declared package, and method name should match the external name defined in the CREATE PROCEDURE statement.
Support for Java stored procedures in the SQL editor includes two templates: createProcedureJavaInOut and createProcedureJavaResultSet. To see the SQL templates, open the Preferences window, and navigate to z/OS Solutions > Db2 for z/OS > Db2 Templates.

For more information, see CREATE PROCEDURE statement (external procedure) in the Db2 for z/OS documentation.

Example of Java stored procedure


CREATE PROCEDURE USERIBM.JDBC_JSP()
    LANGUAGE JAVA
    EXTERNAL NAME 'USERIBM.JDBC_JSP:JdbcJavaSP.getEmp(java.sql.ResultSet[])'
    PARAMETER STYLE JAVA
    WLM ENVIRONMENT WLMJAVA
    READS SQL DATA
    DYNAMIC RESULT SETS 1