IBM Books

Application Development Guide


Stored Procedures and UDFs

You can create and use stored procedures and UDFs in Java just like you can for other programming languages. There are some programming considerations (as discussed in Function Definitions) that you need to know when you write your Java code. You also need to register your stored procedure and UDFs. For information on how to register your stored procedure, see Stored Procedures. For information on how to register your UDF, refer to the CREATE FUNCTION statement in the SQL Reference.

To run your UDFs and stored procedures on the server, DB2 calls the Java interpreter. DB2 does not include a Java interpreter; your database administrator must install and configure the appropriate Java Development Kit (JDK) on your DB2 server before starting up the database.

The runtime libraries for the Java interpreter must be available in the system search paths (PATH or LIBPATH or LD_LIBRARY_PATH, and CLASSPATH). For more information on setting up the Java environment, refer to the Application Building Guide.

DB2 loads or starts the Java interpreter on the first call to a Java UDF or stored procedure. For NOT FENCED UDFs and stored procedures, DB2 loads one Java interpreter per database instance, and runs it inside the database engine's address space for best performance. For FENCED UDFs, DB2 uses a distinct interpreter inside the db2udf process; similarly, FENCED stored procedures use a distinct interpreter inside the db2dari process. In all cases, the Java interpreter stays loaded until the embedding process ends.
Note:If you are running a database server with local clients node type, you must set the maxdari database manager configuration parameter to a non-zero value before you invoke a Java stored procedure.

You can study the Java stored procedure samples that are provided in the sqllib/samples/java directory. For a list of the sample programs included with DB2, see Appendix B, Sample Programs.

Remember that all Java class files that you use to implement a stored procedure or UDF must reside in either a JAR file you have installed in the database, or in the correct stored procedure or UDF path for your operating system as discussed in Where to Put Java Classes.
Note:On a mixed code page database server, Java user-defined functions and stored procedures cannot use CLOB type arguments, because random access on character boundaries on large mixed code page strings has not yet been implemented. Full support for all LOB types is intended for SBCS databases. For mixed databases, support is intended for the BLOB and the DBCLOB types. As a workaround, applications running on a mixed database system should convert CLOB arguments to DBCLOB, LONG VARGRAPHIC, or LONG VARCHAR types. For UDFs, this can be done with the CAST operator.

Where to Put Java Classes

You can choose to use individual Java class files for your stored procedures and UDFs, or collect the class files into JAR files and install the JAR file in the database. If you decide to use JAR files, refer to Java Stored Procedures and UDFs for further instructions.

To enable DB2 to find and use your stored procedures and UDFs, you must store the corresponding class files in the function directory, which is a directory defined for your operating system as follows:

Unix operating systems
sqllib/function

OS/2 or Windows 32-bit operating systems
instance_name\function, where instance_name represents the value of the DB2INSTPROF instance-specific registry setting. If DB2 does not find the stored procedure in instance_name\function, DB2 searches the directories defined by the PATH and LIBPATH environment variables.

For example, the function directory for a Windows NT server with DB2 installed in the C:\sqllib directory, and with no specified DB2INSTPROF registry setting, is:

     C:\sqllib\function

If you choose to use individual class files, you must store the class files in the appropriate directory for your operating system. If you declare a class to be part of a Java package, create the corresponding subdirectories in the function directory and place the files in the corresponding subdirectory. For example, if you create a class ibm.tests.test1 for a Linux system, store the corresponding Java bytecode file (named test1.class) in sqllib/function/ibm/tests.

The Java interpreter that DB2 invokes uses the CLASSPATH environment variable to locate Java files. DB2 adds the function directory and sqllib/java/db2java.zip to the front of your CLASSPATH setting.

To set your environment so that the Java interpreter can find where you have stored the Java class files, you may need to set the jdk11_path configuration parameter, or else use the default value. Also, you may need to set the java_heap_sz configuration parameter to increase the heap size for your application. For more information on configuration parameters, refer to the Administration Guide, Design and Implementation.

