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 in Java) 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 JVM. Ensure that the appropriate Java Development Kit (JDK) or Java Runtime Environment is installed and configured on your DB2 server before starting up the database.
The runtime libraries for the JVM 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 JVM on the first call to a Java UDF or stored procedure. For NOT FENCED UDFs and stored procedures, DB2 loads one JVM per database instance, and runs it inside the address space of the database engine to improve performance. For FENCED UDFs, DB2 uses a distinct JVM inside the db2udf process; similarly, FENCED stored procedures use a distinct JVM inside the db2dari process. In all cases, the JVM 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. |
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.
| Note: | If you update or replace Java routine class files, you must issue a CALL SQLJ.REFRESH_CLASSES() statement to enable DB2 to load the updated classes. For more information on the CALL SQLJ.REFRESH_CLASSES() statement, refer to Updating Java Classes for Routines. |
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:
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 JVM 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 JVM can find 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.
When you update Java routine classes, you must also issue a CALL SQLJ.REFRESH_CLASSES() statement to force DB2 to load the new classes. If you do not issue the CALL SQLJ.REFRESH_CLASSES() statement after you update Java routine classes, DB2 continues to use the previous versions of the classes. The CALL SQLJ.REFRESH_CLASSES() statement only applies to FENCED routines. DB2 refreshes the classes when a COMMIT or ROLLBACK occurs.
| Note: | You cannot update NOT FENCED routines without stopping and restarting the database manager. |
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. 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:
db2set DB2ROUTINE_DEBUG=ON
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. |
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.
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:
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 32. 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 user ID 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:
|
| 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.
| |||
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, collectively known as Java routines, must be registered in the DB2 catalog. DB2 Universal Database Version 7 supports the SQLJ Routines core specification for registering and deploying Java routines. 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:
When you install a JAR file, DB2 extracts the Java class files from the JAR file and registers each class 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.
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:
| 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:
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')
To create a Java routine, you must code the corresponding public static method in a public class. A Java routine 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.
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;
}
}
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 in Java). 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;
}
}
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;
}
}
}