Java Stored Procedures and UDFs | DB2(R) Universal Database for OS/390 V7.1 Application Certification Guide (IBM DB2 Certification Guide Series)


Team-Fly

	DB2 Universal Database for OS/390 v7.1 Application Certification Guide By Susan Lawson
	Table of Contents

	Chapter 13. Java Programming

Java Stored Procedures and UDFs

Stored procedures and user -defined functions (UDFs) are programs that can contain SQL statements. You invoke a stored procedure from a client program by executing the SQL CALL statement. You invoke a UDF by specifying the UDF name , followed by its arguments, in an SQL statement. For more information on stored procedures, refer to Chapter 8, "Stored Procedures," and for more information on UDFs, refer to Chapter 15, "Object Relational Programming."

The word routine refers to either a stored procedure or a UDF. The term compiled Java stored procedure refers to a stored procedure that runs under VisualAge for Java. The term interpreted Java stored routine refers to a stored procedure or a user-defined function that runs in a JVM.

Defining a Java Routine to DB2

Defining a Java routine to DB2 involves one or two steps, depending on how you prepare the routine for execution:

First, for interpreted Java routines that you store in JAR files, you need to define the JAR files to DB2. Refer to the section "Defining a JAR File for a Java Routine to DB2" later in this chapter.
Second, for all types of Java routines, you need to define the routine to DB2. If you prepare the Java routine for execution without IBM DB2 Stored Procedure Builder, execute the CREATE PROCEDURE or CREATE FUNCTION statement to define the routine to DB2. To alter the routine definition, use the ALTER PROCEDURE or ALTER FUNCTION statement.

NOTE

If you use the IBM DB2 Stored Procedure Builder to prepare your Java routine for execution, the IBM DB2 Stored Procedure Builder defines the Java routine and the JAR file to DB2 for you.

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. There are two possible values for a Java stored procedure. If the stored procedure runs in a JVM, specify LANGUAGE JAVA. If the stored procedure runs under VisualAge for Java, specify LANGUAGE COMPJAVA. For a Java UDF, which must run in a JVM, specify LANGUAGE JAVA.
EXTERNAL NAME: Specifies the program that runs when the procedure name is specified in a CALL statement or the UDF 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 value in that string depends on the value of LANGUAGE.

Whether you include JAR-name depends on the way that you prepare your stored procedure for execution. If you create a JAR file from the routine class, you need to include JAR-nam e. You must create the JAR file, define the JAR file to DB2, and grant the USAGE privilege on the JAR before you execute the CREATE PROCEDURE or CREATE FUNCTION statement.

If you use a JAR file, that JAR file must be self-contained. That is, if a class within the JAR file references another class, the referenced class must also be in the JAR file. The exception to this rule is that classes that are part of the Java environment, such as java.lang package or the SQLJ/JDBC driver classes do not need to be in the JAR file.

Whether you include a 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 programs 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 a class that is declared as an SQLJ iterator

Example: EXTERNAL NAME clause for a Java user-defined function. Suppose that you write a Java UDF 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 [])'

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 when the DB2 stored procedure address space is WLM-established.

- A Java routine must run in a WLM-established address space, so this parameter must be specified.
PROGRAM TYPE: Specifies whether Language Environment runs the routine as a main routine or a subroutine. This parameter value must be PROGRAM TYPE SUB. However, you can write a Java routine as a Java main method.
RUN OPTIONS: Specifies the Language Environment runtime options to be used for the routine. This parameter has no meaning for a Java routine. If you specify this parameter with LANGUAGE JAVA or LANGUAGE COMPJAVA, 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 call 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 call a Java user-defined function.
DBINFO: Specifies that when the routine is invoked, an additional argument is passed that contains environmental 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 OS/390 UNIX System Services. The values of SECURITY and the IDs that must have access to OS/390 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.

Defining a Java Stored Procedure

Suppose that you have written and prepared a stored procedure that runs under VisualAge for Java and has these characteristics:

Fully-qualified procedure name: SYSPROC.S1SAL
Parameters: DECIMAL(10,2) INOUT
Language: Java
Runtime environment: VisualAge for Java
Collection ID for the stored procedure package: DSNJDBC
Java executable name: s1.S1Sal.getSals
Type of SQL statements in the program: Statements that modify DB2 tables
WLM environment name: WLMENV1
Maximum number of result sets returned: 1

This CREATE PROCEDURE statement defines the stored procedure to DB2:

 CREATE PROCEDURE SYSPROC.S1SAL  (DECIMAL(10,2)INOUT) FENCED MODIFIES SQL DATA COLLID DSNJDBC LANGUAGE COMPJAVA EXTERNAL NAME 's1.S1Sal.getSals' WLM ENVIRONMENT WLMENV1 DYNAMIC RESULT SETS 1 PROGRAM TYPE SUB PARAMETER STYLE JAVA;