Debugging Stored Procedures

DB2 provides the capability to interactively debug a stored procedure written in JDBC when the stored procedure executes on an AIX or Windows NT server. Debugging support requires JDK level 1.1.6 or higher. The easiest way to invoke debugging is through the DB2 Stored Procedure Builder. See the online help for the Stored Procedure Builder for more information about how to do this.

This section includes the following topics:

Preparing to Debug

  1. Compile the stored procedure in debug mode according to your Java development environment documentation.

  2. Prepare the server.

  3. Set the client environment variables.

  4. Create the debug table.

    If you do not use the Stored Procedure Builder to invoke the debugger, create the debug table with the following command:

         db2 -tf sqllib/misc/db2debug.ddl
    

    Note:On DB2 Enterprise - Extended Edition systems, the default nodegroup is IBMDEFAULTGROUP for the USERSPACE1 table space, and consists of all nodes defined for the system. To improve the performance of debugging stored procedures in a DB2 Enterprise - Extended Edition configuration, you should have a single coordinator node where debugging will occur and define a nodegroup that only contains that node.

  5. Start the debugger daemon on the client.

    From the stored procedure client, start the debugger daemon with the following command:

         db2dbugd -qport=portno
    

    where portno is an unused TCP/IP port number. If you do not supply a value, the debugger uses 8000 as the default port number. On Windows 32-bit operating systems, you can also click the debugger daemon shortcut located in the DB2 folder to start the debugger with the default port number.

Populating the Debug Table

The debug table contains information about the stored procedures you debug and the client/server environment that you debug in. Only DBAs or users with INSERT, UPDATE, or DELETE privilege can manipulate values directly in the base table DB2DBG.ROUTINE_DEBUG. However, unless the DBA has added further restrictions, anyone can add, update, or delete rows through the user view, DB2DBG.ROUTINE_DEBUG_USER. Therefore, the rest of this section assumes that you are populating that table through the user view.

If you use the Stored Procedure Builder to invoke debugging, you can use the debugger utility to populate and manage the debug table. Otherwise, to enable debugging support for a given stored procedure, issue the following command from the CLP:

     DB2 INSERT INTO db2dbg.routine_debug_user (AUTHID, TYPE, 
        ROUTINE_SCHEMA, SPECIFICNAME, DEBUG_ON, CLIENT_IPADDR) 
     VALUES ('authid', 'S', 'schema', 'proc_name', 'Y', 'IP_num')

where:

authid
The user name used for debugging the stored procedure, that is, the user name used to connect to the database.

schema
The schema name for the stored procedure.

proc_name
The specific name of the stored procedure. This is the specific name that was provided on the CREATE PROCEDURE command or a system generated identifier, if no specific name has been provided.

IP_num
The IP address in the form nnn.nnn.nnn.nnn of the client used to debug the stored procedure.

For example, to enable debugging for the stored procedure MySchema.myProc by the user USER1 with the debugging client located at the IP address 123.234.111.222, type the following command:

     DB2 INSERT INTO db2dbg.routine_debug_user (AUTHID, TYPE, 
        ROUTINE_SCHEMA, SPECIFICNAME, DEBUG_ON, CLIENT_IPADDR) 
     VALUES ('USER1', 'S', 'MySchema', 'myProc', 'Y', '123.234.111.222')

If you drop a stored procedure, its debug information is not automatically deleted from the debug table. Debug information for non-existent stored procedures cannot harm your database or instance. If you want to keep the debug table synchronized with the DB2 catalog, you must delete the debug information manually.

Whether you create the debug table manually or through the Stored Procedure Builder, the debug table is named DB2DBG.ROUTINE_DEBUG and has the following definition:

Table 21. DB2DBG.ROUTINE_DEBUG Table Definition
Column Name Data Type Attributes Description
AUTHID VARCHAR(128)
NOT NULL,
DEFAULT USER

