If your business is like most, your vital business data is stored in a relational database. Since that information is in a database, you can use SQL to analyze and manipulate it. And because your database is on a server system, you can use your workstation to access the data, using the client/server paradigm. At some point, however, you may discover that SQL can’t do everything. You then want to create programs to analyze your data, but you don’t want to tie those programs to any particular architecture. Most importantly, you want to be able to get at the information from your workstation.

One approach is to write a Java program that runs on your workstation and uses Java Database Connectivity (JDBC) or ODBC to access the data in your database. However, if that program accesses a large amount of data, the transfer of data from server to client may slow the program’s performance. This forces you to consider another option: putting your program on the server. But if it is a stand-alone program, you have to figure out how to get the results back to the workstation. Fortunately, most databases have a solution to this problem. That solution is stored procedures, which are programs that you can call using the SQL CALL statement. Using a stored procedure puts your code inside the database system.

When writing a stored procedure, a variety of languages can be used. However, if you are concerned with portability, you will probably end up using one of two languages: SQL or Java. If the program you are creating is fairly simple, you can write the function using SQL and then define an SQL stored procedure. However, there are times when the function is more complex, and writing it in SQL is either impossible or requires an SQL genius. In this case, you probably want to write in a procedural language. And, if you want something that is portable, you want to use Java.

Support for Java stored procedures was added to DB2 UDB for AS/400 in V4R5. This article presents examples of how stored procedures can be created, deployed, and used on the AS/400. Since Java is used as the implementation language, these stored procedures can also be used on other platforms.

Java Stored Procedures for DB2

When running a Java stored procedure, the database engine must create a Java Virtual Machine, or JVM, to run the Java program. On the AS/400, the database engine creates a

JVM that uses the latest version of Java installed on the system. Before the database can run a Java program, it must first be able to find the program. With the AS/400, all user- defined Java programs must be stored in the AS/400 Integrated File System (AS/400 IFS), in the /QIBM/UserData/OS400/SQLLib/Function directory.

Interfacing Between Java and the Database

When a stored procedure is called, the database must convert the type of the parameters from a database type to a type understood by the language being used. When the language used is Java, the database must determine which method should be called for the stored procedure. When writing stored procedures for Java on DB2 UDB, the parameter style determines how parameters are passed and how a Java method is structured. Two parameters styles are provided by DB2: DB2General and Java.

The DB2General parameter style was introduced with DB2 UDB, so it is available only on DB2 platforms. In this parameter style, a Java stored procedure is an instance method of a Java class. This class must extend the com.ibm.db. app.StoredProc class supplied by DB2. In this parameter style, parameters are passed (and converted to) Java types. These types are either a Java class or a Java primitive type. Figure 1 shows the mappings for some common SQL types. (The complete set of mappings can be found in the DB2 UDB for AS/400 SQL Reference manual.) The IN, INOUT, and OUT parameters are all passed to the Java method in the same manner. However, the Java method must explicitly set the output parameters, using Set methods provided by the com.ibm.db.app. StoredProc class.

If you are used to programming in Java or C++, using a Set method to set the return value may seem awkward. A more natural approach is provided by the Java parameter style. The Java parameter style is the parameter style specified by the SQLj: SQL Routines standard (latest draft is available at ftp://ftp.calweb.com/business/ sqlj/Pt1—99-
284.pdf). In this parameter style, a Java stored procedure is a “static” method. Like the DB2General parameter style, database types are converted to Java types. Unlike the DB2General parameter style, INOUT and OUT parameters are passed as one-element Java arrays. Setting the first element of the array sets an output parameter. Because the SQLj standard supports this parameter style, it is available on several database systems.

A Stored Procedure Example

Although SQL has some functions that provide statistical information, many functions are missing. This missing functionality may be found by using Java stored procedures. As an example, I will implement a stored procedure that calculates the geometric mean of the column of a table. My stored procedure example is designed to be flexible enough to work with a variety of numeric types as well as with data from a variety of tables. (Note that, although the example is mathematical, stored procedures are commonly used to optimize business database queries.) Any frequently used complex query should be optimized to a stored procedure. Using Java stored procedures to implement your complex business logic will permit you to run that logic on a variety of database platforms.

