As you may have read in my article entitled “Access DB2/400 Data with LS:DO” (MC, November 2000), there are many ways to access DB2/400 data from the Lotus Notes/Domino environment. The option I wrote about in my article was how to access AS/400 data using a Domino agent written in LotusScript Data Object (LS:DO).

In this article, I discuss how to access AS/400 data using an agent written in Java. The primary data access mechanism I use in this article is Java Database Connectivity (JDBC), the Java equivalent to ODBC.

Some History on Domino and Java

Java support was first added to Domino in Release 4.6. The Java Domino classes were created by putting Java “wrappers” around the LotusScript Extension (LSX) architecture. Java Domino classes have functions similar to many of the LotusScript Domino back-end objects. In fact, it is only the back-end objects—such as databases, documents, forms, agents, or views—that are accessible with Java. The Notes user interface objects or the front-end objects (such as Notes UIDocument and NotesUIView) are not.

In Domino Release 4.6, the Domino Java library is provided via the notes.jar file. The notes.jar file contains lotus.notes classes. You can use these classes to access Domino databases, views, documents, and other back-end objects. On the AS/400, the notes.jar file can be found in the /QIBM/ProdData/lotus/notes directory.

With Domino Release 5.0, the Domino Java classes were expanded to include Common Object Request Broker Architecture (CORBA) and a base set of Domino applets. CORBA allows Java programs to access remote Domino objects on a Domino server. The Domino Java library is provided with the following packages:

• The notes.jar file contains the lotus.domino package, which is the new package for Release 5.0, and the lotus.notes package, which provides backward compatibility with applications written to Release 4.6.

• The ncso.jar file contains the complete set of classes to access a Domino server remotely via Internet Inter-ORB Protocol (IIOP). This jar file contains the lotus.domino and lotus.domino.corba packages.

In Release 5.0, the notes.jar file is still located in the /QIBM/ProdData/Lotus/Notes directory, and the ncso.jar file is located in the /QIBM/ProdData/Lotus/Notes/Shared directory. You will also notice that there is a ncso.jar file located in the /domino/java subdirectory of your AS/400 Domino server. If you configured your Domino server on the AS/400 with a directory of /domsvr1/domino/data, the ncso.jar file will be located in the /domsvr1/domino/data/domino/java directory; this ncso.jar file is actually a symbolic link to the ncso.jar file located in the /QIBM/ProdData/Lotus/Notes/Shared directory.

Java on the AS/400

The AS/400 has some unique features for implementing Java. First, the AS/400 has an integrated Java Virtual Machine (JVM). This JVM is written to the Sun Microsystems specification, so it functions the same as ported JVMs on other platforms. The beauty of this implementation is its performance. Most of the Java code on the AS/400 is located below the Technology Independent Machine Interface (TIMI), which translates directly into improved performance.

As a result of this integration of the JVM into OS/400, the AS/400 has something called a Java transformer to enhance performance. The Java transformer optimizes platform-independent Java class files, allowing them to take advantage of the AS/400’s processing architecture and avoiding Java programs from needing to be interpretered during execution. Transforming a Java program is like compiling an RPG or COBOL program. The transformer is the second unique feature of how Java has been implemented on the AS/400. The transformer is part of the AS/400’s JVM. To optimize the performance of your Java applications, you can use the Create Java Program (CRTJVAPGM) command to manually create Java programs for individual class files or for entire Java class archive files
(.jar or .zip). (The recommended optimization level is 30.) It is very important that all of the Java programs you use regularly are associated with a Java program. This is especially important for the archive files that support your applications. Make sure the notes.jar, jt400.zip (AS/400 Toolbox for Java), and ncso.jar files are associated with a Java program. The jt400 and ncso .jar files do not ship with the Java program, so it is imperative that you create a Java program for these archive files. I will address usage of the AS/400 Toolbox later in the article.

You can use the Display Java Program (DSPJVAPGM) command to determine whether or not a particular class or archive file is associated with a Java program:

DSPJVAPGM CLSF(‘/QIBM/ProdData/lotus/notes/notes.jar’)
DSPJVAPGM CLSF(‘/QIBM/ProdData/lotus/notes/shared/ncso.jar’)
DSPJVAPGM +
CLSF(‘/QIBM/ProdData/http/public/jt400/lib/jt400.jar’)

If no Java program is associated with a class or archive file, you will receive a message stating, “No Java program associated with the class file.” If you receive this message, use the CRTJVAPGM command to create a Java program (again, optimization 30 is recommended):

CRTJVAPGM CLSF ('/QIBM/ProdData/lotus/notes/notes.jar’) OPTIMIZE(30)
CRTJVAPGM CLSF(‘/QIBM/ProdData/lotus/notes/shared/ncso.jar’) OPTIMIZE(30)
CRTJVAPGM CLSF(‘/QIBM/ProdData/http/public/jt400/lib/jt400.jar’) OPTIMIZE(30)

The third unique feature is that the AS/400 can support multiple Java Development Kits (JDKs) on the same system. JDKs are implemented as options under the 5769-JV1 product. The following JDKs are supported within the JV1 product:

• Option 1—JDK 1.1.6
• Option 2—JDK 1.1.7

• Option 3—JDK 1.2
• Option 4—JDK 1.1.8
• Option 5—JDK 1.3

As additional JDKs are supported on the AS/400, a new option is added for the JV1 product. Domino supports Java programs written in Java Development Kit 1.1.x.

Java Agents

Java agents are nothing more than agents written to extend the Lotus-supplied AgentBase class. They have full support for all agent scheduling, selection, and security features. Java agents must also include a public void NotesMain() method as an entry point into executable code. This is where Java agent processing begins.

The options for developing a Java agent vary according to the release of Domino Designer you are using. I’ll address development in Release 4.6 first. With the Release 4.6 client, you must use a third-party Java Integrated Development Environment (IDE). That IDE may be something as simple as Notepad or as sophisticated as IBM’s VisualAge for Java. Java agent code is written in the IDE and then compiled using the javac utility, javac myjavaprogram.java. The next step is to import the compiled code, now in the form of a class file, into the agent. This is done by selecting the Imported Java button, found under the Untitled (Agent): Action to run in the Domino Designer IDE. Next, press the Import Class Files... button. Then, select the class file you just created with the javac utility.

In Release 5.0 of Domino, the agent can be created via this same mechanism. Instead of selecting the Imported Java button, simply click the pull down under Agent: Action in the Domino Designer IDE and select Imported Java (see Figure 1).

In Domino Release 5.0, you can code your Java agent in the Domino Designer IDE. Simply select Java from the drop-down list for the run option under Untitled (Agent): Action, and the correct class will automatically be extended for you in addition to some variable initialization. You can code, compile, and debug your Java agent in the Domino Designer, which makes life a whole lot easier (see Figure 2). Development of your Java agent in the Domino Designer IDE allows you to use Java libraries (the equivalent of LotusScript shared libraries).

AS/400 Integration

Agents can be written to manipulate either Domino objects, AS/400 objects, or both. Chances are that you will want your agent to do both. After all, an AS/400 contains many gigabytes of mission-critical data that you will want to access. There are a few tricks to getting this to work. First, you will want to make sure your AS/400 Domino server has access to either the notes.jar file or, if you are using CORBA in your applications, the ncso.jar file. This is accomplished by adding the JavaUserClasses variable to the notes initialization (notes.ini) file for your AS/400 Domino server:

JavaUserClasses=/QIBM/ProdData/Lotus/Notes/Notes.jar

Remember that you need to stop and restart your Domino server for the notes.ini change to take effect.

The next step is getting access to that critical data stored in DB2/400. This is accomplished by using one of the JDBC drivers supported on the AS/400. You can use either the native JDBC driver, supplied with the 5769-JV1 product, or the AS/400 Toolbox JDBC driver, supplied with the 5769-JC1 product. Which JDBC driver you choose depends on where the code will execute. If your agent will always execute on the AS/400 Domino server, you should use the native JDBC driver. If, however, your agent may run on another Domino server (such as Windows NT or UNIX) or run on a client and access an AS/400, you will want to choose the AS/400 Toolbox JDBC driver.

If you choose to use the AS/400 Toolbox JDBC driver, you need to update the notes.ini variable JavaUserClasses to point to the jt400.jar file in the /QIBM/ProdData/http/public/jt400/lib directory.

About the JDBC Architecture

JDBC is an API that defines the Java classes to represent database connections, SQL statements, result sets, and database metadata. JDBC allows a Java programmer to issue SQL statements and to process the results of those statements. JDBC is the primary API for database access in Java.

As I have stated, there are two JDBC drivers available on the AS/400. The AS/400 Developer Kit for Java, the native DB2 JDBC driver (server), is the OS/400 implementation of Sun’s Java Development Kit and includes most of the utilities and functions found therein. The native JDBC driver calls into the native DB2 support on the AS/400 system; it can be found in the AS/400 Integrated File System (AS/400 IFS) at: /QIBM/ProdData/Java400/ext/ db2_classes.jar. The AS/400 Toolbox JDBC driver (server and client) is found in the AS/400 IFS at /QIBM/ProdData/http/public/jt400/lib/ jt400.zip (or jt400.jar).

This path would need to be added to the JavaUserClasses variable of the notes.ini file for each Domino server.

Client Setup

When developing your Java agent on a development client such as Domino Designer, you will need access to either the native JDBC driver classes or the AS/400 Toolbox JDBC driver classes in order to get your code to compile correctly. This can be accomplished by FTPing either the db2_classes.jar file or the jt400.zip file to your client. Simply use FTP or Client Access to drag and drop the appropriate .zip file into the directory structure of your development client. The other necessary step is to edit the notes.ini file of your development client to tell JavaUserClasses where to find the JDBC driver that you wish to import into your Java agent.

The following example assumes that you have placed the native JDBC driver .jar file (db2_classes.jar) and the AS/400 Toolbox JDBC driver .jar file (jt400.zip) into the /lotus/notes/java directory on your C: drive:

JavaUserClasses=c:/lotus/notes/java/db2_classes.jar;c:/lotus/notes/java/jt400.zip

This setup on the client is required even if you are developing your agent in a Notes database that resides on your AS/400 server. Domino Designer’s compiler will look on your local system for all of the .jar and .zip files it needs to import.

A Few Words on Security

When creating an agent to be used mainly for Web-based applications (meaning that the server will be accessed from a Web browser using the HTTP server module, which itself can trigger agents), you have the option of declaring for each agent whether it will run with the signer’s identity or with the Web user’s identity. Select the option you want in the AgentDesign properties box. Go to the Agent view in your database, right-click on the Agent, then select Agent Properties. To run with the identity of the Web user, select the Run Agent as Web user check box, as shown in Figure 3.

Now that you have a good understanding of how to set up the environment, take a look at my code example. In this example, I use the same customer form featured in my November article. The customer form in Figure 4 takes a customer number as input and retrieves the remaining fields when the Retrieve Customer Data action is pressed; this invokes a Java

The Code

agent that connects to DB2/400 and retrieves the record associated with the customer number entered in the form. The retrieved record will then be inserted into the correct fields of the form displayed to the user.

When designing a Java agent, the first thing you must do is load the classes that will be used in the code. This is accomplished by importing the packages that support the required Java classes, as illustrated in Figure 5, Section A (page 74).

You need to import the lotus.domino package to access back-end Domino classes, such as databases and documents. Java.lang is the basic Java package that contains the classes that deal with numerics, strings, objects, runtime, security, and threads. The java.sql package contains the classes and interfaces that access databases and send SQL statements.

The code in Figure 5, Section B is provided when you create a Java agent in the Domino Designer IDE. The correct class, AgentBase, is extended, giving the agent the appropriate inheritance characteristics. Also note that the class that contains the agent code must be public and the entry point to the functional code must be public void NotesMain().

An agent program must extend the lotus.domino.AgentBase class; this is the main class for the agent. AgentBase extends lotus.domino.NotesThread, which allows your agent class to be set up and initialized for Notes. AgentBase creates a session instance and preloads it with an AgentContext instance that is set up for the current agent. The session class is the root of the Domino object containment hierarchy. AgentContext is where all of the information about the agent’s location and environment is accessed. There are various agentContext properties you can access, such as getCurrentAgent, getCurrentDatabase, and getDocumentContext.

Now that you have the agentContext, you can use the getDocumentContext() method (see Figure 5, Section C) to get the current document. You can use this method because your agent is run within a document context. If it were not, you would use the unique ID (UNID) or the Notes ID (NoteID) of a document.

The document context is commonly invoked for Web access by WebQueryOpen and WebQuerySave formulas or by form actions. In a document context, an agent is passed a specific Notes document, upon which it should act.

The first task you need to perform after getting the current document is to get the customer number that has been supplied on the form. To prepare for reading the customer number, you create a string variable, Cusnum, to hold the result. Next, make sure the current document is not null, and read the customer number that has been supplied in the Customer number field of the form. The getItemValueString() method allows you to retrieve this value.

At this point, you are ready to register the native JDBC driver and establish a connection to the AS/400 that contains your DB2/400 database. At Figure 5, Section D, you can see I have created some variables that can be passed into the connection object:

• Server—This variable is the relational database directory entry for the AS/400, which can be found by issuing the CL command WRKRDBDIRE.

• User—This variable is a valid AS/400 user ID.

• Password—This variable is the password for the AS/400 user ID used in the connection.

Before you can use JDBC, you must register the driver of your choice. The DriverManager class is the management layer of JDBC, working between the user and the drivers. It keeps track of the drivers that are available and establishes a connection between a database and the appropriate driver. Once the driver classes have been loaded and registered with the DriverManager class, they are available for establishing a connection with a database. This is accomplished by calling the getConnection() method on the DriverManager class. The getConnection() method takes three parameters: url, userid, and

password. The url parameter specifies that the native JDBC driver (jdbc:db2:) should be used for this connection. Use jdbc:as400: as the URL parameter to specify the AS/400 Toolbox JDBC driver. The userid and password parameters must be a valid AS/400 user ID and password.

Now that you have a connection object, the next step is to create a statement object. The statement object allows you to execute SQL commands.

The SQL command you need to execute is a SELECT statement. This SELECT statement is going to return all of the fields from the table QCUSTCDT in collection QIWS, where the customer number (Cusnum) parameter equals the customer number that was entered on the customer form. The query is executed by running the executeQuery() method on the statement object you created, as illustrated in Figure 5, Section E.

Now you are ready to retrieve the result set that is passed back from the execution of the SELECT statement. As shown in Figure 5, Section F, this is accomplished by calling the next() method on your result set (rs).

To get the fields returned in your result set into the appropriate fields on the form, use the replaceItemValue() method on the current document (doc) object. The methods for retrieving the values in your result set are getString() and getInt(). The string values can be inserted directly into the fields on the form. It’s not quite so easy returning the Zip Code field, which is a type zoned decimal in DB2/400. To retrieve the value of this field, use the getInt() method. An integer in Java is a primary data type and therefore cannot be cast to a String object. To accomplish conversion of a primary integer data type into a String, a few steps are required. First, the Int primary data type needs to be converted into an Integer object. The Integer class provides an object wrapper around the int primitive data type. Now that you have an object, you can call the toString() method on this object to convert it to a String.

The last step, as shown in Figure 5, Section G, involves closing out your JDBC resources: the statement (stmt), result set (rs), and connection (dbConnect). I recommend that you close out these resources so they can be reused by the system, which improves performance and makes applications easier to debug. This is also imperative where object pooling is used, as resources that are not closed are never freed for reuse.

The agent is completed by closing the try/catch block that is used for error-handling in Java. See Figure 5, Section H.

Useful References

Hopefully, this article has shown you the versatility and power of using JDBC to access data in your agents. To enhance the performance of your agents, I recommend that you use prepared statements. Prepared statements improve performance, as they are SQL statements that have been compiled for efficiency. The SQL processor parses and analyzes the query, decides which indexes to use to fetch results, and produces an execution plan for the prepared statement. Prepared statements can provide quite a performance boost over dynamic SQL statements.

To further your knowledge of this topic there are many references available. The Lotus Developer Network Web site (www.lotus.com/developer) has a Lotus Domino Toolkit for Java/CORBA available for download that contains a whole host of documentation and sample agents, servlets, and applications. To find the toolkit download, just click on Technologies and Products (on the left panel), and you will find the toolkit under the Recent releases section. JDBC has its own learning curve, but don’t fret: There are plenty of references available to help you with this architecture. One such resource is the AS/400 Native JDBC driver Web page (www.as400.ibm.com/developer/jdbc ), where you will find a number of sample programs provided by Richard Dettinger. Richard works on the AS/400 Java Data Access team. For information on the AS/400 Toolbox JDBC driver, refer to IBM’s AS/400 Toolbox for Java Web site (www.as400.ibm.com/toolbox). You can also visit Sun’s JDBC Data Access API page (http://java.sun.com/products/jdbc).

And the JDBC APIs are detailed on the Java 2 Platform, Standard Edition, v 1.3 API Specification Web page (http://java.sun.com/j2se/1.3/docs/api/index.html).

I wish you the best of luck as you endeavor to use agents in the Java programming language to access DB2 relational databases.

REFERENCES AND RELATED MATERIALS

• AS/400 Native JDBC driver Web page: www.as400.ibm.com/developer/jdbc

• IBM’s AS/400 Toolbox for Java Web site: www.as400.ibm.com/toolbox

• Java 2 Platform, Standard Edition, v 1.3, API Specification Web page: http://java.sun.com/j2se/1.3/docs/api/index.html

• Lotus Developer Network Web site: www.lotus.com/developer

• Sun’s JDBC Data Access API page: http://java.sun.com/products/jdbc

Figure 1: Select Imported Java under the Action pull down in the Domino Designer IDE to import compiled Java code (.class files) into an agent—required in Release 4.6.

Figure 2: Use Domino Designer’s IDE to code, compile, and debug your Java agents in Release 5.0.

Figure 3: To have your agent run with the Web user’s identity, select Run Agent as Web user in the Agent properties dialog box.

Figure 4: The customer enters his or her customer number, presses the Retrieve Customer Data action, and the Java agent retrieves the remaining fields from DB2/400 using JDBC.

import lotus.domino.*;
import java.sql.*;
import java.lang.*;

public class JavaAgent extends AgentBase {

public void NotesMain() {

try {

Session session = getSession();
AgentContext agentContext = session.getAgentContext();

Document doc = agentContext.getDocumentContext( );

// Variable to receive customer number entered on form
String Cusnum=null;

// Read customer number from form
if (doc != null) {

Cusnum = doc.getItemValueString(“CustomerNumber”);
}

// Declare variables for system name, userid, password
String server = “MC170B”;

String user = “KIMGREENE”;
String password = “KIMPASSWORD”;

// Connect to AS/400
DriverManager.registerDriver(new com.ibm.db2.jdbc.app.DB2Driver());
Connection dbConnect = DriverManager.getConnection(“jdbc:db2://” +
server, user, password);
Statement stmt = dbConnect.createStatement();

// Execute Query
ResultSet rs = stmt.executeQuery(“SELECT * FROM QIWS.QCUSTCDT WHERE Cusnum =” +

Cusnum);

// Read the result set returned
rs.next();

doc.replaceItemValue(“LastName”, rs.getString(2));
doc.replaceItemValue(“Initials”, rs.getString(3));
doc.replaceItemValue(“Street”, rs.getString(4));
doc.replaceItemValue(“City”, rs.getString(5));
doc.replaceItemValue(“State”, rs.getString(6));
Integer zipcode = new Integer(rs.getInt(7));
doc.replaceItemValue(“ZipCode”, zipcode.toString());

// Close the statement
stmt.close();

// Close the result set
rs.close();

// Close the connection
dbConnect.close();

} catch(Exception e) {
e.printStackTrace();
}

}

A

B

C

D

E

F

G

H

Figure 5: This is the complete code for the sample Java agent that retrieves DB2/400 data to populate the Notes Customer form.