Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Stored procedures provide way to reuse RPG apps on the Web

Learn how you can reuse RPG apps on the Web in stored procedures.

What are stored procedures?

Stored procedures offer a safe, portable and reliable way to build application-to-application interfaces in many environments, including e-business WebSphere applications on the iSeries. A client program can CALL a server program using the database manager's support for stored procedures.

Stored procedures allow SQL clients to call application logic using the CALL statement. The procedure called can be defined in SQL procedure language or as an external program in any high level language (RPG, COBOL, Java, C++ etc).

For Java applications, JDBC (Java Database Connectivity) provides a stored procedure SQL escape that allows stored procedures to be called in a standard way for all RDBMSs.

Procedures send and receive data to and from host programs easily

  • This escape syntax has one form that includes a result parameter and one that does not.
  • JDBC drivers handle conversion from Java data types to database data types automatically.
  • If used, the result parm must be registered as an OUT parameter.
  • The other parameters can be used for input, output or both.
  • Parameters are referred to sequentially by number. The first parameter is 1.
  • {?= call <procedure-name>[<arg1>,<arg2>, ...]}
  • {call <procedure-name>[<arg1>,<arg2>, ...]}
  • IN parameter values are set using the set methods inherited from PreparedStatement.
  • OUT parameters must be registered prior to running the stored procedure.
  • Their values are retrieved after execution via get methods provided here.
  • CallableStatement can return one or more result sets from the called program.
  • Client can navigate the result sets (much like a standard subfile).
  • MUST access Result set objects BEFORE getting OUT parameters.

Uses for stored procedures

Sample uses for procedures include the following:

  • Integrating client and host application execution across platforms.
  • Accessing host data efficiently with a local app and returning results to client app.
  • Updating, inserting or deleting rows in host tables easily.
  • Maintaining host data architecture programmatically: create and modify tables dynamically without DDS OR an administrator.
  • Run a simple CL program passing a command string to execute. (That eliminates need for using Toolbox command calls in most cases.)

Where can procedures be executed from?

