© 2002 International Business Machines Corporation. All rights reserved.

Introduction

E-business enables businesses to leverage the use of technology to gain an advantage in the marketplace. With this technology, businesses can expand their customer base, provide faster, more efficient services, offer increased accessibility, flexibility and convenience for customers, and develop more personalized services. In addition, the overhead associated with implementing business processes can be minimized or reduced by leveraging e-business technology to streamline interactions with customers, suppliers and business partners.

The IBM e-business infrastructure [1] facilitates the building, running and management of successful e-business applications. It consists of an open, standards-based model that enables multi-platform, multi-vendor solutions, proven techniques for developing, deploying and managing e-business applications and a diverse portfolio of products. The infrastructure can assist in building the best end-to-end solutions needed by customers to meet their business and technology needs as they transform into e-businesses. The WebSphere® Application Server, coupled with the DB2®, database management system (DBMS) is the foundation of this infrastructure, enabling businesses to leverage existing resources to gain a competitive edge.

Figure 1 shows a multi-tiered, web-enabled environment targeted for e-businesses, using WebSphere and DB2. The first tier consists of a Web browser or some other type of client application, including a PC or wireless device. The browser communicates with a middle tier server via HTTP while other client applications may communicate with the middle tier using alternative communication protocols. This middle tier invokes Java^TM Server Pages (JSPs) or servlets, which are managed by WebSphere. The JSPs and servlets may require access to the DB2 data source in this environment, using Java Database Connectivity (JDBC) or SQL for Java (SQLJ) calls [2]. Web-enabled applications developed in this environment can use several components of the Java 2 enterprise edition (J2EE) server environment [3]. This includes support for Enterprise Java Beans (EJBs) [4], servlets, JSPs, and the Java Naming and Directory Interface (JNDI). JSPs provide server-side scripting, and servlets enable code execution on the middle tier server. Also, EJBs, executing via an EJB server on the middle tier, provide component execution on the middle tier (as described in more detail in Considerations for stored procedures and EJBs). J2EE also offers support for connecting to and accessing the DBMS, including support for JDBC and Java Transaction API (JTA). Furthermore, SQLJ and Extensible Markup Language (XML) are also important open standards technologies and support for them is desirable when developing e-business applications.

The application server processes the request using a servlet engine or EJB server, which may execute an invoked servlet, either directly or via a compiled JSP, to compose a HTML page to be returned to the client browser. The page is created by integrating its static presentation with the results of any dynamic runtime execution, which may include accessing a DBMS on the last tier. JSPs and servlets may also indirectly access a remote data source using EJBs, which are invoked using remote method invocation. In addition to Web browsers, other types of client applications can invoke EJBs directly by remote method invocation. These applications also have the option of directly accessing the DB2 data server using JDBC or SQLJ.

This article includes a step-by-step guide to creating and executing Java stored procedures in a WebSphere-DB2 environment on an eServer, zSeries^TM machine. (This step-by-step guide applies only to interpreted Java stored procedures).

Back to top

Writing Java stored procedures

Here is a summary of the steps to successfully write and execute interpreted Java stored procedures in a z/OS^TM environment. Detailed instructions for each step are also provided.

• Step 1: Verify that the OS/390© system has the required MVS PTFs to run Java stored procedures
• Step 2: Verify that the OS/390 system has the required JDK level
• Step 3: Set up a Work Load Manager (WLM) address space to run Java stored procedures
• Step 4: Set up the environment variables in the JAVENV dataset
• Step 5: Define the JAR files to DB2 (if applicable)
• Step 6: Define the Java stored procedure to DB2
• Step 7: Write a calling program to invoke the stored procedure
• Step 8: Run the stored procedure
• Step 9: Analyze the results

In addition, this section includes a description of how to invoke a Java stored procedure from a servlet that runs under the WebSphere Application Server on z/OS. DB2 PTF PQ46673 must be applied to run Java stored procedures. If you are planning to run Java user-defined functions (UDFs), apply DB2 PTF PQ50443.

Step 1: Verify that the OS/390 system has the required MVS PTFs to run Java stored procedures

