Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Building an IBM i Data API with Mulesoft Anypoint

DZone's Guide to

Building an IBM i Data API with Mulesoft Anypoint

Learn how to build an IBM i Data API with Mulesoft Anypoint.

Free Resource

Modernize your application architectures with microservices and APIs with best practices from this free virtual summit series. Brought to you in partnership with CA Technologies.

Overview

The common IBM i (AS400, iSeries) use case is to share the application data with other systems. In this article, I will show you how easy it is to build the IBM i Data API with Mulesoft Anypoint. We will define our RAML API first, then add the code to pull data directly from the DB2 database using standard Database connector and IBM i database driver. Depending on complexity and design of the IBM i application, the persistent data must be transformed through complex business rules before it can be used by consuming applications. In this case, API implementation must execute IBM i programs that implement such business logic. We will do that in the follow-up post.

It may appear at first that creating API adds more work and operational overhead compared to point to point integrations. Based on our experience, however, building out API layers is one of the most efficient ways to promote the reuse and secure, govern and monitor the access to application data and logic. Relatively small upfront investment results in greatly improved delivery speed, especially as the number of interfaces grows.

Data Model

Suppose our sample application database model consists of 3 tables: products, product prices, and inventory.

image07

The script can be found here. It can be executed with any SQL client such as SquirrelSQL or by using RUNSQLSTM command on IBM i. It generates the database structure and seeds it with sample data.

Product Price API and Implementation

Designing and building an IBM i data API with Mulesoft Anypoint is pretty straightforward and includes the following steps:

  1. Create RAML API definition in API Designer or Anypoint Studio.
  2. Generate Mule implementation stubs from RAML in Anypoint Studio.
  3. Define Database Select and connection settings in Anypoint Studio with Generic Database connector.
  4. Define transformation rules with Data Weave in Anypoint Studio.

RAML Definition

Create api.raml file in src/main/api folder or in Anypoint API Designer. Our product price lookup RAML is very simple. The real life project will most likely include more involved resources, the schema, security, pagination, filtering, and sort order parameters and/or HATEOAS links.

#%RAML 0.8

title: Product Pricing
version: 1.0
/products/{productname}:
 get:
   responses:
     200:
       body:
         text/json:
           example: |
            {
             "productName": "TV",
             "productID": 3,
             "productPrices": [{
                 "priceGroup": "REGULAR",
                  "productPrice": 500.00
               }, {
                  "priceGroup": "SALE",
                  "productPrice": 450.00
               }]
             }

Once the RAML definition is ready, in the spirit of TDD approach, we can run it as a mock service in API designer then create test scripts with Postman or other API testing tools. This would also be a good time to share RAML and use cases with other developers and teams via API Notebook and get their feedback.

Mule Implementation

Define Maven dependency in pom.xml

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

If not using Maven, download IBM i DB2 JDBC driver jt400.jar from http://jt400.sourceforge.net/ and add it to the project build path.

Generate Mule flows from RAML. Right-click on api.raml and select Mule -> Generate flows from RAML. After the flow stubs are generated, run the project as Mule application. The API will run inside Anypoint Studio and return sample data defined in RAML. Define the database connector configuration.

Make sure to set up connection pooling with an adequate number of threads to keep the connections open between subsequent requests. Opening a new connection to IBM i DB2 is an expensive operation!

image06

Now it’s time to implement our auto-generated API flow get:/products/{productname}. The resulting flow is very simple and consists of just three steps - retrieve product data from the database, then transform the results, then set the HTTP status to 200 (success) or 404 (not found).

First, open main/app/api.xml, in the get:/products/{productname} flow remove auto-generated Set Payload processor and instead add Database connector. We will use the HTTP query parameter productName as a key to pull all prices for given product.

Add Transform Message DataWeave next to the database connector:

We can see that input properties are pre-populated from the database table structure, but the output format is not defined.

image02

Save sample json output from API definition into a file /src/test/resources/productPriceSample.json

On Transformation Properties screen, click on Define Metadata and add new metadata configuration > set type to JSON > select sample file.

Select the definition then use it to map (drag) input to output elements.

