Overview
Unlocking IBM i business logic and data is a priority for many companies. There is a number of IBM i (AS/400), iSeries tools and technologies that can be used to expose existing back-end programs as APIs. In this post, we will use the MuleSoft Anypoint integration platform and Infoview Web Transaction Framework to create a RESTful API that calls the RPG program. This is a continuation of IBM i (AS400, iSeries) integration with the 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, an order typically has a header and a variable number of lines, payment options, taxes, special instructions etc. IBM i programs typically work with a 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.
A more flexible approach is using the staging tables on IBM i:
- Mule API parses JSON or XML request parameters and saves the request data into IBM i staging, using a unique “transaction id”
- Mule API calls IBM i program, passing transaction id
- IBM i program reads request data from staging table and executes business logic
- IBM i program saves the response into IBM i staging tables and notifies Mule when done
- Mule API retrieves response data from staging tables and builds JSON or XML response
The benefit of this approach is a clear 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 with all good things in life, this comes at a cost – extra I/O associated with persisting and retrieving the data. In the 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:
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:
- Mule: Create RAML API definition in API Designer or Anypoint Studio
- Mule: Generate implementation stubs from RAML in Anypoint Studio
- IBM i: Define new Web Transaction type and interface program
- IBM i: Optionally create staging tables for request and response
- Mule: Call WebTF stored procedure CRTTRN to create a new transaction
- Mule: Optionally insert request data into a DB2 staging table(s)
- Mule: Optionally call WebTF stored procedure PRCTRN to process the transaction
- Mule: Optionally retrieve response data from DB2 staging tables
- Mule: Transform the response data into API response format
As discussed above, in the 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 the Data API post. Follow exactly the same steps as in Data API tutorial, to create a 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 a 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 the CALL EDTTYPES command and then pressing F6 to create a new:
Note – since we don’t need to build a request staging table, we set Auto Process on Create to Y. When Mule calls CRTTRN, it will create a new transaction and then immediately call the specified interface program WTGETPRC.
Next, let’s create a Response staging table:
[code] --*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); [/code]
The processing program populates the staging file with product and pricing info as well as an error flag, tied to a specific transaction ID.
Next, we will create a WebTF processing program WTGETPRC that gets the 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 a given product, or returns an error message if the product is not found. Below is the program definition:
[code] 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 [/code]
WebTF implementation program WTGETPRC will call GETPRDPRC and save the response to the staging table.
We will use the 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 the response staging table with the current trans ID as a key. The interface program is just a simple wrapper around an 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.
[code] /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 [/code]
After the program is created, we can quickly test it, something like:
[code]
monitor;
crttrn('GETPRDPRC':' ': 'TABLE' : transID : returnCd:returnMsg);
on-error;
dsply 'Program bombed!';
endmon;
if returnCd <> 0 ;
dsply 'Transaction Processing failed!';
endif;
*inlr = *on;
return;
[/code]
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 and 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:
[code] <dependency> <groupId>net.sf.jt400</groupId> <artifactId>jt400</artifactId> <version>8.5</version> </dependency> [/code]
Add Database connector to the flow get:/products/{productname} and define configuration similar to how it was done for Data API
Note: the connection string may have to include the library list required for back-end logic execution, as well as WebTF library
[code]
jdbc:as400://${as400.endpoint};user=${as400.user};password=${as400.password};libraries=${as400.libraries};naming=*system
[/code]
Below is an example of a library list definition. The leftmost library will be at top of the list
[code]as400.libraries=WEBTF,MULEDEMOS,*LIBL[/code]
For production configuration, make sure to set up connection pooling for the database, defining the appropriate initial/max number of connections in the pool. Otherwise, Mule will open/close the 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, and set the parameterized query to
[code]CALL CRTTRN (:transType,:altTrnID,:reqData,:transID,:returnCd,:returnMsg)[/code]
And add CRTTRN parameters as follows:
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 the staging table. Set operation to Select and use the following query:
[code]select * from WTGETPRCR where transid = #[payload.transID][/code]
Next, add the Transform Message step, define the output format using a sample JSON file exactly as we did for the Data API, and then map the response to our target data structure:
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:
And that’s it – the resulting flow looks like this:
Set mule-app.properties and run the application:
Try a product that’s not defined in our database:
Finally, we can check all transactions with their start/end timestamp, request parameter, and status, in WebTF table TRNHDR.
This operational data is very valuable and can use for alerting and monitoring directly on IBM i or better yet streamed to the event and log aggregation / alerting and monitoring tools such as Splunk or ELK.
Conclusions
We walked through the process of exposing IBM i business logic via APIs using the 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 developers. 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 a significant long-term return on investment. For 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.
Recent Comments