Java stored procedures must run with the IBM Developer Kit for OS/390, Java 2 technology Edition with Persistent Reusable Java Virtual Machines. This JDK has as a prerequisite various MVS PTFs that must be applied. Table 1 lists the PTFs as of the time we wrote this article.

Table 1. Prerequisite MVS PTFs

To find the latest updates on MVS required PTFs required, go to http://www-1.ibm.com/servers/eserver/zseries/software/java/wizard13s/.

Step 2: Verify that the OS/390 system has the required JDK level

As of this writing, the required Java Virtual Machine (JVM) to run Java stored procedures is J2RE 1.3.1 IBM OS/390 Persistent Reusable VM build hm131s. To download the latest JDK, go to http://www.s390.ibm.com/java/.

Step 3: Set up a Workload Manager (WLM) address space to run Java stored procedures

1. Define a new WLM started task in OS/390

   Java Stored procedures on z/OS and OS/390 must run in a WLM application environment. An MVS system might already have various WLM tasks defined. Unlike stored procedures in other anguages, Java stored procedures have the unique requirement that only Java stored procedures can run in a particular application environment. (For example, a C stored procedure cannot run in the same WLM application environment as a Java stored procedure.) This application environment must first be defined before the stored procedure can be executed. Complete steps on defining a new WLM address space to OS/390 can be found in [5].

2. Set up the WLM proc to run Java stored procedures

   When the new WLM address space has been defined on z/OS, it is time to set up the proc to run Java stored procedures. This requires some extra setup as part of the WLM address space that is not required for stored procedures in other languages.

   When setting up WLM, keep in mind the following:

   • Use separate WLM application environments to run interpreted versus compiled Java stored procedures.
   • If non-Java stored procedures are running on the same MVS system, define a separate WLM stored procedures address space for these.

Listing 1 is an example of job control language (JCL) used to start up the address space. The examples shown in this article were run using this sample JCL.

Listing 1. JCL to start a WLM address space for Java stored procedures

 
   //DT12WLJI PROC RGN=0K,SUBSYS=DT12,NUMTCB=1,APPLENV=DT12WLJI 
   //IEFPROC  EXEC PGM=DSNX9WLM,REGION=&RGN,TIME=NOLIMIT, 
   //         PARM='&SUBSYS,&NUMTCB,&APPLENV' 
   //STEPLIB  DD  DISP=SHR,DSN=DSNT1.DT12.SDSNEXIT 
   //         DD  DISP=SHR,DSN=DSNT1.DT12.SDSNLOAD 
   //         DD  DISP=SHR,DSN=DSNT1.RUNLIB.LOAD 
   //         DD  DISP=SHR,DSN=CEE.SCEERUN 
   //JAVAENV  DD  DISP=SHR,DSN=WLMCJAV.JSPENV 
   //JSPDEBUG DD  SYSOUT=A 
   //SYSPRINT DD  SYSOUT=A 
   //MSGFILE  DD  SYSOUT=A 
   //SYSABOUT DD  SYSOUT=A 
   //CEEDUMP  DD  SYSOUT=A 
   //DSNRRSAF DD  DUMMY 

Notes on Listing 1:

• The SUBSYS parameter should be the name of the DB2 subsystem used to run the Java stored procedures.
• It is recommended that NUMTCB be set to no more than 5 TCBs.
• JAVAENV is a sequential data set that exists on the MVS system. The name of the data set is variable and is set by the creator of the WLM proc. Be sure that the data set is cataloged before attempting to run any stored procedures in the WLM address space. Step 4 discusses in depth the information that must be included in the JAVAENV data set.

Step 4: Set up the environment variables in the JAVENV dataset

The data set named WLMCJAV.JSPENV, specified in the JAVAENV DD card in Listing 1, contains various environment properties such as classpaths, JVM directory, OUTPUT directories, etc. Here is an example of the contents of that dataset.

 
		ENVAR(	"CLASSPATH=/usr/nst/JavaSP/CLASSES:/usr/nst/SQLJ20", 
			 	"JAVA_HOME=/java13s/IBM/J1.3", 
			 	"WORK_DIR=/usr/nst/JavaSP/OUTPUT", 
			 	"DB2_HOME=/usr/lpp/db2/dev710", 
				"DB2SQLJPROPERTIES=/usr/nst/JDBC20") 
 