In MANY network scenarios or locally on a single system. JDBC drivers like the iSeries Java toolbox driver (type 4 JDBC drivers) can run anywhere in a network. Many JDBC drivers exist in the market for almost every need today. The one scenario where a JDBC driver is usually NOT the best solution is for a client application accessing a server application over the Web. The host database remote access support (IBM's DRDA ) usually runs on a specific port. Opening this port through a firewall for Internet access is NOT the best idea. Options include VPN or some other remote access method.

Web applications DO work very well WHEN the procedures are invoked by an application running on a server. Often, a user is working in a Web browser using an application running in WebSphere, WebLogic, etc.

Why use procedures over other options?

  • The DB2 stored procedure builder can CREATE stored procedure applications easily.
  • Stored procedures are the "portable" way to integrate existing server applications.
  • They can return result sets for applications that bring reports to a browser, etc.
  • Procedures execute in the stable database environment.
  • Procedures can be invoked anywhere in the network.
  • Procedures can execute "in process" like JNI or "out of process".
  • The majority of existing batch and server programs are directly callable without changes.
  • Compare to JNI, ProgramCall, Runtime.exec, JCA, RMI.
  • You request stored procedure services defined by three Java Interfaces: CallableStatement, PreparedStatement, Statement.

Calling procedures without registering

If you have existing batch or server programs, the majority of those can be called without registering procedures first. Programs that receive or update parameters can be statically called directly if they meet certain rules:

  • All parms are treated as INOUT (input and output) host variables.
  • The CALL type does not include indicators (GENERAL).
  • Use a library list or qualified program name (based on connection naming option).
  • The program language is retrievable at runtime by the system.

If you use naming = system on a JDBC connection, you can set a libraries=lib1, lib2 etc parm on the Java toolkit JDBC driver (com.ibm.as400.access.AS400JDBCDriver).

DB2 Stored Procedure Builder (SPB)

IBM has created different versions of a stored procedure builder. The iSeries version is built in to Operations Navigator and can generate SQL procedures.

The DB2 UDB version in Universal database common servers (NT, Unix, Linux) generates both SQL and Java stored procedures. That's the one I use. It supports the following:

  • Visually registering and managing stored procedures in a database.
  • Testing any stored procedure installed.
  • Generating SQL or Java stored procedures using wizards.

Three types of stored procedures

  1. SQL stored procedures are defined with SQL statements and compiled to a C++ executable with SQL.
  2. Java stored procedures are either JDBC or SQLJ Java classes that are packaged into jar files for deployment.
  3. RPG stored procedures (or other high-level language: COBOL, C++) are developed as regular programs using other tools and defined as procedures using a CREATE PROCEDURE statement or directly called as procedure.

Which type of stored procedure is right for you?

Benefits of SQL stored procedures

  • Use SQL stored procedure language (extended set of SQL statements) for speed.
  • Generate SQL stored procedure application easily and install it.
  • Compiled executable performs as well as RPG service program.
  • Test interface for running a stored procedure.
  • Correctly handle data type mapping between database and Java automatically.
  • Built-in debugger for SQL stored procedures IF running on a local server.
  • Can replace RPG logic for application processing, reporting in some cases.
  • Requires suitable C++ compiler installed on server to compile procedures.

Benefits of Java stored procedures

  • Use Java stored procedures for EASY portability, greater customization of application logic.
  • Generate Java stored procedure application easily and install it.
  • IF NOT returning result set, code must be modified slightly to set output variables.
  • Java performs very well IF you reuse connections and statements.
  • Test interface for running a stored procedure.
  • Correctly handle data type mapping between database and Java automatically.
  • Must use IBM distributed debugger for Java stored procedures on any server.
  • Can replace RPG logic for application processing, reporting in most cases quickly.
  • Requires only a Java Runtime Environment (JRE) accessible by database.

Benefits of RPG stored procedures

  • Use RPG stored procedures to reuse existing code and skills, integrate existing applications.
  • RPG performs very well especially IF you reuse connections and statements.
  • Test interface for running a stored procedure in the SPB.
  • Correctly handle data type mapping between RPG and Java automatically.
  • Use IBM distributed or iSeries debuggers for RPG stored procedures.

Build process for stored procedures using SPB

How to create Java or RPG procedures on Windows

  1. Use SPB to create the procedure definition
    Java wizard can generate the procedure
    or manually create a definition to call a selected RPG program.
  2. When running build, set options to build for debug if needed.
  3. Check that the procedure is installed in the selected database.
  4. Check and edit the generated bat file to install the procedure on iSeries (e.g., password is not set correctly).
  5. Open a DB2 command line processor on the iSeries to run the bat file.
  6. Test the procedure with a sample call.
  7. Use IBM Distributed Debugger to debug the procedure if needed.
    -- installed in DB2 UDB on local server, run idebug
    -- installed in DB2 iSeries server, run irmtdbgc

SPB procedure configuration

User definable default dataType mappings from SQL to Java. They're used only when explicit definitions aren't supplied. Normally, especially in the wizard, they are supplied.

Portable JDBC procedures

When building Java applications that call stored procedures, use the Java standard SQL interfaces (java.sql package for Statement, PreparedStatement and CallableStatement). Those interfaces are guaranteed portable across any platform. IBM does provide other classes with different options for some functions, BUT those won?t necessarily be portable to any environment.

For more, see the java.sql package for

The java doc comments are very accurate on how to use these classes correctly.

SPB examples

//Example 1 -- SQL procedure to return employee salary

                OUT salary dec(11,2) )
-- SQL Stored Procedure 
  -- Declare variable
  DECLARE salary_TMP dec(11,2);

  -- Declare cursor

  -- Cursor left open for client application
  OPEN cursor1;

  SET salary = salary_TMP;
END P1          

//Example 2 -- Java procedure to return Employee Salary

 * JDBC Stored Procedure JGETEMPPAY6
 * Cape Cod Bay Systems, copyright 2002, all rights reserved