Note that the input is a list of records returned by resultset, and the output is a complex structure with a header-level product name and ID and a detail-level price group and price. I wish DataWeave UI would be smart enough to recognize it but it’s not quite there yet, so we will have to make few tweaks to the auto-generated mapping in the mapping code.

  1. Change output format from %output application/json to %output application/java.
  2. Replace productID: payload.PRODUCTID as :number with productID: payload.PRODUCTID[0] as :number - this will use the product ID from the first result record.
  3. Return productName = “Product not found” when database select returned no records.

The resulting transformation should look like this:

image04

Finally, we need to set HTTP status to 200 (success) or 404 (not found). There are several ways to do it, here I am going to use DataWeave.

Add another Transform Message to the flow, in the output add a property with name http.status, and remove payload. This will ensure that the payload value built in the previous step remains intact and we just set the correct HTTP status here.

The mapping is very simple. I check the value of productName field set in the previous step. If it’s “Product not found” then it sets the http.status to 404 otherwise defaults to 200.

image03

Run Mule application and test it with Postman or API console:

image10

Now run it for product NOTFOUND:

image08

Considerations

Wouldn’t it be nice if all real life projects were as simple as the how-to guides like this one? As always, the devil is in the details! Here are some things to remember:

  • Testing: create API test scripts and plug into the CI/CD tool.
  • Security and policies: set up application access to the API along with rate limiting and other policies. By default, use HTTPS endpoints unless there’s a compelling reason not to.
  • Schemas and models: create external resource type definitions that can be reused by multiple APIs.
  • Add filters, sorting, pagination, and/or HATEOAS links: pagination and filtering are important as they help clients request and get only relevant information and avoid shuffling large volumes of data to and from with little value. HATEOAS links help simplify and streamline the control flow logic on the client.
  • Community engagement: post the API definition to the company’s Anypoint Exchange with references to the API Notebook to make it easy for various teams to discover and consume.

Security

  • DB2 for IBM i JDBC driver supports SSL and TLS encrypted connections. The certificate must be configured on IBM i and imported into each client application’s trust store to accommodate SSL handshakes (read this blog post for more details).
  • Access to the IBM i database from clients outside the network generally should not be allowed. Communicate with IBM i via proxy server (for more information see https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/rzahh/proxies.htm).
  • Use application-specific user IDs defined with limited read-only access to application data. Optionally create read-only views for external access and restrict remote users’ access to everything else.

Performance

  • Pool and reuse connections across multiple requests for high volume real time interfaces. Each time a new connection is open, it starts a new QZDASOINIT/QZDASSINIT communication job on IBM i, which is an expensive operation.
  • Analyze DB2 for i query access plan. Create new indexes if needed based on index advisor recommendations to tune complex select statements.
  • Use read blocking for selects and commit blocks for inserts and updates to process records in chunks for large data volumes.
  • Configure application specific IBM i subsystems to segregate communication jobs QZDASOINIT/QZDASSINIT instead of running all interface jobs in the QUSRWRK subsystem.
  • Proxy server configuration and encrypted communication with SSL can negatively impact the interface performance.

Conclusions

Building IBM i data API with Mulesoft Anypoint is a straightforward task involving standard API and Mule development. We created RAML API definition in API Designer or Anypoint studio, generated Mule flows from RAML, added database select,defined and tweaked a bit the mapping rules for payload and HTTP status code, and that was really it! Reasonable integration developer should be able to implement similar APIs in less than an hour or in some cases in a matter of minutes, focusing mostly on RAML design, DB extract and transformation rules.

The complete example can be found here.

The Legacy databases are famous for complex non-normalized data models that require applying complex business rules before the data can be exposed to other systems. In most cases, the IBM i application already has programs that implement this logic. In these scenarios, instead of accessing DB, the Mule applications should call IBM i program. In the next article, I will walk through this use case.

The Integration Zone is proudly sponsored by CA Technologies. Learn from expert microservices and API presentations at the Modernizing Application Architectures Virtual Summit Series.

Topics:
integration ,mulesoft ,api

Published at DZone with permission of Dmitriy Kuznetsov. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}