Java tips and tricks.: April 2013

Monday, April 15, 2013

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.

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).

Using AS/400 DataQueues in Java

Using AS/400 DataQueue.

DataQueues are an integral part of the AS/400 System. For any external communication they are a very good tool to implement event based applications to take appropriate actions on either sides on receipt of a message. Best thing about the DataQueues is that they are bidirectional, means not only you can send a message to the AS/400 but also you can receive a message from AS/400.

Data queues provide considerable flexibility to the Programmer. The DataQueues interfaces require no communications programming and can be used either for connected or disconnected communication. Java programs can communicate with AS/400 programs via a common AS/400 DataQueue. The data queue messages are merely described at the record-level, allowing the application programmer to define the field-level structure as required.

JT Open provides a simple set of Classes that hide most of the communication complexity and presents a very simple easy to use set of APIs to the Java Programmer. I am going to demonstrate the use of these classes with the help of tow very simple programs that reads and writes messages to a DataQueue. First the program that reads data from the DataQueue.

Reading from DataQueue


package as400;

import com.ibm.as400.access.AS400;
import com.ibm.as400.access.DataQueue;
import com.ibm.as400.access.DataQueueEntry;

public class DataQueueTest implements Runnable{

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

 String queueName = "MYDTQ";
 String libraryName = "MYLIB";
 
 private AS400 system = null;
 private DataQueueEntry dqData = null;
 
 @Override
 public void run() {

  String queue = "/QSYS.LIB/" + libraryName +".LIB/" + queueName +".DTAQ";
  
  try{
   int cntr=1;
   system = new AS400(server, user, pass);
   DataQueue dq = new DataQueue(system, queue);
   
   while(true){
    
    String data = null;
    try{
     
     System.out.println("Listening to DataQueue ......");
     
     // Start wailting for a message to arrive
     // Disconnect after 5 seconds if nothing received
     dqData = dq.read(5);
     if (dqData != null) {
      // get the data out of the DataQueueEntry object.
         byte[] bytes = dqData.getData();
      data = new String(bytes, "IBM285").trim();
        }
     
     if (data == null || data.trim().length() <= 0) {
      
      // Break after 5 tries
      if(cntr < 5){
       System.out.println("DataQueue Re-Started: " + cntr);
       cntr ++;
       continue;
      }else{
       System.out.println("Giving up on DataQueue after " + cntr + " tries");
       break;
      }
     }
     System.out.println("--|" + data + "|--");
    }catch(Exception e){
     e.printStackTrace();
    }
   }
  }catch(Exception e){
   e.printStackTrace();
  }finally{
   // Make sure to disconnect
   if(system != null){
    try{
     system.disconnectAllServices();  
    }catch(Exception e){}
   }
  }
 }
 
 // Main method to start the Thread
 public static void main(String a[]){
  new Thread(new DataQueueTest()).start();
 }
}

This is a very simple program listening to a DataQueue and waiting for a message to arrive. We pass the timeout to dq.read(5); method in seconds. This is very low just to show the output as this tries again and again to re-connect to the queue. Here is the output when I run this program on my system.

Listening to DataQueue ......
DataQueue Re-Started: 1
Listening to DataQueue ......
--|00000001ADS#D000000000000000000000100000000000000#JK9000000000000001000|--
Listening to DataQueue ......
DataQueue Re-Started: 2
Listening to DataQueue ......
--|00000001SDFJKLO0000000000000000000100000000000000#OD8000000000000001000|--
Listening to DataQueue ......
DataQueue Re-Started: 3
Listening to DataQueue ......
DataQueue Re-Started: 4

As you can see from the output, the data received is a long String, this is actually a list of parameters received from AS/400 with fixed lengths. We can extract data from this String and use it to Kickstart something in our Java program.

Writing to a DataQueue

Now let's see how we can write data to a DataQueue.


package as400;

import com.ibm.as400.access.AS400;
import com.ibm.as400.access.DataQueue;

public class DataQueueTest {

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

 String queueName = "MYDTQ";
 String libraryName = "MYLIB";
 
 private AS400 system = null;
 
 public static void main(String a[]){

  String queue = "/QSYS.LIB/" + libraryName +".LIB/" + queueName +".DTAQ";
  
  String dataStr = "Message from Java";
  
  try{
   system = new AS400(server, user, pass);
   DataQueue dq = new DataQueue(system, queue);
   
   // Convert the Data Strings to IBM format
   byte[] byteData = dataStr.getBytes("IBM285");
   
   dq.write(byteData);
   
  }catch(Exception e){
   e.printStackTrace();
  }finally{
   // Make sure to disconnect
   if(system != null){
    try{
     system.disconnectAllServices();  
    }catch(Exception e){}
   }
  }
 }
}