package cx1.procs;

import java.sql.*;          // JDBC classes

public class JGETEMPPAY6
  public static void jGETEMPPAY6 ( String EMPNO,
                   ResultSet&amp;#91;&amp;#93; rs ) throws SQLException, Exception
    // Get connection to the database
    Connection con = DriverManager.getConnection("jdbc:default:connection");
    PreparedStatement stmt = null;
    String sql;

    sql = "SELECT"
      + " FROM"
      + "  JEM.EMPLOYEE"
      + " WHERE"
      + "  ("
      + "   ( JEM.EMPLOYEE.EMPNO = ? )"
      + "  )";
    stmt = con.prepareStatement( sql );
    stmt.setString( 1, EMPNO );
    rs&amp;#91;0&amp;#93; = stmt.executeQuery();
    if (con != null) con.close();

Manual build process for stored procedures

How to create Java or RPG procedures on Windows

  1. Create a stored procedure application using Java, RPG, COBOL, C++ or SQL.
  2. Use CREATE PROCEDURE statement to install in the selected database manager. (Use DROP first to remove existing procedure definition by the same name.)
  3. Run the stored procedure from the client (here a JDBC app) using CALL statement.

IF the host program is updated, you will need to recreate the procedure definition to install the updated program in the database manager.

Using SELECT statement Result Sets in Stored procedures

SQL Select statement can return a result set. A result set is a table for rows representing the data selected by executing the SELECT statement.

Cursors on result sets

When SQL runs a select statement, the resulting rows comprise the result table. A cursor provides a way to access a result table. It is used within an SQL program to maintain a position in the result table. SQL uses a cursor to work with the rows in the result table and to make them available to your program. Your program can have several cursors, although each must have a unique name. SQL statements used with a cursor include: DECLARE CURSOR, OPEN, CLOSE, WHENEVER, FETCH, UDPATE, DELETE, SELECT.

Using a SELECT statement with cursor

Here are some RPG code excerpts showing how to

  1. Do a static SQL SELECT query using a cursor
  2. Open the cursor for navigation
  3. Set a result set with the cursor so the client application can retrieve the data.
   C/Exec Sql Declare C1 Cursor For
   C+  Select
   C+  FIRSTNME AS IFirstName,
   C+  LASTNAME AS ILastName
   C+  From EMPLOYEE
   C+  Where EMPNO >= :IEmpno
   C+ For Fetch Only         -- Read Only Cursor

   C*  -- open the cursor on the result set for navigation by row
   C/Exec Sql
   C+ Open C1

   C*  -- use if returning a result set from SELECT (vs a host structure)
   C**   Set Result Sets Cursor C1 - use for result sets
   C**   Set Result Sets Array Output For ORowno Rows ? for program loops
   C/Exec Sql
   C+ Set Result Sets Cursor C1

Cursor types under JDBC 2.0

You use a cursor to access data in a client application that receives a result set from the sample RPG code above.

Serial cursors provide only forward access through a result of rows. After a row is read, it can't be retrieved again. Scrollable cursors support back and forward navigation similar to subfiles. You can also monitor for end-of-data conditions to do special handling. And you can use blocked fetch to bring rows down a page at a time for better performance for Web applications, etc. JDBC 2.0 also defines support for updates through result sets. My last attempt to test this found it wasn't yet supported on the iSeries as a CachedRowSet object. I haven't checked again in V5R1.

Summary on stored procedures

Stored procedures are incredibly useful for many application scenarios, portable, connect applications in any language over many network configurations and don't require MQ licenses or programming. The IBM Stored Procedure Builder makes it easy to generate new stored procedures. And your existing batch and server applications can often be called with no changes. What could be better?

About the authors: Jim Mason is president of ebt-now.com, and he writes, consults, teaches, designs and develops iSeries Web applications using Java, WebSphere, DB2, Lotus Domino and the WebSphere Development Tools for AS/400. Dave Slater is World Wide Market Manager of AS/400 Application Development at IBM Canada.


Dig Deeper on Web Tools