Last year I wrote "Encore's Extreme Makeover from the Inside Out" (February 2007, article ID 20806 at SystemiNetwork.com) for System iNEWS. That article focused on my company, Encore Capital Group, and its creation of a modern interface for a custom System i consumer management application. Rather than using IBM's Host Access Transformation Services (HATS) or its WebFacing tool, Encore chose to redesign its existing application to use a Model-View-Controller (MVC) design. The MVC design reduces tight coupling of application components and increases component reuse. It enabled us to replace our limited text-based interface with a more intuitive graphical interface, reducing our training efforts and costs and helping us deliver our application to anyone, anywhere, without installing client software.

Although beneficial, Encore's application redesign and new interface is not the last step toward modernizing the company's core application, nor is it the ultimate design we want to use for our new applications. It's not the last step because the application is still too dependent on RPG. Encore wants all of its applications to be flexible for easy adoption of standards-based technologies as they are born and adopted by the IT industry at large.

The good news for System i customers is that the System i supports technologies that have been around for decades as well as the latest industry-accepted, standards-based ones. There is no need to hazard moving to a new platform or performing "pull the plug" type conversions to adopt new technologies. Instead, we can remain on the System i and move gracefully in stages that involve less risk than performing wholesale conversions.

My team and I are now in what we call "stage two" of our road to the future. At this point, we are increasing our use of standards-based technologies and incorporating service-oriented architecture (SOA) into our design. Specifically, stage two has two goals:

• replace RPG external stored procedures with either SQL stored procedures or in-line client SQL
• design to SOA by implementing web services with Java Enterprise Edition (JEE)

In stage two, Encore will better exploit the available skills of contemporary developers and give them greater component and application reuse whether the application is internal or external. Here, I provide an overview of the components we are developing and the SOA design we are using to help us meet our goals.

Goal One: Replace RPG External Stored Procedures

First, let's review Encore's MVC pattern in Figure 1, specifically the model. We refactored RPG programs by removing the DDS interface logic, leaving components containing only the domain model (business rules and data). We created a browser user interface (the view) with a Java web application, which accesses the model through the controller (Java servlet) and a design pattern known as the Data Access Object (DAO). The DAO calls RPG programs indirectly through external stored procedures. Using RPG to perform the domain-specific work enabled us to use our existing RPG skills. As you can see in Figure 1, the model uses an external stored procedure to call an RPG program. Of course, as in any RPG program, these programs can optionally call RPG service program procedures. In addition, as of V5R3, external stored procedures can call RPG service program procedures directly, enabling companies to reuse many common shared procedures as stored procedures.

The proprietary System i RPG language is great and has been used to produce industrial-strength applications for a long time. But, adopting non-RPG languages provides easier adoption of new technologies, access to a larger pool of developers (including a huge offshore pool), and an easier path to other platforms. This is not to say that you should abandon RPG, but I do encourage you to consider different methods for new software development that will facilitate the adoption of future technologies.

