Deploying Java stored procedures

Before you begin

Before you can deploy an external Java or SQLJ stored procedure on Db2® for z/OS®, you need to set up a WLM environment and set Java environment variables. For instructions, see Setting up the environment for Java routines.

You also need SYSADM authority or EXECUTE privilege on the following procedures:

If you do need to refresh the WLM environment, you’ll also need to set up the WLM_REFRESH stored procedure. See WLM_REFRESH stored procedure for details.

Prerequisites

  • Create a .spsql or .javasql file to contain the CREATE PROCEDURE statement for your Java or SQLJ stored procedure.
  • The file must contain a single CREATE PROCEDURE statement. If multiple are present, only the first is deployed.

To deploy a Java stored procedure, do these steps:

  1. Open the .spsql or .javasql containing the CREATE PROCEDURE statement for the Java Stored Procedure.
  2. Right-click anywhere in the SQL editor and select Db2 for z/OS > Deploy Routine or click on the Deploy Routine option. The Deploy Routine wizard opens.
  3. Specify deployment and routine options on the wizard pages, and then click Deploy. For descriptions of the options you can specify in this wizard, see the list at the end of this procedure. If one or more of the JAR dependencies defined on the Java Options page of the wizard already exist on the target Db2 for z/OS server, you are prompted to replace the existing JAR IDs.
  4. The results of the deploy operation are shown in the Execution Status dialog, where you can see any warnings or errors and link to more information about SQL codes and status.
Deployment options
Option Description
Db2 Connection Select a connection to a Db2 location to deploy the stored procedure to. The default is the active connection for the .spsql file, if one is defined.
Target schema Set the target schema for unqualified stored procedures. The wizard suggests an initial value that is based on the associated Db2 connection. If you leave this field empty, the JDBC property currentSchema is used. If the JDBC currentSchema property is not set, the JDBC connection username is used. If the stored procedure content declares the target schema, it appears in this field and cannot be changed.
Build owner

Specify the owner of the stored procedure. The wizard suggests an initial value that is based on the associated Db2 connection. If you leave this field empty, the JDBC property currentSQLID is used. If currentSQLID is not set, the JDBC connection username is used.
Default path Specify one or more schemas for resolving an unqualified data type, function, or procedure that is referenced by the procedure being deployed. Separate multiple schemas with commas, for example, "ADMF002","ADMF003","ADMF004".
Duplicate handling Choose one of these options to specify how to handle a stored procedure that already exists:
Drop duplicates Calls DROP PROCEDURE before running the CREATE PROCEDURE DDL. By default, Db2 activates the first version of a stored procedure.
Treat duplicate deployments as errors Returns an error. If other versions of the procedure do not already exist, the procedure is created.

For more information about JDBC properties, see Common IBM Data Server Driver for JDBC and SQLJ properties for Db2 and Db2 for z/OS servers.

Routine Option
Option description
Enable Debugging Select this checkbox to specify whether this procedure is available to be debugged.
Limitation: This checkbox is disabled for Java stored procedures. Debugging Java stored procedures is not supported.
WLM Environment Specify which Workload Manager environment to use for debugging the procedure. If the source file declares a WLM environment, it is used as the default and this field is disabled. If you enable debugging but leave this field empty, the default WLM environment is used.
ASU Time Limit Specify the number of CPU seconds permitted for each SQL statement. The default value of 0 means no time limit. If the source file declares an ASU time limit, it is used as the default and this field is disabled.
Tip: The WLM environment and ASU time limit fields can be populated with values specified in the CREATE PROCEDURE statement.
Additional Routine Options Specify any additional options that you want to include. Separate each option with a semicolon. Example: QUALIFIER ADMF002; ISOLATION LEVEL RS.
Java options

Each time you run this wizard, the options specified on this page are saved. The page might contain options from the previous Deploy Routine operation.

Option Description
Java File Path Specify the Java class file. It must have a public static void method, which works as an entry point for the stored procedure. The class name, declared package, and method name should match the external name defined in the CREATE PROCEDURE statement. If the Java class declares a package path, its location must match the package path to compile properly.
Java Archive Dependencies Use this table to add, edit, or remove any compilation and runtime dependencies that are required by the Java class. Dependent JARs are defined to Db2 if you select Upload; otherwise, dependent JARs are used only for compilation. Dependent JARs must be defined in the Java path on the server unless they have already been defined to Db2 by other Java stored procedures.
Requirement: If a dependent JAR file has its own dependent JAR files, save this JAR file and all its dependent JAR files on the server and add them to your JAVA path on the server. When you deploy this stored procedure, clear the Upload option for this JAR file and its dependent JAR files.
Compatible JRE Version Specify a JRE that is at the same level or lower than the JRE version that is installed on the Db2 for z/OS server. If the specified JRE version is higher than the version on the server, the stored procedure does not run.
Upload Java Source Select this checkbox to include the associated Java source file when you deploy the stored procedure. If you do not select this option, you can run the stored procedure, but cannot view the source in the Catalog folder of the Db2 for z/OS connection in the Remote Systems view.
Refresh Java WLM Environment Select this checkbox to ensure that the most current version of the stored procedure is run.