Defining a Java UDF

Suppose that you have written and prepared a user-defined function that has these characteristics:

Fully-qualified function name: MYSCHEMA.S2SAL
Input parameter: INTEGER
Output parameter: VARCHAR(20)
Language: Java
Runtime environment: JVM
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: WLMENV1

This CREATE FUNCTION statement defines the UDF to DB2:

 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 WLMENV1 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 a SQL type of INTEGER.

Defining a JAR File for a Java Routine to DB2

One way to organize the classes for a Java routine is to collect those classes into a JAR file. If you do this, you need to install the JAR file into the DB2 catalog. DB2 provides three built-in stored procedures that perform the following functions for the JAR file:

SQLJ.INSTALL_JAR installs a JAR file into the DB2 catalog.
SQLJ.REPLACE_JAR replaces an existing JAR file in the DB2 catalog.
SQLJ.REMOVE_JAR deletes a JAR file from the DB2 catalog.

You can use the IBM DB2 Stored Procedure Builder to install JAR files into the DB2 catalog, or you can write a client program that executes SQL CALL statements to invoke the SQLJ.INSTALL_JAR, SQLJ.REPLACE_JAR, and SQLJ.REMOVE_JAR stored procedures. The following sections explain how to call the stored procedures.

Calling SQLJ.INSTALL_JAR

Use SQLJ.INSTALL_JAR to create a new definition of a JAR file in the DB2 catalog. The syntax for the statement is

 CALL SQLJ.INSTALL_JAR (  url  ,  jar-name  ,  deploy  )

The meanings of the parameters are

url : a VARCHAR(128) input parameter that identifies the hierarchical file system (HFS) full path name for the JAR file that is to be installed in the DB2 catalog. The format is file://path-name.
jar-name : a VARCHAR(27) input parameter that contains the DB2 name of the JAR, in the form schema.jar-id or jar-id. This is the name that you use when you refer to the JAR in SQL statements. If you omit schema, DB2 uses the default schema name
deploy : an INTEGER input parameter that indicates whether additional actions should be performed after the JAR file is installed. Additional actions are not supported, so this value should always be 0.

Calling SQLJ.REPLACE_JAR

Use SQLJ.REPLACE_JAR to replace an existing JAR file in the DB2 catalog. The syntax for the statement is

 CALL SQLJ.REPLACE_JAR (  url  ,  jar-name  )

The meanings of the parameters are

url : a VARCHAR(128) input parameter that identifies the HFS full path name for the JAR file that replaces the existing JAR file in the DB2 catalog. The format is file://path-name.
jar-name : a VARCHAR(27) input parameter that contains the DB2 name of the JAR, in the form schema.jar-id or jar-id. This is the name that you use when you refer to the JAR in SQL statements. If you omit schema, DB2 uses the default schema name.

Calling SQLJ.REMOVE_JAR

Use SQLJ.REMOVE_JAR to delete a JAR file from the DB2 catalog. The syntax for the statement is

 CALL SQLJ.REMOVE_JAR (  jar-name  ,  undeploy  )

The meanings of the parameters are

jar-name: a VARCHAR(27) input parameter that contains the DB2 name of the JAR that is to be removed from the catalog in the form.
schema.jar-id or jar-id : This is the name that you use when you refer to the JAR in SQL statements. If you omit schema, DB2 uses the default schema name.
undeploy : an INTEGER input parameter that indicates whether additional actions should be performed after the JAR file is removed. Additional actions are not supported, so this value should always be 0.

Writing a Java Routine

A Java routine is a JDBC or SQLJ application program that runs in a stored procedure address space. A Java routine is much like any other Java program and follows the same rules as routines in other languages. It receives input parameters, executes Java statements, optionally executes SQLJ clauses, JDBC methods , or a combination of both, and returns output parameters.

Differences Between Java Routines and Standalone Java Programs

A Java routine differs from a standalone Java program in the following ways:

A Java routine does not establish its own connection to the local data source. Instead, the routine uses the default recoverable resource management services (RRS) connection to the data source that processes the CALL statement or the statement that contains the UDF invocation. If you want to execute SQLJ clauses or JDBC methods at another location, use the same methods to connect to those locations as you do in SQLJ or JDBC application programs.
A Java routine must be declared as static and public.
As in routines in other languages, the SQL statements that you can execute in the routine depend on whether you specify a SQL access level of NO SQL, CONTAINS SQL, READS SQL DATA, or MODIFIES SQL DATA.

Differences Between Java Routines and Other Routines

A Java routine differs from stored procedures that are written in other languages in the following ways:

A Java routine must be defined with PARAMETER STYLE JAVA. PARAMETER STYLE JAVA specifies that the routine uses a parameter-passing convention that conforms to the Java language and SQLJ specifications. DB2 passes INOUT and OUT parameters as single-entry arrays.
Java main programs must have a signature of String[]. If your Java routine is a main method, it must be possible to map all the parameters to Java variables of type java.lang.String.
You cannot make instrumentation facility interface (IFI) calls in Java routines.
As in other Java programs, you cannot include the following statements in a Java routine:

- CONNECT

- RELEASE

- SET CONNECTION
The mappings between data types for routine parameters and host data types follow the rules for mappings between SQL and SQLJ data types.
The technique for returning result sets from Java stored procedures is different from the technique for returning result sets in other stored procedures.

Writing a Java Stored Procedure to Return Result Sets

Your stored procedure can return multiple query result sets to a DRDA client if the following conditions are satisfied:

The client supports the DRDA code points that are used to return query result sets.
The value of RESULT_SETS in the stored procedure definition is greater than 0.

For each result set you want to be returned, your Java stored procedure must perform the following actions

For each result set, include an object of type java.sql.ResultSet[] or an array of a class that is declared as a SQLJ iterator in the parameter list for the stored procedure method. If the stored procedure definition includes a method signature, then for each result set, include java.sql.ResultSet[] or the fully qualified name of an array of a class that is declared as a SQLJ iterator in the method signature. Do not include a java.sql.ResultSet array or an iterator array in the SQL parameter list of the stored procedure definition.
Execute a SELECT statement to obtain the contents of the result set.
Retrieve any rows that you do not want to return to the client.
Assign the contents of the result set to the java.sql.ResultSet[] object that is in the parameter list.
Do not close the ResultSet, the statement that generated the ResultSet, or the connection that is associated with the statement that generated the ResultSet. DB2 does not return result sets for ResultSets that are closed before the stored procedure terminates.

The code below shows an example of a Java stored procedure that uses an SQLJ iterator to retrieve a result set.

 package s1;  import sqlj.runtime.*; import java.sql.*; import java.math.*; ₁ #sql iterator NameSal(String LastName,BigDecimal Salary); public class S1Sal { ₂ public static void getSals(BigDecimal []AvgSalParm,java.sql.ResultSet []rs) throws SQLException { NameSal iter1; try { ₃ #sql iter1 ={SELECT LASTNAME,SALARY FROM EMP WHERE SALARY>0 ORDER BY SALARY DESC}; ₄ #sql {SELECT AVG(SALARY)INTO :(AvgSalParm [0 ])FROM EMP}; } catch (SQLException e) { System.out.println("SQLCODE returned:"+e.getErrorCode()); throw(e); } ₅ rs [0 ]=iter1.getResultSet(); } }

Code Notes

This SQLJ clause declares the iterator named NameSal, which is used to retrieve the rows that will be returned to the stored procedure caller in a result set.
The declaration for the stored procedure method contains declarations for a single passed parameter, followed by the declaration for the result set object.
This SQLJ clause executes the SELECT to obtain the rows for the result set, constructs an iterator object that contains those rows, and assigns the iterator object to variable iter1.
This SQLJ clause retrieves a value into the parameter that is returned to the stored procedure caller.
This statement uses the GetResultSet method to assign the contents of the iterator to the result set that is returned to the caller.

Running a Java Routine

Like other routines, Java routines run under Language Environment and in a stored procedure address space. A Java routine always runs as a subprogram.

The Stored Procedure Address Space for Java Routines

A Java routine must run in a WLM-established stored procedure address space. The startup procedure for Java routines requires extra DD statements that other routines do not need. The code below shows an example of a startup procedure for an address space in which Java routines can run.

 //DSNWLM PROC RGN=0K,APPLENV=WLMCJAV,DB2SSN=DSN,NUMTCB=1  //IEFPROC EXEC PGM=DSNX9WLM,REGION=&RGN,TIME=NOLIMIT, //PARM='&DB2SSN,&NUMTCB,&APPLENV' //STEPLIB DD DISP=SHR,DSN=DSN710.RUNLIB.LOAD ¹ //DD DISP=SHR,DSN=HPJSP.PDSE1  //DD DISP=SHR,DSN=CEE.SCEERUN  //DD DISP=SHR,DSN=DSN710.SDSNEXIT  //DD DISP=SHR,DSN=DSN710.SDSNLOAD ² //DD DISP=SHR,DSN=HPJ.SQLJ,DISP=SHR ³ //DD DISP=SHR,DSN=HPJ.SHPJMOD   //DD DISP=SHR,DSN=HPJ.SHPOMOD   //DD DISP=SHR,DSN=VAJAVA.V2R0M0.SHPOMOD ⁴ //JAVAENV DD DISP=SHR,DSN=WLMCJAV.JSPENV ⁵ //JSPDEBUG DD SYSOUT=A   //CEEDUMP DD SYSOUT=A   //SYSPRINT DD SYSOUT=A