The application authid under which the debugging for this stored procedure is to be performed. This is the userid that was provided on connect to the database.
TYPE CHAR(1) NOT NULL Valid values: 'S' (Stored Procedure)
ROUTINE_SCHEMA VARCHAR(128) NOT NULL Schema name of the stored procedure to be debugged
SPECIFICNAME VARCHAR(18) NOT NULL Specific name of the stored procedure to be debugged
DEBUG_ON CHAR(1)
NOT NULL,
DEFAULT 'N'

Valid values:

  • Y - enables debugging for the stored procedure named in ROUTINE_SCHEMA.SPECIFICNAME

  • N - disables debugging for stored procedure named in ROUTINE_SCHEMA.SPECIFICNAME. This is the default.
CLIENT_IPADDR VARCHAR(15) NOT NULL The IP address of the client that does the debugging of the form nnn.nnn.nnn.nnn
CLIENT_PORT INTEGER
NOT NULL,
DEFAULT 8000

The port of the debugging communication. The default is 8000.
DEBUG_STARTN INTEGER NOT NULL Not used.
DEBUG_STOPN INTEGER NOT NULL Not used.

The primary key of this table is AUTHID, TYPE, ROUTINE_SCHEMA, SPECIFICNAME.

Invoking the Debugger

If you have successfully followed the previous steps, calling a stored procedure invokes the debugger on the client with the IP address that you specified in the debug table.

In the debugger, you can step through the source code, display variables, and set breakpoints in the source code. For detailed information on using the debugger, see the debugger documentation contained in the online help.

Java Stored Procedures and UDFs

Java stored procedures and UDFs, hereafter referred to as Java functions, must be registered in the DB2 catalog. DB2 Universal Database Version 6 supports the SQLJ Routines core specification for registering and deploying Java functions. Use PARAMETER STYLE JAVA in your CREATE PROCEDURE and CREATE FUNCTION statements to specify compliance with SQLJ Routines.

Alternatively, DB2 supports DB2 V5 and V5.2 PARAMETER STYLE DB2GENERAL stored procedures and UDFs. For more information, see Appendix C, DB2DARI and DB2GENERAL Stored Procedures and UDFs.

To register a Java function or stored procedure, follow these steps:

  1. Create the Java function as a Java method. Compile the Java source code into a Java class file. For information on creating Java stored procedures, see "Stored Procedures". For information on creating Java UDFs, see "Creating and Using Java User-Defined Functions".

  2. Collect the class file containing the Java function in a jar file. You can collect one or more class files in a single JAR file. For instructions on creating JAR files, refer to the Application Building Guide.

  3. Install the JAR file in the DB2 instance. For instructions on how to use the CALL SQLJ.INSTALL_JAR command from the command line, see Installing, Replacing, and Removing JAR Files. You can also CALL the sqlj.install_jar procedure in an application.

  4. Issue the appropriate CREATE PROCEDURE or CREATE FUNCTION SQL statement for the Java function.

When you install a JAR file, DB2 extracts the Java class files from the JAR file and registers each procedure in the system catalog. DB2 copies the JAR file to a jar/schema subdirectory of the function directory. DB2 gives the new copy of the JAR file the name given in the jar-id clause. Do not directly modify a JAR file which has been installed in the DB2 instance. Instead, you can use the CALL SQLJ.REMOVE_JAR and CALL SQLJ.REPLACE_JAR commands to remove or replace an installed JAR file.

Installing, Replacing, and Removing JAR Files

To install or replace a JAR file in the DB2 instance, you can use the following command syntax at the Command Line Processor:

                                             (1)                   (2)
>>-CALL-+-SQLJ.INSTALL_JAR-+---(--'--jar-url-------'--,--'--jar-id-------'--)-->
        '-SQLJ.REPLACE_JAR-'
 
>--------------------------------------------------------------><
 

