Creating external Java and SQLJ stored procedures

This article covers the basics of using Db2 Developer Extension to create a simple external Java or SQLJ stored procedure.

Before you begin

Before you can run 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.

Creating a basic external Java or SQLJ stored procedure with code snippets

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

  • The Java source file that you want to create a stored procedure for, which you provide. This file must end with an extension of .java or .sqlj.
  • The DDL to create the java stored procedure, which you can create and deploy by using Db2 Developer Extension. This file must end with an extension of .sqsql or .javaspsql.

To create the stored procedure DDL:

  1. Create a new folder to contain your stored procedure files.

  2. Within that new folder, create a new DDL file (.spsql or .javaspsql file) and the package structure for your java package.

    For example, if your java package is package com.Administrator.Administrator, the folder structure would look like this:

    <DDL_file>.javaspsql | .spsql
/com
  /Administrator
    /Administrator
      /<source_file>.java | .sqlj

  3. Open your new .spsql or .javaspsql file and start typing CREATE PROCEDURE. You’ll only need to type a few letters before you see CREATE PROCEDURE in the list of available code snippets.

  4. Select the CREATE PROCEDURE statement (Java - IN/OUT parameters) snippet to populate your file with the basic CREATE PROCEDURE structure:

CREATE PROCEDURE code snippet

The snippet includes some of the more commonly used parameters and the required EXTERNAL NAME clause. 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.

To see all of the options that you can define in an external Java stored procedure, open the link that is included at the top of the snippet.

What’s next