Code Notes

This DD statement specifies the PDSE that contains the Java program objects for compiled Java stored procedures.
This DD statement specifies the PDSE that contains Java program objects for Java classes that are referenced by the stored procedure.
This DD statement and the following DD statement specify the PDSEs that contain the VisualAge for Java compiler and runtime library.
JAVAENV specifies a dataset that contains environmental variables that specify system properties for the ET/390 Java execution environment.
JSPDEBUG specifies a dataset into which DB2 puts information that you can use to debug your stored procedure.

If you specify SYSOUT=A in the JSPDEBUG DD statement, the debug information is written to your SYSOUT dataset. If you specify a dataset name, you also need to specify the MSGFILE( ddnam e) runtime option in your JAVAENV dataset. Specify JSPDEBUG for ddname to direct all diagnostic output to the JSPDEBUG dataset. If you do not redirect standard output, println statements in your stored procedure program write text to the JSPDEBUG dataset.

Setting Environmental Variables for Java Routines

For Java routines, the startup procedure for the stored procedure address space contains a JAVAENV DD statement. This statement specifies a dataset that contains environmental variables for the VisualAge for Java execution environment. You need to set the following environmental variables in the JAVAENV data set:

CLASSPATH : Modify CLASSPATH to include the following HFS directories: (1) the directories that contain the external links to the routines that run in the WLM-established stored procedure address space, and (2) the directory in which the VisualAge for Java links are defined ($IBMHPJ_HOME/lib). For example,
```
 CLASSPATH=.:/u/sysadm/links:/usr/lpp/hpj/lib 
```
LIBPATH and LD_LIBRARY_PATH : Modify LIBPATH and LD_LIBRARY_PATH to include the following HFS directories: (1) the path for the ET/390 code ($IBMHPJ_HOME/lib), and (2) the path for the SQLJ/JDBC driver code, if the stored procedures contain SQL. For example,
```
 LIBPATH=/u/sysadm/links:/usr/lpp/hpj/lib:/u/hpjsp/lib  LD_LIBRARY_PATH=/u/sysadm/links:/usr/lpp/hpj/lib:/u/hpjsp/lib 
```
JAVA_HOME : For an interpreted Java routine, used to determine Java.home. DB2 uses this environment variable to determine whether the WLM environment is for a compiled Java stored procedure or an interpreted Java routine. Therefore, if the startup procedure is for a WLM environment for interpreted Java routines, you need to include this environment variable. For example:
```
 JAVA_HOME=/usr/local/Java 
```
LANG : Modify LANG to change the locale to use for the locale categories when neither the LC_ALL environment variable nor the individual locale environment variables specify locale information. For example:
```
 LANG="En_US.IBM-037" 
```
LC_ALL : Modify LC_ALL to change the locale to be used to override any values for locale categories specified by the settings of the LANG environment variable or any individual locale environmental variables. For example,
```
 LC_ALL="S370" 
```
LC_CTYPE : Modify LC_CTYPE to change the locale for character classification, case conversion, and other character attributes. This value should match the DB2 for OS/390 and z/OS installation default. For example,
```
 LC_CTYPE="En_US.IBM-037" 
```
WORK_DIR: Specify WORK_DIR to direct diagnostic output to an HFS directory. When you specify the JSPDEBUG DD statement in your WLM address space startup procedure, DB2 creates three data sets in this directory for diagnostic information. Those data sets are server_stdin.txt, server_stdout.txt, and server_stderr.txt.
TZ : Modify TZ to change the local time zone. The default is GMT. For example,
```
 TZ="PST08" 
```

The following example shows the contents of a JAVAENV dataset.

 ENVAR("CLASSPATH=.:/u/sysadm/links:/usr/lpp/hpj/lib",  "TZ=PST08", "LIBPATH=/u/sysadm/links:/usr/lpp/hpj/lib:/u/hpjsp/lib", "LD_LIBRARY_PATH=/u/sysadm/links:/usr/lpp/hpj/lib:/u/hpjsp/lib"), MSGFILE(JSPDEBUG)

Testing a Java Routine

Before you invoke your Java routines from SQL applications, it is a good idea to run the routines as standalone programs, which are easier to debug. When you are ready to invoke your programs as Java routines, include a JSPDEBUGDD statement in your startup procedure for the stored procedure address space. This DD statement specifies a dataset to which DB2 writes debug information as the Java routines execute.


Team-Fly

Top