IBM i Business Logic API with Mule

Overview

Unlocking IBM i business logic and data is a priority for many companies. There is a number of IBM i (AS400, iSeries) tools and technologies that can be used to expose existing back-end programs as APIs. In this post we will use Mulesoft Anypoint integration platform and Infoview Web Transaction Framework to create a RESTful API that calls RPG program. This is a continuation of IBM i (AS400, iSeries) integration with Mulesoft Anypoint series. Previous articles can be found here: 

IBM i integration use cases
Building IBM i Data API with Mule

In the previous post we created Mule API that retrieves IBM i business data directly from DB2 for i database. The approach works great for simple data structures and normalized models. In most legacy applications, however, the stored data must first be processed through a complex business logic, which is typically a part of the existing back-end system, for example RPG program. API implementation must execute the business logic programs and handle the request / response data mapping. 

Passing request and response data

In general, the data passed to IBM i API or returned back can be quite complex and include variable size payloads. For example order typically has a header and a variable number of  lines, payment options, taxes, special instructions etc. IBM i programs typically work with fixed number of input and output data elements. It’s possible to pass a reference to dynamic lists but it requires more effort and doesn’t support streaming for large data sets.

More flexible approach is using the staging tables on IBM i:

  1. Mule API parses JSON or XML request parameters and saves the request data into IBM i staging, using unique “transaction id”
  2. Mule API calls IBM i program, passing transaction id
  3. IBM i program reads request data from staging table and executes business logic
  4. IBM i program saves the response into IBM i staging tables and notifies Mule when done
  5. Mule API retrieves response data from staging tables and builds JSON or XML response

The benefit of this approach is clean separation of duties between IBM i and Mule applications. Mule handles HTTP, security, formatting to / from JSON and XML, schema validation etc. IBM i programs focus on executing business logic.

As all good things in life, this comes at a cost – extra I/O associated with persisting and retrieving the data. In case of small fixed size payloads and high transaction volumes, the data can be passed directly via IBM i program parameters.  

Solution design

The high level design includes Mulesoft API that invokes IBM i business logic program and exchanges the request / response data via Web Transaction Framework (WebTF) and staging DB2 for i tables

The flow diagram below explains the detailed steps and areas of responsibility for Mule and IBM i sides:

image09

Implementation

OK finally let’s get to coding! As you will see, building an API that executes IBM i business logic is pretty straightforward and includes the following steps:

  1. Mule: Create RAML API definition in API Designer or Anypoint Studio
  2. Mule: Generate implementation stubs from RAML in Anypoint Studio
  3. IBM i: Define new Web Transaction type and interface program
  4. IBM i: Optionally create staging tables for request and response
  5. Mule: Call WebTF stored procedure CRTTRN to create new transaction
  6. Mule: Optionally insert request data into DB2 staging table(s)
  7. Mule: Optionally call WebTF stored procedure PRCTRN to process transaction
  8. Mule: Optionally retrieve response data from DB2 staging tables
  9. Mule: Transform the response data into API response format

As discussed above, in case of complex request / response structures the data is exchanged via staging tables while simple payloads can be passed directly via Web Transaction Framework calls

Mule: RAML and flow stubs

For this example we will use the same Product Price API RAML definition that we created for Data API post. Follow exactly the same steps as in Data API tutorial, to create new Mule project, define IBM i database connector, import RAML API definition and generate Mule flow stubs.

IBM i: Define WebTF transaction and staging files

First, we need to decide whether to stage the request and response parameters or pass them via WebTF calls. Our RAML definition takes a single parameter productName, and expects back a list of prices (regular, sales, etc). We can easily pass single parameter via WebTF call, however we will need a staging table for the response, as we don’t know in advance how many price records can be returned.

Next let’s define WebTF transaction using CALL EDTTYPES command then pressing F6 to create new:

image04

Note – since we don’t need to build request staging table, we set Auto Process on Create to Y. When Mule calls CRTTRN, it will create new transaction then immediately call the specified interface program WTGETPRC.

Next, let’s create Response staging table:

--*CRT: RUNSQLSTM COMMIT(*NONE) ERRLVL(30) :*
set schema muledemos;
drop table wtgetprcr;
-- Response Staging
create table WTGETPRCR (
id bigint not null generated always as identity primary key,
transid bigint not null with default,
productID bigint not null with default,
productName char(30) not null with default,
priceGroup char(10) not null with default,
productPrice decimal(11,2) not null with default,
errorYN char(1) not null with default,
errorMsg char(254) not null with default);

The processing program populates staging file with product and pricing info as well as error flag, tied to a specific transaction ID.

Next, we will create a WebTF processing program WTGETPRC that gets product name, calls “business logic” back-end program then saves the data to response staging table above.

For the purpose of this example, let’s assume that we have an existing RPG program GETPRDPRC that retrieves a list of price groups and product prices for given product, or returns an error message if product not found. Below is program definition:

d main            pr                  extpgm('GETPRDPRC')
d  productName                  30a   const
d  productID                    20i 0
d  priceGroup                   10a   dim(100)
d  productPrice                 11s 5 dim(100)
d  returnCd                      3s 0
d  returnMsg                   254a                    

WebTF implementation program WTGETPRC will call GETPRDPRC and save the response to staging table.

We will use demo program DEMO02R that comes with WebTF distribution as a template for WTGETPRC. The program logic is very simple – call GETPRDPRC then save the result to response staging table with current trans ID as a key. The interface program is just a simple wrapper around existing business logic program that handles the request / response data mapping and perhaps some basic translations, but delegates all heavy lifting to the existing programs. 