A geometric mean for n values is calculated by multiplying the values together and then taking the nth root of the product. In its simplest form, this algorithm is impractical because multiplying many values together soon produces a number that is so large that it cannot be represented by a 64-bit double. However, it is possible to iteratively calculate the mean.

Figure 2 (page 89) presents a Java method for calculating the geometric mean of a column of numbers. This method, called calculate, is a static method of the com.xxx.GeometricMean class. It is flexible in that it uses an SQL query to select the column from which the values are to be calculated. The method first executes the SQL query using a statement created from a JDBC connection. It then loops through each value

returned by the query and calculates the current geometric mean, using the previous geometric mean as input.

I then want to use this method within a stored procedure. Figure 3 (page 89) shows an example of a method used as a Java stored procedure that uses the DB2General parameter style. Because the DB2General parameter style is used, the GMeanDB2 class extends the com.ibm.db2.app.StoredProc class. The method, gmean, has two parameters. The first parameter, Answer, is an output parameter and is used to return (Java) double. The second parameter, query, is a String, which contains the SQL statement that selects the column of values. This procedure first uses the getConnection method of the StoredProc class to get the JDBC connection to the current database. It then passes this connection, as well as the query, to the GeometricMean.calculate method. This method returns a double, which is used by the Set method to return the value to the database.

Figure 4 (page 89) contains an example of a method to be used as Java stored procedure, which uses the Java parameter style. In this example, the GMeanJava class is merely a container. It does not extend another class. The method, gmean, is a static method. The output parameter is passed as a single-element array of double. As in the DB2General parameter style, the input parameter is passed as a String. This procedure obtains a connection to the database by using the URL jdbc.default.connection, as specified by the SQLj standard. It passes the connection and the query to GeometricMean.calculate, which returns the value. The return value is then returned to the database by setting it as the value of the first element in the array.

Compiling and Placing Code for Java Stored Procedures

Once the stored procedures are written, they need to be compiled to Java byte code. Then they need to placed on the system on which they will be run, in a location where the database can access them.

Since a class containing a Java stored procedure that uses the DB2General parameter style extends the StoredProc class, the StoredProc class must be available when the stored procedure class is compiled. On the AS/400, the StoredProc class resides in the db2routines_classes.jar file, which is found in the /QIBM/ProdData /Java400/ext directory. To compile my Java program, I first copied the db2routines_classes.jar file to my Microsoft Windows NT workstation. I then added the JAR file to my CLASSPATH and compiled using the following commands: Compiling the class for the Java parameter style, I just use the Java compiler: Then these files must be placed on the AS/400. Since they are part of the com.xxx packages, the class files must be copied to the /QIBM/UserData/OS400/SQLLib/Function/ com/xxx directory. In my case, I used FTP (in binary mode) to move the files to the AS/400.

Registering the Stored Procedure with the Database

Before you are able to use a Java method as a stored procedure, you must create a stored procedure. The Create Procedure SQL statement creates a stored procedure and allows you to associate that stored procedure with an external program. When creating a Java stored procedure, there are three phrases that must be used. First, a Language Java phrase must be used to indicate Java. Then a parameter style phrase must be used. This phrase is either parameter style Java or parameter style DB2General. Finally, the external name phrase is

set CLASSPATH=db2routines_classes.jar;%CLASSPATH%
javac comxxxGMeanDB2.java

javac comxxxGMeanJava.java

used to specify the Java method to be called. The external name phrase includes the full name of the class, followed by the method name. Figure 5 (page 89) shows the SQL statements used to create stored procedures that use both parameter style Java and parameter style DB2General.

Running the Stored Procedure

Now that the stored procedure is created, how do you run it? Given that the stored procedure calculates a mean, it needs to have some data to operate on. Using the SQL statements in Figure 6 (page 89), I create the table numbers and populate the table with data. You may notice that integer values are stored in the table. Because the stored procedure uses JDBC to read the data, the JDBC call getDouble(), within the stored procedure, automatically converts the SQL INTEGER into a Java double.

One way to run the stored procedure is to create a client program that calls the stored procedure and obtains the return value. A JDBC client program to call the stored procedure is shown in Figure 7. This program uses the AS/400 Toolbox for Java JDBC driver to connect to the AS/400. You execute Client by qualifying the IP address (or host name), profile, password, and the name of the file, as follows: The output of the program is shown below.