CLASSPATH - The directory containing the Java class files. In addition, specify as part of the CLASSPATH the location of the directory containing the .ser file for the JDBC DBRMs. The four JDBC DBRMs with default names of DSNJDBC1-4 are created when the db2genJDBC utility is run. In addition to these DBRMs, a DSNJDBC_JDBCProfile.ser file is created. For more information on the db2genJDBC utility, see [

].

JAVA_HOME - JVM home directory.

WORK_DIR - Specifies the directory to contain the output files for the Java stored procedures. When a Java stored procedure is invoked and runs, four output files are created:

• ResetEvents.txt
• server_stderr.txt
• server_stdin.txt
• server_stdout.txt

Any error encountered while the stored procedure is executing is logged in these files. The directory specified here must exist and therefore should be created first before running any Java stored procedures. However, the Java stored procedure output does not always go to these files. See the second bullet under Additional tips for more details.

DB2_HOME - The JDBC driver code directory.

DB2SQLJPROPERTIES - The directory containing the db2sqljjdbc.properties file. Setting this environment variable is not always necessary. See the third bullet under Additional tips for an explanation.

For an extensive list of all the various settings for this dataset, see [6].

Step 5: Define the JAR files to DB2 (if applicable)

The class file to be executed as a Java stored procedure can optionally be stored as a JAR file in the DB2 catalog. If you are not using this approach, continue to Step 6. Otherwise, do the following:

1. Compile the Java stored procedure source code.
2. Run the JAR command to package the stored procedure class file into a JAR file. For example, if the name of the stored procedure source file is MySP.java, then in order to package the MySP.class file into a JAR file called MyJAR, issue the following command:

   jar -cvf MyJAR.jar MySP.class

   
   
3. Now that the JAR file has been created, invoke the INSTALL_JAR stored procedure to define the JAR file to DB2. You can do this by creating a calling program that invokes the INSTALL_JAR stored procedure. A sample calling program written in Java is found in Downloads.

   Alternatively, you can invoke the INSTALL_JAR stored procedure by using the IBM Stored Procedure Builder. The INSTALL_JAR stored procedure takes three input parameters: url, jar-name, and deploy.

   • url - The path to the JAR file. For example, if MyJAR.jar is in the directory /usr/jarfiles, then this parameter should be initialized to the following: file:/usr/jarfiles/MyJar.jar
   • jar-name - The name of the JAR file. This is the name given to the JAR DB2 object. In this example, the name is MyJAR (without the .jar extension).
   • deploy - This parameter tells DB2 whether additional actions should occur after the JAR file is installed. Currently, the only value accepted for this parameter is 0.
4. Finally, now that the JAR file has been defined to DB2, execute the GRANT USAGE ON JAR statement to grant the privilege to use the JAR to the user who defines the stored procedure to DB2.

After all these steps are executed, the JAR file is now defined to DB2.

Step 6: Define the Java stored procedure to DB2

A Java stored procedure is defined in DB2 much as any other stored procedure, with a few exceptions. For example, assume that a stored procedure is named MyJSP and that it takes one input/output parameter of type CHAR(12). The stored procedure is a Java stored procedure with classes that are not packaged in a JAR file. The procedure uses the WLM proc defined in Step 4 (the address space DT12WLJI). The Java stored procedure does not return any result sets, and its package was bound in a collection called SAMPLE. The Java program to be executed is sample1 in class MyJSP.

Here is the DDL that would create the Java stored procedure described above. This DDL is also included in Downloads.

 
CREATE PROCEDURE MyJSP (INOUT CHAR(12)) 
	FENCED 
	MODIFIES SQL DATA 
	COLLID SAMPLE 
	LANGUAGE JAVA 
	EXTERNAL NAME 'MyJSP.sample1' 
	WLM ENVIRONMENT DT12WLJI 
	DYNAMIC RESULT SETS 0 
	PROGRAM TYPE SUB 
	PARAMETER STYLE JAVA; 