/free                                                                 
    productName = %trim(reqData);
   returnCd = *zeros;
   getPrice(productName:productID:priceGroup:
                    productPrice:w#returnCd:w#returnMsg);                                                                   
    if returnCd < 0;
       exec sql                                                        
        insert into wtgetprcr (transid,productName,
                                  errorYN, errorMsg) 
        values(:transID, :productName, 'Y', :w#returnMsg);            
      returnCd = w#returnCd;                                          
    else;                                                             
      for i = 1 to 100;                                               
        if priceGroup(i) = *blanks;                                 
          leave;                                                    
        endif;                                                      
        w#pricegrp = pricegroup(i);                                 
        w#price = productPrice(i);                                  
        exec sql                                                    
          insert into wtgetprcr (transid,productid, productName,    
            priceGroup, productPrice, errorYN)                      
            values (:transID,:productid, :productName,              
            :w#priceGrp, :w#price, 'N');                            
      endfor;                                                       
    endif;                                                          
  //*inlr = *on;                                                    
  return;                                                           
/end-free                                                           

After the program is created, we can quickly test it, something like:

monitor;
crttrn('GETPRDPRC':' ': 'TABLE' : transID : returnCd:returnMsg);
on-error;
dsply 'Program bombed!';
endmon;
if returnCd <> 0 ;
dsply 'Transaction Processing failed!';
endif;
*inlr = *on;
return;

Mule: call WebTF and get the response from staging

Now that we defined and implemented WebTF transaction and interface program, we can easily call it from Mule then get back the results.

We already have the Mule project created with API definition and auto-generated stubs. Make sure jt400.jar (AS400 JDBC driver) is on the project Build path. For Maven projects, add the following dependency to pom.xml:

<dependency>
             <groupId>net.sf.jt400</groupId>
            <artifactId>jt400</artifactId>
            <version>8.5</version>
</dependency>

Add Database connector to the flow get:/products/{productname} and define configuration similar to how it was done for Data API

image08

Note: the connection string may have to include the library list required for back-end logic execution, as well as WebTF library

jdbc:as400://${as400.endpoint};user=${as400.user};password=${as400.password};libraries=${as400.libraries};naming=*system

Below is an example of library list definition. The leftmost library will be on top of the list

as400.libraries=WEBTF,MULEDEMOS,*LIBL

For production configuration, make sure to set up connection pooling for the database, defining appropriate initial / max number of connections in the pool. Otherwise Mule will open / close connection every time DB operation is performed. This can work well for development and testing, as the WebTF program is closed on IBM i side after each call, but affects the interface performance. When connection pooling is configured, the DB connections and the associated WebTF interface programs stay open between the calls, minimizing the start up / shut down overhead.  

Back to Database processor, select operation Stored Procedure, set the parameterized query to

CALL CRTTRN (:transType,:altTrnID,:reqData,:transID,:returnCd,:returnMsg)

And add CRTTRN parameters as follows:

image03

The output of the stored procedure call is a map of parameter name / value pairs.

Next, add another database processor right next to CRTTRN call, to retrieve the response from staging table. Set operation to Select and use the following query:

select * from WTGETPRCR where transid = #[payload.transID]

Next, add Transform Message step, define the output format using sample JSON file exactly as we did for the Data API, then map the response to our target data structure:

image05

Next, drag another Transform Message processor to set the HTTP response code to 200 if no errors, 404 if there are any errors returned in the staging file, and 500 if there are no records at all in the staging file:

image10

And that’s it – the resulting flow looks like this:

image00

Set mule-app.properties and run the application:

image06

Try a product that’s not defined in our database:

image11

Finally, we can check all transactions with their start / end timestamp, request parameter, and status, in WebTF table TRNHDR.

image01

This operational data is very valuable and can used for alerting and monitoring directly on IBM i or better yet streamed to the event and log aggregation / alerting and monitoring tool such as Splunk or ELK.

Conclusions

We walked through the process of exposing IBM i business logic via APIs using Mulesoft Anypoint platform and Web Transaction Framework.

The Mule development is very straightforward and focuses on designing RAML API, calling WebTF stored procedure, working with staging tables for complex request / response structures, and transforming resultset to required JSON output. Anypoint platform provides great low code tools that greatly simplify these tasks even for beginner Mulesoft developer. Really there’s nothing special IBM i skills needed here, just a regular API, database and Data Weave development.

The IBM i part of development is also very simple and includes defining WebTF type, creating staging tables for complex request / response (if needed), and building simple WebTF processing programs that wrap calls to business logic and stage the response. WebTF provides unified structure for implementing and operating integration components, as well as demo code that can be used as a template for quickly creating new programs. There is nothing specific to Mule or external APIs here, and IBM i development does not require any JSON or XML parsing or building, or using any special tools or APIs. It’s just a regular database, data transformation, and program call operations that all IBM i developers are very comfortable with.

Separation of duties is a great concept and works very well in this example. Based on our team’s experience, it is very helpful when the dev team and individual developers / architects can address both Mule and IBM i sides, eliminating “lost in translation”, time lags, and many other issues. Investing in IBM i teams Mule training offers significant long term return on investment. For the quick wins during initial implementation, consider partnering with cross-functional Mulesoft and IBM i team.

The code for this article can be found at https://github.com/infoviewsystems/IBMi-Mule-Logic-API

Contact us to get a free evaluation version of Web Transaction Framework.

 

Dmitriy Kuznetsov