For more on how to debug your Java stored procedures, read the Web sidebar to this article, “Debugging Tips for Java Stored Procedures.”

The use of Java stored procedures permits Java code to run inside your database. This is especially useful if you want to write business logic that is portable to a wide range of database systems. This article has only briefly covered Java stored procedures and their use on the AS/400. More information about Java stored procedures is available in the AS/400 DB2 manuals and in the Redbook DB2 UDB for AS/400 Advanced Database Functions.

REFERENCES AND RELATED MATERIALS

• DB2 UDB for AS/400 Advanced Database Functions, Redbook (SG24-4249-02)

• DB2 UDB for AS/400 SQL Programming Concepts: http://publib.boulder.ibm.com/pubs/html/as400/v4r5/ic2924/info/db2/rbafymst02.htm

• DB2 UDB for AS/400 SQL Reference: http://publib.boulder.ibm.com/pubs/html/ as400/v4r5/ic2924/info/db2/rbafzmst02.htm

• SQLj.org Web site: www.sqlj.org

java Client hostName profile password numbers

The answer using gmeandb is 3.936283427035352
The answer using gmeanjava is 3.936283427035352

Debugging Tips for Java Stored Procedures

One of the difficulties associated with using Java stored procedures is the problem of debugging a stored procedure when something goes wrong. There are two different options I use when debugging a stored procedure: manual tracing and the AS/400 debugger.

One way to debug a Java stored procedure is to manually add tracing code to your stored procedure. Within a stored procedure, you can open a file and then write trace information to that file using typical Java file output methods. One thing to remember is that, within a Java stored procedure, you cannot write to System.out or Syster.err. Doing so may cause the job to fail with an MCH3402 exception.

Another option for debugging a stored procedure is to use the AS/400 debugger. To do this, you first need to compile your code in debuggable mode using the -g option. Second, you need to move your Java source file, as well as your Java class file, to the /QIBM/UserData/OS400/SQLLib/Function directory on the AS/400. Third, you need to figure out what job on the AS/400 is running the stored procedure. If you are using the toolbox JDBC driver or Operations Navigator, the job will be a QZDASOINIT job in the QSERVER subsystem. A tedious way to do this is to look at all the QZDASOINIT jobs on the system and find the job with the user profile used to connect to the system.

A simpler way to determine the job is to write a small CL program that is called each time someone connects to a QZDASOINIT job. Figure A shows a small CL program that sends a message to my message queue (EBERHARD) whenever I connect to the AS/400. The message indicates which QZDASOINIT job is being used. This program is registered as an exit program using the following command:

ADDEXITPGM EXITPNT(QIBM_QZDA_INIT) FORMAT(ZDAI0100) PGMNBR(1) PGM(ZDAEXIT)

The exit program can later be removed using the following command:

RMVEXITPGM EXITPNT(QIBM_QZDA_INIT) FORMAT(ZDAI0100) PGMNBR(1)

Once the job is found, I start a service job on the AS/400 using the Start Server Job (STRSRVJOB) command. After that, I can start the debugger using the Start Debugger (STRDBG) command. Before I can add a Java program to the debugger, the JVM must be started. I usually do this by letting my Java stored procedure run once. Once it has run, I am able to add my Java program to the debugger using the Display Module Source (DSPMODSRC) command. From there, I can debug the program like any other AS/400 Java program.

—John Eberhard

PGM PARM(&OKAY &REQUEST)

dcl &Okay *char 1

dcl &Request *char 34

dcl &User *char 10

dcl &JOB *CHAR 10

dcl &JOBUSER *CHAR 10

dcl &JOBNBR *CHAR 6

chgvar &User %sst(&Request 1 10)

chgvar &Okay ‘1’

IF COND(&USER *NE ‘EBERHARD ‘) THEN(GOTO +

CMDLBL(NOCHG))

SNDPGMMSG MSG(‘SENDING MESSAGE’)

RTVJOBA JOB(&JOB) USER(&JOBUSER) NBR(&JOBNBR)

SNDMSG MSG(‘THE JOB IS ‘ *CAT &JOBNBR *CAT ‘/’ *CAT +

&JOBUSER *CAT ‘/’ *CAT &JOB) TOUSR(&USER)

