Deploying and running external Java stored procedures

This article shows you how to use Db2 Developer Extension to deploy an external Java stored procedure with various deployment options: altering previous deployments, setting target schema, and others. It also walks you through the process of running an external Java stored procedure.

Tip: If you’re new to stored procedures, make sure you’re familiar with the information in Creating external Java stored procedures first.

One big advantage of using Db2 Developer Extension to deploy a stored procedure, as opposed to manually executing SQL, is that you can save the various deployment options separately from the SQL itself, which means that you can push your code into a source code manager, such as GitHub, without having to remove the deployment debug options first.

Another advantage is that you don’t need to specify --#SET TERMINATOR in the header of .spsql or .javaspsql files. Db2 Developer Extension uses the number sign character (#) as the default terminator character, but you can use any terminator character you want by setting a SQL routine option (explained later in this article).

Note: Currently, Db2 Developer Extension deploy and run options support only one stored procedure per .spsql or .javaspsql file. When stored procedure options are specified, only the first stored procedure in the file will be executed; additional stored procedures and SQL statements will be ignored.

We’ll use the following example stored procedure throughout this article to demonstrate how to deploy and run an external Java stored procedure. This stored procedure will read the total salary, including bonuses, from the table SYSADM.EMP from a passed in-parameter DEPTNUMBER. You can paste it into a file so that you try things out for yourself.

TotalSalaryJavaSP.spsql

CREATE PROCEDURE MYAPPS.TotalSalaryJavaSP(IN DEPTNUMBER CHAR(3),
  OUT DEPTSALARY DECIMAL(15,2),
  OUT DEPTBONUSCNT INT)
    LANGUAGE JAVA
    EXTERNAL NAME 'MYAPPS.TotalSalaryJavaSP:TotalSalary.getDeptSalary'
    PARAMETER STYLE JAVA
    WLM ENVIRONMENT WLMJAVA
    READS SQL DATA

And here is the example Java code that includes the SQL SELECT statement:

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * A sample JDBC Java stored procedure that returns a result set.
 */
public class TotalSalary {
    public static void getDeptSalary(String DEPTNUMBER, BigDecimal[] DEPTSALARY, int[] DEPTBONUSCNT) throws SQLException {
        Connection conn = null;
        Statement stmt = null;
        String query = "SELECT SALARY, BONUS FROM SYSADM.EMP WHERE WORKDEPT = '" + DEPTNUMBER + "'";
        BigDecimal totalSalary = new BigDecimal(0);
        int bonusCount = 0;
        try {
            conn = DriverManager.getConnection("jdbc:default:connection");
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            while (rs.next()) {
                BigDecimal bonus = rs.getBigDecimal(2);
                totalSalary = totalSalary.add(rs.getBigDecimal(1)).add(bonus);
                if (bonus.doubleValue() > 0)
                    bonusCount++;
            }
            DEPTSALARY[0] = totalSalary;
            DEPTBONUSCNT[0] = bonusCount;
        } catch (SQLException e) {
            throw e;
        } finally {
            if (null != stmt)
                stmt.close();
            if (null != conn)
                conn.close();
        }
    }
}

The file extension of .spsql identifies it as a stored procedure. When you open a .spsql or .javaspsql file in Db2 Developer Extension, you get some additional actions in the toolbar in the upper right corner of the view:

Deploy and Run action icons

From left to right, these actions are Deploy and Run. We’ll cover each one in the following sections.

Note: Db2 Developer Extension does not currently support debugging external Java stored procedures.

Deploying a stored procedure

Click the Deploy action to open the Deployment view. This is where you can set and customize deployment options and save the options for the .spsql or .javaspsql file. These options are used only during deployment and do not impact the DDL source code.

The Deployment view consists of three sections: Deployment options, Routine options, and Java options.

Deployment options

Deployment options

  • Use the Db2 connection option to select a connection from the list of defined connections. See Creating a database connection for more information.

  • Use the Target schema option for unqualified stored procedures. This field is enabled only for unqualified stored procedures. If the stored procedure is qualified (like the one in our example CREATE PROCEDURE statement, which is qualified to MYAPPS), the field is disabled. If you leave this field empty, the JDBC property currentSchema will be used. If currentSchema is not set, the JDBC connection username will be used.

  • Use the Build owner option to specify the owner of the stored procedure. If this field is left empty, the JDBC property currentSQLID will be used. If currentSQLID is not set, the JDBC connection username will be used.

  • Use the Default path option for resolving an unqualified data type, function, or procedure referenced by the procedure that’s being deployed. You can specify multiple schemas, each one separated by a comma. For example:

    "ADMF002","ADMF003","ADMF004"

  • Use the Duplicate handling option to specify the behavior of the deployment if the procedure already exists:

    • Drop duplicates calls DROP PROCEDURE before running the CREATE PROCEDURE DDL if the procedure already exists and creates the procedure.

    • Treat duplicate deployments as errors returns an error if the procedure already exists. If other versions of the procedure do not already exist, the procedure is created.

Note: Not all options mentioned above are displayed due to screen size limit.

If you’re new to SQL and want more information about embedding SQL in host languages, there’s a lot of information about this topic in the Db2 for z/OS documentation.

Routine options

Routine options

  • The Enable debugging option is not currently available for external Java stored procedures.

  • Use the WLM environment option to specify which Workload Manager environment will be used to debug the procedure. If you enable debugging but leave this field empty, the default WLM environment will used.

  • Use the Additional routine options field to specify any additional options that you want to include. Separate each option with a semicolon. For example:

   QUALIFIER ADMF002; ISOLATION LEVEL RS

Java options

Routine options

  • Use the Java file path option to 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 teh external name defined in the CREATE PROCEDURE statement. If the Java class declares a package path, its location must match the package path to be able to compile properly.

  • Use the Java dependencies option to specify 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’ve been defined already 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, uncheck the Upload option for this JAR file and its dependent JAR files.

  • Use the Compatible JRE version option to specify a JRE that is at the same level or lower than the JRE version that is on Db2 for z/OS database. If the specified JRE version is higher than the version on the database, the stored procedure will not run.

  • Select Upload source to the database field to include the associated Java source file when you deploy the stored procedure. If this option is not selected, you can run the stored procedure, but you will not be able to view the source for the stored procedure.

  • Click Refresh Java WLM environment to ensure that the most current version of the stored procedure is run.

Running a stored procedure

Click the Run action to start execution process. If your stored procedure contains input variables, you’ll be prompted to specify values for them before the stored procedure executes, as shown in the following figure. Note that built-in data types for each input variable are detected automatically and are set to the correct data type.

If you have an input type that’s an array, you’d specify the values like this:

numeric:   [1; 2; 3]
character: [hello; world]

Input parameters for running

After your stored procedure is executed, the following output is displayed in these tabs:

  • The Status tab contains overall execution status. If an error occurred, the status will contain SQL code, SQL state, and Message text.

Status tab

  • The Parameters tab contains variable information for passed-in parameters.

Parameters tab

  • The SQL Results view contains data if a result set is expected (in our example, a result set isn’t expected so the Result tab isn’t shown).