Java Stored Procedures

You can write DB2 Java stored procedures in both JDBC and in SQLj, although we recommend the latter for better performance. There are also two ways to pass parameters to stored procedures, which you declare when cataloging your stored procedure with a CREATE PROCEDURE statement. The PARAMETER STYLE DB2GENERAL requires you to extend a class called COM.ibm.db2.app.StoredProc, and pass IN, IN/OUT, and OUT arguments in the specified order. One of the drawbacks to this method is that it is more proprietary, and also requires you to instantiate a separate class of the stored procedure with every invocation.

DB2 UDB V6.1 introduced Java stored procedures that use the SQLJ Routines standard, which are cataloged using PARAMETER STYLE JAVA. We use this style in all of our examples because it is the recommended method and provides several advantages:

• All stored procedure methods are public static, thus not requiring a new object to be instantiated each time the stored procedure is invoked.

• IN/OUT and OUT parameters are passed as single element arrays, thereby passing them by reference instead of by value to retrieve them again.

• All result sets are returned as an array parameter of JDBC ResultSet objects.

Let us consider a stored procedure called getPurchaseStatistics() that returns a result set containing information about all of the purchases made between a specific start date and end date. These dates are specified as IN parameters to the stored procedure. In addition, the stored procedure returns a single output parameter, with the PRODUCT_ID of the most purchased product. This procedure would have the following method signature:

public static void getPurchaseStatistics
                   (java.sql.Date start,
                    java.sql.Date end,
                    int [] prodID,
                    ResultSet [] rs);

We won't worry about the underlying implementation, which could be in either JDBC or in SQLj. The main point to consider is how to obtain access to the Connection or ConnectionContext from the calling program. The calling program could be written using any supported DB2 development language and interface. If using JDBC, use the following syntax:

Connection con =
DriverManager.getConnection("jdbc:default:connection");

Notice that a special URL is used in this case to obtain the connection. After you have the Connection object, you can proceed as you would in any other JDBC application, except for the inherent restrictions of stored procedures.

In SQLj stored procedures, you don't ever need to refer to the ConnectionContext. As you may recall, if our SQLj statements do not specify a context, then the DefaultContext property is used. In an SQLj stored procedure, the DefaultContext property is the context of the calling program.

As discussed in Chapter 3, in DB2 v8, you can COMMIT or ROLLBACK a UOW within a stored procedure. This only applies to stored procedures that are not called within a DUOW being managed by an XA-compliant transaction manager. Other restrictions and allowances of stored procedures in general also apply to Java stored procedures.