Notes:

  1. Specifies the URL containing the JAR file to be installed or replaced. The only URL scheme supported is 'file:'.

  2. Specifies the JAR identifier in the database to be associated with the file specified by the jar-url.

Note:On OS/2 and Windows 32-bit operating systems, DB2 stores JAR files in the path specified by the DB2INSTPROF instance-specific registry setting. To make JAR files unique for an instance, you must specify a unique value for DB2INSTPROF for that instance.

For example, to install the Proc.jar file located in the file:/home/db2inst/classes/ directory in the DB2 instance, issue the following command from the Command Line Processor:

   CALL SQLJ.INSTALL_JAR('file:/home/db2inst/classes/Proc.jar' , 'myproc_jar')

Subsequent SQL commands that use of the Procedure.jar file refer to it with the name myproc_jar. To remove a JAR file from the database, use the CALL REMOVE_JAR command with the following syntax:

                                        (1)
>>-CALL-SQLJ.REMOVE_JAR----(--'--jar-id-------'--)-------------><
 

Notes:

  1. Specifies the JAR identifier of the JAR file that is to be removed from the database

To remove the JAR file myProc_jar from the database, enter the following command at the Command Line Processor:

   CALL SQLJ.REMOVE_JAR('myProc_jar')

Function Definitions

To create a Java function, you must code the corresponding public static method in a public class. A Java function must also be declared with the throws SQLException clause. Code the method signature and the rest of the method declaration to correspond with the output expected from the method body.

Functions That Return No Values  To create a method that returns no values to the calling program, declare the method to return void and include any parameters in the signature that need to be passed to the method body. You can write a stored procedure that performs a simple UDPATE and returns no value to the client application as follows:

   public class JavaExamples {
      public static void updateJob(String oldJob, String newJob) 
            throws SQLException {
         Connection conn=DriverManager.getConnection("jdbc:ibm.db2.sample");
         PreparedStatement stmt = conn.prepareStatement("UPDATE employee 
            SET job = ? WHERE job = ?");
         stmt.setString(1, newJob);
         stmt.setString(2, oldJob);
         stmt.executeUpdate();
         conn.close();
         return;
         }
      }

Functions That Return A Single Value  Declare Java methods that return a single value with the Java return type that corresponds to the respective SQL data type (see Supported SQL Data Types). You can write a scalar UDF that returns an SQL INTEGER value as follows:

public class JavaExamples {
   public static int getDivision(String division) throws SQLException {
      if (division.equals("Corporate")) return 1;
      else if (division.equals("Eastern")) return 2;
      else if (division.equals("Midwest")) return 3;
      else if (division.equals("Western")) return 4;
      else return 5;
   }
}

Functions That Return Multiple Values  Java methods which are cataloged as stored procedures may return one or more values. You can also write Java stored procedures that return multiple result sets; see Returning Result Sets from Stored Procedures. To code a method which will return a predetermined number of values, declare the return type void and include the types of the expected output as arrays in the method signature. You can write a stored procedure which returns the names, years of service, and salaries of the two most senior employees with a salary under a given threshold as follows:

public Class JavaExamples {
   public static void lowSenioritySalary 
      (String[] name1, int[] years1, BigDecimal[] salary1,
       String[] name2, int[] years2, BigDecimal[] salary2,
       Integer threshhold) throws SQLException {
      #sql iterator ByNames (String name, int years, BigDecimal salary);
      ByNames result;
      #sql result = {"SELECT name, years, salary
                      FROM staff
                      WHERE salary < :threshhold
                      ORDER BY years DESC"};
      if (result.next()) {
         name1[0] = result.name();
         years1[0] = result.years();
         salary1[0] = result.salary();
      }
      else {
         name1[0] = "****";
         return;
      }
      if (result.next()) {
         name2[0] = result.name();
         years2[0] = result.years();
         salary2[0] = result.salary();
      }
      else {
         name2[0] = "****";
         return;
      }
   }
}


[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]