Use the following specifications for Java stored procedures:

LANGUAGE JAVA must always be specified for interpreted Java stored procedures.

EXTERNAL NAME contains the class and method name in between single quotes. If the stored procedure was packaged in a JAR file, then EXTERNAL NAME is the name of the jar file. For example, EXTERNAL NAME 'MyJAR: MyJSP.sample1'. If there are multiple methods in the class name, then a Java signature is required for the method name.

WLM ENVIRONMENT specifies the name of the WLM address space that was created in Step 4.

PROGRAM TYPE SUB should always be specified for a Java stored procedure.

PARAMETER STYLE JAVA should always be specified for a Java stored procedure.

After this DDL is executed, the stored procedure is now defined in DB2's catalog, specifically in the SYSIBM.SYSROUTINES table. To verify this, issue the following statement:

 
 SELECT * FROM SYSIBM.SYSROUTINES WHERE NAME = 'MyJSP'

Step 7: Write a calling program to invoke the stored procedure

Any calling program used to call any other stored procedure can be used to invoke a Java stored procedure, as long as the correct name and parameters are specified. Other alternatives for invoking the stored procedure is to use the IBM Stored Procedure Builder or to write a Java calling application. An example of a Java calling application is included in Downloads.

Step 8: Run the stored procedure

If the calling program is written in Java, it can be executed from Unix Systems Services (USS) on z/OS by issuing the Java command. For example, if the calling program is denoted by callmysp.java, then you can start the program using the command: java callmysp.

You can also use the IBM Stored Procedure Builder to invoke the procedure.

Step 9: Analyze the results

When the stored procedure completes execution, output is placed in the directory specified in the Java environment data set. Four output files are created in that directory, as described in Step 4. These files contain the results of println executions that can be used to verify the successful execution of the stored procedure and the expected results.

Example of a Java stored procedure

The sample Java stored procedure in Downloads is named JAVASPJ. JAVASPJ has one INOUT parameter of type CHAR(12). The stored procedure is found in a class javaspj, and its method is called jsp1. The stored procedure does not return any result sets. When invoked, the stored procedure issues a SELECT statement against an existing table and returns to the calling program.

Back to top

Invoking a Java stored procedure from a servlet

The steps to invoke a Java Stored Procedure from a servlet running on WebSphere Application Server on z/OS are:

1. Code the servlet and compile it.
   The SQL CALL statement in the servlet is the same as the SQL CALL statement in a stand-alone Java calling program. A servlet, however, extends the HttpServlet class and contains other needed methods. An example of a simple servlet that calls a Java stored procedure is included in Downloads. More information on servlets and servlet programming is found in [7].
2. Define the servlet to the WebSphere Application Server. More information on defining a servlet to WebSphere is in [8].
3. Run the servlet from a Web browser.
   From a browser window, invoke your servlet with the appropriate URL. Note that this URL is determined by your server IP address, and how you have defined the servlet to the application server. This URL will vary for each running servlet.

Back to top

Additional tips for writing and executing Java stored procedures

• When establishing a connection to the JDBC driver in the stored procedure, remember that the Java stored procedure is not establishing a new connection to the driver. Instead, it is using the existing connection that the calling application has established in order to connect to the data source. As a result, do not specify the URL of the data source when establishing the connection; instead use the url jdbc:default:connection.
• With JDK 1.3.1, the output files of the Java stored procedures are sent to a /tmp directory in Unix Systems Services. The output is no longer sent to the directory specified by WORK_DIR in the JAVAENV data set.
• When running JDBC on z/OS (both for Java stored procedures or regular Java programs that use JDBC to access the data source) first run the utility db2genJDBC -pgmname=yourprogramname to create the four JDBC DBRMs. When this utility is run, a .ser file is created, together with the four DBRMs. If DSNJDBC is specified as yourprogramname, then the four DBRMs created are called DSNJDBC1, DSNJDBC2, DSNJDBC3, and DSNJDBC4. If yourprogramname is something else, for example JDBC20, then four DBRMs will be created: JDBC201, JDBC202, JDBC203, and JDBC204. If you choose to use your own name for the DBRMs, then be sure to add the following to the JAVAENV dataset:

  "DB2SQLJPROPERTIES=/u/prod_SP/db2sqljjdbc.properties"

  
  where /u/prod_SP is to be replaced with the name of the directory in your system where the db2sqljjdbc.properties file with your settings is located. This will point the Java stored procedure to the correct db2sqljjdbc.properties file that you are using, and therefore will use the DBRMs that are specified in that file, and not the default DBRMs.