Friday, April 12, 2013

Calling AS/400 CL Commands from Java

Calling AS/400 Commands.

In this article, we shell see how the CommandCall class work to execute AS/400 Commands from Java. First thing we need are the details of an AS/400 Command that we will be executing from our Java client. For the purpose of this exercise we created a test AS/400 Command that executes a batch job on the AS/400 System. Here are the details of the program:


AS/400 Command:  MYLIB/RUNMYJOB SOME(PARAM)

NOTE: You can call any iSeries server CL command.


package as400;

import java.util.Date;

import com.ibm.as400.access.AS400;
import com.ibm.as400.access.AS400Message;
import com.ibm.as400.access.CommandCall;


/**
 * Test program to test the AS/400 Command from Java.
 */
public class AS400CommandCallTest {

 public static void main(String[] args) {  

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

  String commandStr = "MYLIB/RUNMYJOB SOME(PARAM)";

  AS400 as400 = null;
  try  {
   // Create an AS400 object  
   as400 = new AS400(server, user, pass);  
   
   // Create a Command object
   CommandCall command = new CommandCall(as400);

   // Run the command.
   System.out.println("Executing: " + commandStr);
   boolean success = command.run(commandStr);
   
   if (success) {  
    System.out.println("Command Executed Successfully.");
   }else{
    System.out.println("Command Failed!");
   }
   
   // Get the command results
   AS400Message[] messageList = command.getMessageList();
   for (AS400Message message : messageList){
    System.out.println(message.getText());
   }
  } catch (Exception e) {  
   e.printStackTrace();  
  }finally{
   try{
    // Make sure to disconnect   
    as400.disconnectAllServices();  
   }catch(Exception e){}
  }  
  System.exit(0);  
 }  
}

And here is the out put of this program when you run it.


Executing: MYLIB/RUNMYJOB SOME(PARAM)
Command Executed Successfully.

All the command parameters are passed as a space saparated list with the command. We check the value returned by command.run(commandStr); to see if the command was successful and display the appropriate message. Calling the command.getMessageList(); will always return a list of messages if gnerated by the AS/400 Command. They can be either failure or success messages depending on if the command succeded or failed.

Note that the command.run(commandStr); will only return data if a completion message was generated by the command you are executing. Some commands do not generate a completion message if they run successfully. If you want the CommandCall to return a message regardless of its success or failure, you could do the following:

Create a CL program that will run the necessary command(s).

Include the SNDPGMMSG command as follows:
SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) MSGDTA('put msg text here') MSGTYPE(*COMP)
Note: The MSGTYPE parameter must be set to *COMP.

Now create a CL command that will call your CL program.

What you have done here is actually wrapped the AS/400 Command inside your custom command. The new CL command will allow you to receive a meaningful completion message in your JAVA program.

Monday, April 8, 2013

Calling AS/400 RPG Programs from Java

Calling AS/400 RPG Programme.

In this post, I am going to demonstrate how to execute the RPG programs from Java. First thing we need are the details of an RPG program that we will be calling from our Java client. For the purpose of this exercise we created a test RPG program in our AS/400 system that takes two parameters, one for input and one for output. The program takes the input parameter as the first name and tries to find the users with that name in the system and returns the last name of the user if it exists and ERROR if it fails to find the user. Here are the details of the program:


Program Name: RAZPGM.PGM 
Library Name: RAZA.LIB 
PGM Location: QSYS.LIB

We have two options at our disposal here to call this same program from our Java application. One approach is to call this program directly using the ProgramCall class and the other option is to generate an XML document that contains all the information needed by AS/400 to call the program and pass it on to the AS/400. The first approach is easier and gives you more control whereas the XML approach is a lot more flexible. Both of these approaches are very easy to use. I did not create a performance benchmark for this exercise; however, my little tests did not show any significant difference in performance.

Using ProgramCall Class
One way of calling a RPG program from Java is to use the ProgramCall class in JT Open library that will seamlessly integrate the RPG program into your java application. Calling a program is fairly trivial, all you need to do is to create an AS/400 instance and pass it to the ProgramCall constructer and then call the program with list of parameters. Let's see how the ProgramCall class is used to call this RPG program.

package as400;

import java.util.Date;

import com.ibm.as400.access.AS400;
import com.ibm.as400.access.AS400Message;
import com.ibm.as400.access.ProgramCall;
import com.ibm.as400.access.ProgramParameter;


/**
 * Test program to test the RPG call from Java.
 */
public class AS400Test {