NOCHG: ENDPGM

Figure A: This CL program notifies a user that a connection has been accepted.

SQL Data Type Java Data Type

INTEGER int DOUBLE float CHARACTER String DECIMAL BigDecimal

Figure 1: SQL datatypes have a corresponding Java class or primitive type.

package com.xxx;

import java.sql.*;

public class GeometricMean {

public static double calculate(Connection con, String query)

throws java.sql.SQLException {

double answer= 1.0;

double count = 0.0;

Statement stmt = con.createStatement();

ResultSet rs = stmt.executeQuery (query);

while (rs.next()) {
double value = rs.getDouble(1);
if (value != 0.0) {

count += 1.0;

answer=Math.pow(answer,(count-1)/count)*

Math.pow(value,1.0/count); }

}

rs.close();

stmt.close();

return answer;

}

}

Figure 2: This Java program calculates the geometric mean of a table’s column.

package com.xxx;
import java.sql.*;
import com.ibm.db2.app.StoredProc;
// Example of parameter style db2general stored procedure
public class GMeanDB2 extends StoredProc {

public void gmean(double answer, String query) throws Exception {

Connection con = getConnection();

set(1,GeometricMean.calculate(con, query));

}

public void gmean2(String resultTable, String query) throws Exception {

Connection con = getConnection();

double answer = GeometricMean.calculate(con, query);

Statement stmt = con.createStatement ();

stmt.executeUpdate("create table "+resultTable+"(mean double)");

stmt.executeUpdate("insert into "+resultTable+" values("+answer+")");

stmt.close();

}

}

Figure 3: A Java stored procedure can use the DB2General parameter style.

package com.xxx;
import java.sql.*;
// Example of parameter style java stored procedure
public class GMeanJava { public static void gmean(double[] answer, String query) throws Exception {

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

answer[0]=GeometricMean.calculate(con, query);

}

public static void gmean2(String resultTable, String query) throws Exception {

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

double answer = GeometricMean.calculate(con, query);

Statement stmt = con.createStatement ();

stmt.executeUpdate(“create table “+resultTable+”(mean double)”);

stmt.executeUpdate(“insert into “+resultTable+” values(“+answer+”)”);

stmt.close();

}

}

Figure 4: A Java stored procedure can use Java parameter style.

create procedure gmeanjava(inout double, in VARCHAR(30))

language java parameter style java

external name 'com.xxx.GMeanJava.gmean';

create procedure gmeanjava2(in VARCHAR(30), in VARCHAR(30))

language java parameter style java

external name 'com.xxx.GMeanJava.gmean2';

create procedure gmeandb(inout double, in VARCHAR(30))

language java parameter style db2general

external name 'com.xxx.GMeanDB2.gmean';

create procedure gmeandb2(in VARCHAR(30), in VARCHAR(30))

language java parameter style db2general

external name 'com.xxx.GMeanDB2.gmean2';

Figure 5: The SQL Create Procedure statements can qualify a Java class.

create table numbers (number integer)
insert into numbers values(1)
insert into numbers values(3)
insert into numbers values(5)
insert into numbers values(7)
insert into numbers values(9)

Figure 6: SQL insert statements can be used to populate a table.

import java.sql.*;

public class Client {

public static void main(String args[]) throws Exception {

Class.forName(“com.ibm.as400.access.AS400JDBCDriver”);
String url = “jdbc:as400://”+args[0];
Connection con = DriverManager.getConnection (url, args[1], args[2]);
CallableStatement cstmt;

cstmt = con.prepareCall(“call gmeandb(?, ?)”);

cstmt.setDouble(1, 0.0);

cstmt.setString(2, args[3]);

cstmt.registerOutParameter(1,java.sql.Types.DOUBLE);

cstmt.execute();

System.out.println(“The answer using gmeandb is “+cstmt.getDouble(1));

cstmt = con.prepareCall(“call gmeanjava(?, ?)”);

cstmt.setDouble(1, 0.0);

cstmt.setString(2, args[3]);

cstmt.registerOutParameter(1,java.sql.Types.DOUBLE);

cstmt.execute();

System.out.println(“The answer using gmeanjava is “+cstmt.getDouble(1));

}

}

Figure 7: JDBC clients can easily invoke a stored procedure.