Executing DB2 Stored Procedure on AS/400

Effective SOA solutions take advantage of all the services available across the systems, which include the legacy systems in your organization. There are several ways you can integrate the existing services running on your legacy systems. This series of posts is about using the AS/400 services directly in your Java applications. I will be covering how to call RPG programs, AS/400 Commands and AS/400 DataQueues in your Java applications.

Fortunately IBM has provided a very nice easy to use library for communicating with the AS/400 server from Java. The IBM Toolbox for Java is a library of Java classes that give Java programs easy access to IBM iSeries data and resources. JT Open is the open source version of Toolbox for Java. You can go to JT Open link to download the full set of java libraries and some more details of how that can be used to easily communicate with the AS/400 server. There are several ways you can access the services on AS/400 server, most common are as follows.

• Executing a DB2 Stored Procedure on AS/400.
• Executing the AS/400 RPG programs from Java.
• Sending a message via AS/400 DataQueues to communicate with the Server.
• Using CommandCall class of JT Open library to execute the AS/400 Commands.

All of these different methods of accessing the AS/400 services and have their own pros and cons. JT Open is a very powerful library and provides very easy to use APIs to communicate with the AS/400 services. These posts are about exploiting the JT Open libraries to use the AS/400 services. I am splitting these different approaches to separate posts. Click on the topic above to see the relevant post for that topic.

• Executing DB2 Stored Procedures.

Calling StoredProcedures created in DB2 running on iSeries AS/400 is no different to calling a StoredProcedure created in SQL Server or Oracle running on Windows or Unix. The same JDBC interfaces that you use for any other databases are used to call DB2 StoredProcedures. For this exercise we created a very simple SQL StoredProcedure in DB2 that takes the Customer Code as a parameter and will return all Open Orders for that customer from our DB2 database. The StoredProcedure name is CUSTORDOP and it is created in MYLIB library.

package as400;

import com.ibm.as400.access.AS400;
import com.ibm.as400.access.AS400JDBCCallableStatement;
import com.ibm.as400.access.AS400JDBCConnection;
import com.ibm.as400.access.AS400JDBCDriver;
import com.ibm.as400.access.AS400JDBCResultSet;

public class AS400DBTest {

 public static void main(String av[]){

  String server="yourserver.company.com";
  String user = "AS400USER";
  String pass = "AS400PWRD";

  AS400 as400 = null;
  AS400JDBCDriver driver = null;
  AS400JDBCConnection con = null;
  AS400JDBCCallableStatement stm = null;
  AS400JDBCResultSet rs = null;
  
  String sp = "CALL MYLIB.CUSTORDOP(?)";

  try{
   driver = new AS400JDBCDriver();
   as400 = new AS400(server, user, pass);

   // Connect to the Database
   con = AS400JDBCConnection.class.cast(driver.connect(as400));

   // Prepare the call
   stm = AS400JDBCCallableStatement.class.cast(con.prepareCall(sp));
   stm.setString(1, "ABC123");

   // Execute the Stored Procedure
   rs = AS400JDBCResultSet.class.cast(stm.executeQuery());

   while(rs.next()){
    System.out.println(rs.getString(5) + " : " + rs.getString(6));
   }

  }catch(Exception e){
   e.printStackTrace();
  }finally{
   try{
    // Make sure to disconnect   
    as400.disconnectAllServices();  
   }catch(Exception e){
    e.printStackTrace();
   }
  }
 }
}

In DB2 we use the keyword CALL to execute a StoredProcedure. To call a Stored Procedure, we need to create a CallableStatement from the connection and then we can set the values of the parameters using the setter methods. After all the values are set for the Stored Procedure, you simple call the executeQuery method of the stm object if it returns a ResultSet, or executeUpdate method in case of a StoredProcedure that updates records.

I am using the wrappers provided in the JT Open library for Connection, Statement, ResultSet, etc. for this example. This however is not required, you can use the same interfaces provided by the java.sql.* package. Infect I have to Cast the returned objects to the AS400 types explicitly before using them. This is because, for example, stm.executeQuery() by default returns an instance of java.sql.ResultSet and I have to explicitly cast it to com.ibm.as400.access.AS400JDBCResultSet before I can use it. Here this is done merely to show that we have AS/400 specific classes in the JT Open library. However, you may need them if you want to access some AS/400 specific data types and functions.

These are the very basics of how to use the IBM Toolbox for Java to communicate with programs in AS/400. For more details and advanced topics you can consult the IBM programmers guide. To view or download the PDF version of this document, select IBM® Toolbox for Java™ (about 3100 KB).