 public static void main(String[] args) {  
  
  String server="yourserver.company.com";
  String user = "AS400USER";
  String pass = "AS400PWRD";

  String input = "RAZA";
  String fullProgramName = "/QSYS.LIB/RAZA.LIB/RAZPGM.PGM";

  AS400 as400 = null;
  
  try  {
   
   // Create an AS400 object  
   as400 = new AS400(server, user, pass);  

   // Create a parameter list
   // The list must have both input and output parameters
   ProgramParameter[] parmList = new ProgramParameter[2];  

   // Convert the Strings to IBM format
   byte[] inData = input.getBytes("IBM285");

   // Create the input parameter  
   parmList[0] = new ProgramParameter(inData);

   // Create the output parameter
   parmList[1] = new ProgramParameter(5);

   // Create a program object  
   // specifying the name of the  
   // program and the parameter list.  
   ProgramCall pgm = new ProgramCall(as400);  
   pgm.setProgram(fullProgramName, parmList);  

   // Run the program.  
   if (!pgm.run()) {  
    // If the AS/400 cannot run the  
    // program, look at the message list  
    // to find out why it didn't run.  
    AS400Message[] messageList = pgm.getMessageList();
    for (AS400Message message : messageList) {
     System.out.println(message.getID() + " - " + message.getText());
    }

   } else {  
    // Else the program ran. Process the  
    // second parameter, which contains  
    // the returned data.
    byte[] data = parmList[1].getOutputData();  
    String lastName = new String(data, "IBM285").trim();

    System.out.println("Output is " +  lastName);  
   }  

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

And here is the out put of this program when you run it.


Output is ABIDI

Notice the parmList array of parameters. Each member of this array is an instance of ProgramParameter. Notice the difference between the first and second ProgramParameter() constructors. The first one takes an array of byte and the second one takes an integer. When you pass a byte array, that indicates this as an input parameter where the byte array contains the data that you want to pass on as a parameter to the RPG program. When just an integer is specified, it tells the program that this is an output parameter of the specified size.

Using PCML document
Another way is to create an XML document using the Program Call Markup Language (PCML) format that IBM has provided. This is essentially a simple XML document that you can pass on to the AS/400 system and the AS/400 will extract the program information and parameters from the XML document and will execute the program for you. Now let's see how the PCML works. Here is a sample PCML file that is using the same RPG program.


<pcml version="1.0">

  <!-- Program RAZPGM and its parameter list -->
  <program name="razpgm" path="/QSYS.LIB/RAZA.LIB/RAZPGM.PGM">
    <data name="firstName"    type="char"    length="4"    usage="input"    init="RAZA"/>
    <data name="lastName"     type="char"    length="5"    usage="output" />
  </program>

</pcml>

And here is the Java program that is using this PCML file to call the RPG program.


package as400;

import com.ibm.as400.access.AS400;
import com.ibm.as400.access.AS400Message;
import com.ibm.as400.data.ProgramCallDocument;



/**
 * Test program to test the RPG call from Java using PCML.
 */
public class AS400XMLTest {

 public static void main(String[] argv){
  
  String server="yourserver.company.com";
  String user = "AS400USER";
  String pass = "AS400PWRD";
  
  AS400 as400 = null;

  try {
   
   // Create an AS400 object  
   as400 = new AS400(server, user, pass);
   
   // Construct ProgramCallDocument
   // First parameter is system to connect to
   // Second parameter is pcml resource name. In this example, 
   // PCML source file "qsyrusri.pcml" must be found in the classpath.
   ProgramCallDocument pcml = new ProgramCallDocument(as400, "qsyrusri.pcml");

   // Request to call the API
   // If return code is false, we received messages from the server
   if(!pcml.callProgram("razpgm")) {
    
    // If the AS/400 cannot run the  
    // program, look at the message list  
    // to find out why it didn't run.  
    AS400Message[] msgs = pcml.getMessageList("razpgm");
    
    for (AS400Message message : msgs) {
     System.out.println(message.getID() + " - " + message.getText());
    }
    
   }else{
    
    // Return code was true, call to RAZPGM succeeded
    // Write the results to standard output
    Object value = pcml.getValue("razpgm.lastName");
    System.out.println("Output is " +  value);  
   }
   
  }catch (Exception e){
   e.printStackTrace();
  }finally{
   try{
    // Make sure to disconnect   
    as400.disconnectAllServices();  
   }catch(Exception e){}
  }

  System.exit(0);
 }
}

Note here that in the java program we are not passing any parameters. The parameter value is provided in the XML document. Also note that the program name and the parameter names are referenced using the name of the program in the tag of the XML document. Rest of the XML document is pretty self-explanatory.

And here is the output when this program is executed.


Output is ABIDI

Pages