• In order to facilitate data type equivalence between Java and DB2 for z/OS, refer to Table 2. The types in bold are supported in DB2 for z/OS Version 7, but require the CREATE PROCEDURE statement to explicitly specify a Java method signature.
  
  Table 2. DB2-to-Java parameter type mapping
  

  DB2 Type	Java Type
  SMALLINT	short, java.lang.Integer
  INTEGER | INT	int, java.lang.Integer
  { DECIMAL | DEC } (N [,N] )	java.math.BigDecimal
  NUMERIC (N [,N])	java.math.BigDecimal
  FLOAT(N) (see REAL or DOUBLE)
  REAL	float, java.lang.Float
  DOUBLE [PRECISION]	double, java.lang.Double
  { CHARACTER | CHAR } (N)	java.lang.String
  { CHARACTER VARYING | VARCHAR } (N)	java.lang.String
  { CHARACTER | CHAR } (N) FOR BIT DATA	byte[]
  DATE	java.sql.Date
  TIME	java.sql.Time
  TIMESTAMP	java.sql.Timestamp

• If changes are made to the stored procedure source code after the original stored procedure has been invoked, not only does the stored procedure need to be recompiled, rebound, etc., but the WLM address space where the stored procedure runs must be refreshed. In addition, the WLM environment DT12WLJI must be refreshed with the following MVS command:

  v wlm,applenv=dt12wlji,refresh.

  
  

Back to top

Considerations for stored procedures and EJBs

EJBs [4] are portable, server side application components that run in an EJB server environment within a container. The container provides an application context for the component and, through the EJB server, several services such as transaction, security, naming and directory, communications, data access, load distribution, and persistence. This enables application developers to focus on business logic, as opposed to the component infrastructure, which is supplied by the EJB server and tooling.

There are two types of EJBs: session beans and entity beans. Session beans exist only during the lifetime of the session and represent work performed by a single client. They therefore contain information that will disappear after the user has closed the server connection. An example is the contents of an online shopping cart. Entity beans are persistent, exist for an extended period of time (until they are explicitly destroyed) and may be used by several clients. They contain persistent data that can be saved across multiple sessions in various data stores. Examples include credit card and billing information. Entity bean persistence is managed either by the bean itself (bean-managed persistence) or within the container (container-managed persistence). A step-by-step guide for creating and deploying EJBs in a WebSphere environment on the eServer zSeries is detailed in [5].

Session and entity beans execute business logic on the middle tier. Both can customize their access to data using JDBC connectivity. Using container-managed persistence, entity beans can map to DB2 tables that are defined at deployment time. For life cycle operations, data access code is automatically generated for entity beans with container-managed persistence.

Many DB2 servers contain stored procedures that can be leveraged by server applications and components through EJBs [9]. In fact, for stored procedures, there are tradeoffs between calling EJBs that in turn call stored procedures and directly encoding the business logic in the EJB. Some advantages include flexibility in where to locate the EJB server and performance. For example, if the business logic is encoded directly in the EJB, and the EJB server executes on the middle tier, each update requires access to the network. These updates require exclusive access to DB2 locks, and network delays may result in increased lock contention, deadlocks and lock timeouts. Encapsulating several updates in a stored procedure addresses the issue of network contention by minimizing the amount of network traffic used for updates.

