Definition of a Java routine to Db2
Before you can use a Java routine, you need to define it to Db2 using the CREATE PROCEDURE or CREATE FUNCTION statement.
The definition for a Java routine is much like the definition for a routine in any other language. However, the following parameters have different meanings for Java routines.
- LANGUAGE
- Specifies the application programming language in which the routine
is written.
Specify LANGUAGE JAVA.
You cannot specify LANGUAGE JAVA for a user-defined table function.
- EXTERNAL NAME
- Specifies the program that runs when the procedure name is specified
in a CALL statement or the user-defined function name is specified
in an SQL statement. For Java routines,
the argument of EXTERNAL NAME is a string that is enclosed in single
quotation marks. The EXTERNAL NAME clause for a Java routine has the following syntax: Notes:
- 1 For compatibility with Db2, you can use an exclamation point (!) after JAR-name instead of a colon.
- 2 For compatibility with previous versions of Db2, you can use a slash (/) after package-name instead of a period.
Whether you include JAR-name depends on where the Java code for the routine resides. If you create a JAR file from the class file for the routine (the output from the javac command), you need to include JAR-name. You must create the JAR file and define the JAR file to Db2 before you execute the CREATE PROCEDURE or CREATE FUNCTION statement. If some other user executes the CREATE PROCEDURE or CREATE FUNCTION statement, you need to grant the USAGE privilege on the JAR to them.
If you use a JAR file, that JAR file must refer to classes that are contained in that JAR file, are found in the CLASSPATH, or are system-supplied. Classes that are in directories that are referenced in DB2_HOME or JCC_HOME, and JAVA_HOME do not need to be included in the JAR file.
Whether you include (method-signature) depends on the following factors:- The way that you define the parameters in your routine method
Each SQL data type has a corresponding default Java data type. If your routine method uses data types other than the default types, you need to include a method signature in the EXTERNAL NAME clause. A method signature is a comma-separated list of data types.
- Whether you overload a Java routine
If you have several Java methods in the same class, with the same name and different parameter types, you need to specify the method signature to indicate which version of the program is associated with the Java routine.
If your stored procedure returns result sets, you also need to include a parameter in the method signature for each result set. The parameter can be in one of the following forms:java.sql.ResultSet
[]- An array of an SQLJ iterator class
Example: EXTERNAL NAME clause for a Java user-defined function: Suppose that you write a Java user-defined function as method getSals in class S1Sal and package s1. You put S1Sal in a JAR file named sal_JAR and install that JAR in Db2. The EXTERNAL NAME parameter is :EXTERNAL NAME 'sal_JAR:s1.S1Sal.getSals'
Example: EXTERNAL NAME clause for a Java stored procedure: Suppose that you write a Java stored procedure as method getSals in class S1Sal. You put S1Sal in a JAR file named sal_JAR and install that JAR in Db2. The stored procedure has one input parameter of type INTEGER and returns one result set. The Java method for the stored procedure receives one parameter of type java.lang.Integer, but the default Java data type for an SQL type of INTEGER is int, so the EXTERNAL NAME clause requires a signature clause. The EXTERNAL NAME parameter is :EXTERNAL NAME 'sal_JAR:S1Sal.getSals(java.lang.Integer,java.sql.ResultSet[])'
- NO SQL
- Indicates that the routine does not contain any SQL statements.
For a Java routine that is stored in a JAR file, you cannot specify NO SQL.
- PARAMETER STYLE
- Identifies the linkage convention that is used to pass parameters
to the routine.
For a Java routine, the only value that is valid is PARAMETER STYLE JAVA.
You cannot specify PARAMETER STYLE JAVA for a user-defined table function.
- WLM ENVIRONMENT
- Identifies the MVS workload
manager (WLM) environment in which the routine is to run.
If you do not specify this parameter, the routine runs in the default WLM environment that was specified when Db2 was installed.
- PROGRAM TYPE
- Specifies whether Language Environment® runs the routine as a main routine or a subroutine.
This parameter value must be PROGRAM TYPE SUB.
- RUN OPTIONS
- Specifies the Language
Environment run-time options to be used for the routine.
This parameter has no meaning for a Java routine. If you specify this parameter with LANGUAGE JAVA, Db2 issues an error.
- SCRATCHPAD
- Specifies that when the user-defined function is invoked for the
first time, Db2 allocates memory
for a scratchpad.
You cannot use a scratchpad in a Java user-defined function. Do not specify SCRATCHPAD when you create or alter a Java user-defined function.
- FINAL CALL
- Specifies that a final call is made to the user-defined function,
which the function can use to free any system resources that it has
acquired.
You cannot perform a final call when you call a Java user-defined function. Do not specify FINAL CALL when you create or alter a Java user-defined function.
- DBINFO
- Specifies that when the routine is invoked, an additional argument
is passed that contains environment information.
You cannot pass the additional argument when you call a Java routine. Do not specify DBINFO when you call a Java routine.
- SECURITY
- Specifies how the routine interacts with an external security
product, such as RACF®, to control
access to non-SQL resources. The values of the SECURITY parameter
are the same for a Java routine
as for any other routine. However, the value of the SECURITY parameter
determines the authorization ID that must have authority to access z/OS®
UNIX System Services.
The values of SECURITY and the IDs that must have access to z/OS
UNIX System Services are:
- Db2
- The user ID that is defined for the stored procedure address space in the RACF started-procedure table.
- EXTERNAL
- The invoker of the routine.
- DEFINER
- The definer of the routine.
- ALLOW DEBUG MODE, DISALLOW DEBUG MODE, or DISABLE DEBUG MODE
- Specifies whether a Java stored
procedure can be run in debugging mode. When DYNAMICRULES run behavior
is in effect, the default is determined by using the value of the
CURRENT DEBUG MODE special register. Otherwise the default is DISALLOW
DEBUG MODE.
- ALLOW DEBUG MODE
- Specifies that the procedure can be run in debugging mode.
- DISALLOW DEBUG MODE
- Specifies that the procedure cannot be run in debugging mode.
You can use an ALTER PROCEDURE statement to change this option to ALLOW DEBUG MODE.
- DISABLE DEBUG MODE
- Specifies that the procedure can never be run in debugging mode.
The procedure cannot be changed to specify ALLOW DEBUG MODE or DISALLOW DEBUG MODE once the procedure has been created or altered using DISABLE DEBUG MODE. To change this option, you must drop and recreate the procedure using the desired option.
Example: Defining a Java stored procedure: Suppose that you have written and prepared a stored procedure that has these characteristics:
Clause | Description |
---|---|
Fully-qualified procedure name | SYSPROC.S1SAL |
Parameters | DECIMAL(10,2) INOUT |
Language | Java |
Collection ID for the stored procedure package | DSNJDBC |
Package, class, and method name | s1.S1Sal.getSals |
Type of SQL statements in the program | Statements that modify Db2 tables |
WLM environment name | WLMIJAV |
Maximum number of result sets returned | 1 |
CREATE PROCEDURE SYSPROC.S1SAL
(DECIMAL(10,2) INOUT)
FENCED
MODIFIES SQL DATA
COLLID DSNJDBC
LANGUAGE JAVA
EXTERNAL NAME 's1.S1Sal.getSals'
WLM ENVIRONMENT WLMIJAV
DYNAMIC RESULT SETS 1
PROGRAM TYPE SUB
PARAMETER STYLE JAVA;
Example: Defining a Java user-defined function: Suppose that you have written and prepared a user-defined function that has these characteristics:
Clause | Description |
---|---|
Fully-qualified function name | MYSCHEMA.S2SAL |
Input parameter | INTEGER |
Data type of returned value | VARCHAR(20) |
Language | Java |
Collection ID for the function package | DSNJDBC |
Package, class, and method name | s2.S2Sal.getSals |
Java data type of the method input parameter | java.lang.Integer |
JAR file that contains the function class | sal_JAR |
Type of SQL statements in the program | Statements that modify Db2 tables |
Function is called when input parameter is null? | Yes |
WLM environment name | WLMIJAV |
CREATE FUNCTION MYSCHEMA.S2SAL(INTEGER)
RETURNS VARCHAR(20)
FENCED
MODIFIES SQL DATA
COLLID DSNJDBC
LANGUAGE JAVA
EXTERNAL NAME 'sal_JAR:s2.S2Sal.getSals(java.lang.Integer)'
WLM ENVIRONMENT WLMIJAV
CALLED ON NULL INPUT
PROGRAM TYPE SUB
PARAMETER STYLE JAVA;
In this function definition,
you need to specify a method signature in the EXTERNAL NAME clause
because the data type of the method input parameter is different from
the default Java data type for
an SQL type of INTEGER.