Encore decided to reduce its dependency on RPG and replace those RPG programs with something more standard. One way to do so is to use a different type of stored procedure — an SQL stored procedure (to find out more about stored procedures, see the references listed in Find Out More, below.

A big benefit of SQL stored procedures over RPG external stored procedures is that the former are standards-based components. It is common for application developers who target MS SQL, Oracle, and other major databases to use stored procedures. For example, we moved one of our Microsoft developers familiar with the MS SQL server database into our DB2 for System i department. He quickly began writing SQL stored procedures for DB2 UDB to retrieve information. It was a natural thing for him to do, and there was no need for him to learn RPG.

RPG programmers may not realize that in a typical RPG shop, users can more easily perform most record I/O with SQL Procedure Language (SPL) than with RPG. SPL offers common program constructs to support program flow (such as while loops and conditional blocks), just as in RPG and other common languages.

Now, when we get a project request that requires modification to an RPG program used by a stored procedure, we consider rewriting that program as an SQL stored procedure. Figure 2 contains an RPG external stored procedure that we replaced with an SQL stored procedure. As you can see, the SQL stored procedure is relatively concise, while the RPG program is lengthy; generally the stored procedure is more intuitive than the RPG program.

Replacing RPG external stored procedures that call RPG programs with SQL stored procedures is relatively straightforward, but replacing RPG external stored procedures that call RPG service program procedures takes more consideration. If the RPG service program procedures that are used by a stored procedure are also used by RPG programs, then the RPG programs will need to be refactored to use the newly created SQL stored procedure replacement. We made the choice to leave these specific RPG external stored procedures alone to avoid refactoring existing RPG programs. You can see in Figure 3 that one of the options is to use an RPG external stored procedure that calls an RPG service program procedure. Eventually, as we replace or sunset our RPG programs (e.g., with Java), we will consider replacing this type of RPG external stored procedures with SQL stored procedures.

Procedure language can vary between databases such as DB2, MS SQL, and Oracle. There are even some differences in SPL for DB2, depending on the platform DB2 is running on. If using SPL for DB2 on different platforms is one of your requirements, see the useful IBM article "The SQL Reference for Cross-Platform Development," to learn more about those differences. With proper planning, you can write SQL stored procedures that can be run on other DB2 platforms with little change. Even though my company is commited to DB2 on System i, using stored procedures in place of RPG will enabled us to adopt other DB2 platforms and databases in the future.

To create and maintain stored procedures, our development team uses WebSphere Development Studio Standard Edition (WDS SE) for System i and Rational Application Developer (RAD). Figure 4 shows an SQL stored procedure being edited in the WebSphere Development Studio's Data Perspective. (Also worth noting: We have devised a source version control methodology using the open-source Subversion system, which is similar to the ubiquitous CVS and enables us to track all source code changes to our stored procedures.)

Stored Procedure Global Temporary Tables

If you're going to work with stored procedures, it is important to grasp the concept of Global Temporary Tables (GTT), or session tables. Session table are similar to work files managed in a QTEMP library. They provide a means by which information can be shared between nested calls between stored procedures. GTTs also provide simple management of temporary information.

DB2 automatically creates SQL GTTs in the QTEMP library. SPL always requires a schema qualifier of SESSION for explicit and implicit GTTs. Similar to any object created in the QTEMP library, the system deletes all temporary tables when the job that runs the procedure ends (or in the case where JDBC is used, when the connection ends).

Stored procedures do have some limitations. First, a stored procedure result set can be accessed only by the first-level call, so if a stored procedure calls a second stored procedure, the first procedure will not have access to the result set of the second stored procedure.

You can overcome this limitation by using Global Temporary Tables, an SQL feature introduced in V5R2. Instead of a procedure returning data via result sets, the data is instead placed into a Global Temporary Table object. Then, with prior knowledge of the agreed-upon name of the temporary table, the invoking procedure reads from the temporary table to access the result set data.

Goal Two: Implement SOA Web Services

Our next goal was to create web services for designing our applications for SOA. Here also, we decided to use Java because we knew that we could build applications that would provide better support for the design patterns used in object-oriented applications and get the job done with less effort. Most Java developers already have experience with JEE, Enterprise JavaBeans (EJBs), and SOA. As I pointed out in my last article, the key to transitioning to Java technology is to hire a senior-level, experienced Java developer who can get you over the hurdles of adopting a new language and development paradigm.

SOA is an architecture, and web services are an implementation of that architecture. Web services provide a standard methodology to publish application components locally or remotely (over the web) to increase component reuse. They also provide a standard messaging protocol for communicating with the components. A client application (either internal or external) makes a request to a web service through an XML message that meets the terms of the web service contract laid out in the Web Services Description Language (WSDL).

Figure 5 illustrates an overview of the web service environment. As you can see, there is a service consumer and a service producer. You can't have one without the other, regardless of whether the service is local or remote. An application may act as a producer of one or more web services and act as a consumer of one or more services. (For example, it might act as a consumer by obtaining shipping information from a remote FedEx web service, and it may be a producer of a service that supplies shipping information to a remote customer about the FedEx shipment your company made.) You can refer to a web service producer as the host of the service. We have several applications where we host a service for a business partner and they host a service for us.

Web services use HTTP for a common communication protocol and XML for a common language to form messages and functions. The web service elements are Universal Description, Discovery and Integration (UDDI), Simple Object Access Protocol (SOAP), and WSDL. (Note: The UDDI is basically an electronic Yellow Pages for web services. We have not made use of this element because we have obtained information about our partner's web services via direct contact.)

WSDL is the electronic web service contract. This text file contains the XML that describes the interface for the web service (e.g., the input and output parameters, the data types of the parameters). Think of WSDL as an API definition written in XML that can be interpreted by an application to communicate with the web service interface.

SOAP is the communication protocol for web services. It allows applications running on other systems (whether running the same or a different OS) to communicate because it uses HTTP and XML. Most systems allow HTTP to pass through their firewalls, but when our application calls a web service, it does so through the SOAP protocol.

If you take on a web service project, you will need a free and open-source testing tool called soapUI. With soapUI, you can test a web service by using the URL of the web service (which you can find in the WSDL). The soapUI application will present you with the XML input that you can then use to submit input values. You then submit the request, and soapUI will present the response in another window (Figure 6).

So how do you develop a web service? Next, I'll provide an overview of EJBs and the Facade design pattern, both of which are easily implemented with the WebSphere Development Studio Advanced Edition (WDS AE) for System i or RAD.

Enterprise JavaBeans

We use WDS AE for System i and RAD to develop EJBs. There are three types of EJBs: entity, session, and message-driven beans (MDBs). We use the session beans because they are the most useful for web services. There are two types of session beans: stateless and stateful.

With stateless beans, we don't need to persist the bean since its sole purpose for our web services is to provide the service (it's basically a method call). The stateless session bean may contain a state, but only for the duration of the invocation to fulfill a request.

EJBs run in a container. Request volumes for web services can vary; for example, there are no requests for hours, and then suddenly you get thousands of requests in short succession. The container manages the number of beans available that are required to meet the demand, eliminating the need for a developer to manage a bean's life cycle.

The Facade Pattern

The Facade pattern makes an interface simpler by hiding the implementation of the interface from the consumer of the service. Figure 7 illustrates the use of the Facade pattern for a web service that provides a customer's profile information. The input to the web service might be a customer number. The output would be profile information. When a request is made to this web service, the user submits a single input parameter, the customer number. However, under the hood, the application calls four different methods from as many as four different classes. Without the facade, the consumer of the service (the requester) would need to make four separate calls to the each of the methods. (Notice the methods belong to stateless session beans running inside an EJB container.)

Road to Discovery

Based on our efforts to create more flexible, standards-based applications over the last several years, we have found that SQL stored procedures are more useful and powerful than we had realized, and they can effectively replace many RPG programs. We also discovered Java design and development is not that difficult, especially when we use readily available tools such as WDS AE and RAD. But don't hesitate to hire experts in the technology areas where your department's IT skills may be deficient. Besides providing quicker implementation, they can also act as mentors.

Also, although everyone is talking about SOA, not everyone has implemented it. In some cases, we had to forgo designing an application to use a web service because our business partner would not agree to create a web service for the project. In this case, we used FTP to exchange information between applications. In another project, we worked with a partner that was implementing a web service for the first time, which caused unexpected delays in our schedule, as we sometimes waited for that partner to work out problems with the implementation. New technologies are great, but they don't get implemented overnight. Fortunately, the System i supports a broad scope of technologies that provide a phased approach to migration. Make a commitment to adopt new technologies and create a staged implementation plan to lessen your risks and increase your chances of success.

Richard Shaler is a senior programmer/analyst at Midland Credit Management, Inc., a subsidiary of Encore Capital Group, Inc., in San Diego, California. He has been involved with new technologies on the System i for the past decade as an author, technical editor, teacher, advisor, and software developer.

What Are Stored Procedures?

Stored procedures are server programs called through an SQL interface. As with program calls, you can pass input, output, and input-output parameters between the client application and the stored procedure it calls. Stored procedures also return result sets and enable users to define a cursor, which provides easy navigation through a result set. Although result sets normally contain multiple rows, they can return only one row, similar to what an RPG CHAIN operation would yield.

Native RPG supports the concept of result sets, although it takes more effort to create them and pass them between programs. For example, RPG developers typically use multiple occurrence data structures or arrays. But you can always use embedded SQL to create and return results in RPG with less effort. However, unlike RPG procedures, Java methods, or C functions, stored procedures do not return a value that can be used in a program expression.

All major database vendors support an SQL interface to their databases through a JDBC or ODBC API, so stored procedures give your application more flexibility because most applications can call them regardless of the language or platform (e.g., Java, .NET, PHP) used. Stored procedures are portable, so if we decide to write a .NET application, it can use the same stored procedure used by our Java applications. Encore's graphical-interface web applications (written with Java) get most of their data from calls to stored procedures written for DB2. Stored procedures can also call other stored procedures, increasing code reuse through modularization.

There are two types of stored procedures: external and SQL. An external stored procedure uses an external program such as RPG to do its work. So in reality, these are RPG external stored procedures. Think of them as wrappers around an RPG program or service program procedure.

An SQL stored procedure is a procedure written with the SQL Procedure Language (SPL). You can use SPL to define tables, indexes, views, and user-defined functions (UDFs). UDFs enable users to create functions in an SQL statement to return a value.

— R.S.

Find Out More

Chong, Raul, Clara Liu, Sylvia Qi, and Dwaine Snow. Understanding DB2: Learning Visually with Examples. IBM Press, 2005.

Janmohamed, Zamil, Clara Liu, Drew Bradstock, and Raul F. Chong. DB2 SQLPL: Essential Guide for DB2 UDB on Linux, UNIX, Windows, i5/OS, andz/OS. IBM Press, 2004.

Stored Procedures, Triggers, and User-Defined Functions on DB2 Universal Database for iSeries (Click here)