Another advantage of using existing stored procedures in EJBs is that it increases productivity by minimizing redundant code while exploiting the use of existing business logic. This increases EJB productivity by leveraging common, existing business logic. It also enables the transformation of legacy applications to the EJB programming model. One approach is to wrap stored procedures as methods in stateless session beans, as shown in Figure 2. IBM offers a tool to assist developers in creating EJBs that wrap existing stored procedures for use in Web applications.

You can use Websphere Application Developer V5 to wrap stored procedure calls as methods in stateless session beans. The wizard can be launched from the Data perspective. In a WebSphere-DB2 configuration, EJBs execute with the aid of the EJB server on the middle tier, while stored procedures execute on the back end data server. This stored procedure execution reduces the communication between the middle tier and the database server, particularly if the stored procedure contains several SQL statements. This may also consume fewer resources compared to using container-managed entity beans.

EJBs that leverage existing stored procedures can exploit existing business logic that provides a common service for many applications. This may result in the inability to optimize the specific tasks implemented by the EJB. In spite of this limitation, exploiting stored procedures via EJBs has the advantage of reduced network traffic between the EJB server and the DBMS, improved EJB productivity and reduced software maintenance costs.

Back to top

Summary

The IBM Application Framework for e-Business is designed to enable businesses to quickly build, run and manage a successful e-business using open standards, multiple platforms, and multiple vendors. WebSphere Application Server and DB2 offer an attractive integrated infrastructure to enable customers to gain a competitive advantage in the marketplace. J2EE, including the use the of EJBs for component software development, provides a foundation for developing this infrastructure.

We presented a step-by-step guide to develop and execute Java stored procedures in a z/OS environment. We also described how EJBs can leverage Java stored procedures in this environment to exploit the business logic in legacy and newly developed applications. This offers the advantages of increased performance and development productivity, potentially reduced resource consumption, and improved software maintenance costs. More information on these issues is found in [3][4][9].

Back to top

Download

Name	Size	Download method
basicado.zip	7 KB	FTP | HTTP

Information about download methods

Resources

• [ 1] IBM e-business infrastructure, http://www.ibm.com/e-business/doc/content/event/infrastructure.html
  
  
• [ 2] Meet the Experts: John Campbell on Java Performance for DB2 Applications, http://www-106.ibm.com/developerworks/db2/library/techarticle/002campbell/0202campbell.html.
  
  
• [ 3] Java 2 Platform, Enterprise Edition, http://java.sun.com/j2ee.
  
  
• [ 4] Enterprise Java Beans 2.0 Specification, http://java.sun.com/products/ejb/2.0.html
  
  
• [ 5] IBM Redbook DB2 UDB for OS/390 and Continuous Availability. http://www.ibm.com/redbooks/abstracts/sg245486.html
  
  
• [ 6] DB2 UDB for OS/390 and z/OS Application Programming Guide and Reference for Java http://www-4.ibm.com/software/data/db2/os390/v7books.html
  
  
• [ 7] Hunter, Jason with William Crawford. Java Servlet Programming, 2nd Edition. O'Reilly & Associates, 2001
  
  
• [ 8] WebSphere Application Server for z/OS and OS/390, http://www.ibm.com/software/webservers/appserv/zos_os390/library.html
  
  
• [ 9] Saracco, C.M., "Leveraging DBMS Stored Procedures Through Enterprise JavaBeans," IBM Silicon Valley Laboratory White Paper/Technical Report, TR 03.723, August, 2000, http://www-4.ibm.com/software/data/pubs/papers/index.html#javabeans
  
  

About the authors

Rate this article

Comments

Back to top

Table of contents

• Introduction
• Writing Java stored procedures
• Invoking a Java stored procedure from a servlet
• Additional tips for writing and executing Java stored procedures
• Considerations for stored procedures and EJBs
• Summary
• Download
• Resources
• About the authors
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: Create an electronic form solution with DB2 pureXML and Lotus Forms
• Community: DB2
• Buy: DB2 Enterprise

Dig deeper into Information Management on developerWorks

Discover knowledge paths

Skill-building guides for Information Management, Linux, open source, cloud, Java, business analytics, and more.

Special offers