MuleSoft and IBM i (AS400): A Winning Combination (But How Do I Call a PGM?)

We can access the IBM DB2 for i data using the MuleSoft Database Connector, but as we know after 20-40 years of program-centric design, our data and processes are entwined within our programs (PGM).

There are at least a half-dozen methods to call a PGM on the IBM i from another tier, and they are all welcome. I would like to highlight the Young i Professionals' XMLSERVICE solution, as it offers a MuleSoft Team an intuitive XML in XML out of implementation with no PGM re-coding or the requirement of a web server.

Required Reading

These resources will help you understand the rest of the article.

• What is XMLSERVICE?

  • Unleash Your IBM i With XMLSERVICE

  • XMLSERVICE Offers New Life for RPG Programs

• How to connect to the IBM i using Mule
  • Building IBM i Data API with MuleSoft Anypoint
• How to ease your security worries
  • Securely connect Mule to IBM i / AS400

Note: The original goal of XMLSERVICE was flexibility over performance. Call anything IBM i, anywhere, any language. However, the project became more popular than expected, so currently, a higher speed JSON and/or XML version using a different technology for XMLSERVICE(2) is planned, and as an integration solution provider, this is what you want to read.

MuleSoft Implementation

Note: The following is an illustration, not a Mule best practice guide. It is to show you how easy it is to call a PGM via XMLSERVICE DB2 stored procedure, but in the the real world, you would look to use Maven, spring bean, templates, dataweave, exception strategies, placeholders, and even your very own DevKit Connector (watch this space!).

This illustration will use the XMLSERVICES demo as an example, so we aim to call an RPG program called ZZCALL via the PLUG512K stored procedure (see here the subheading XMLSERVICE APIs).

This MuleSoft project can be found on GitHub here.

Step 1

Create a new Mule Project named xmlservices.

Step 2: Add a Subflow to the default Mule Configuration File.

Step 3: Add a Message Properties Transformer, a Database Connector, a Set Payload Transformer, and a Logger Component to our subflow.

Step 4

Let's deal with the Message Properties Transformer. Create a PLUG512K stored procedure and input parameters and values.

• Set Scope to invocation.
• Add three Flow Variables to represent the three input parameters to the stored procedure. See the Interface description here.

The inputForSP_IPC value is set to #['NA']. This input parameter is ignored due to the subsequent inputForSP_CTL setting, as we are making a public connection (see the subheading Connection public or private). Not setting the parameter leads to the following run-time error:

[SQL0470] Null values not allowed for parameter 1 in procedure PLUG512K
in XMLSERVICE. (java.sql.SQLException). (org.mule.api.MessagingException)

The inputForSP_CTL value is set to #['*here'] (again, see the subheading Connection public or private).

The inputForSP_CI value is set to the example XML script under XMLSERVICE PGMs, SRVPGMs, APIs. Details about the contents and reserved words can be found here.

<?xml version='1.0'?><script><pgm name='ZZCALL' lib='XMLSERVICE'><parm  io='both'><datatype='1A'var='INCHARA'>a</data></parm><parm  io='both'><datatype='1A'var='INCHARB'>b</data></parm><parm  io='both'><datatype='7p4'var='INDEC1'>11.1111</data></parm><parm  io='both'><datatype='12p2'var='INDEC2'>222.22</data></parm><parm  io='both'><ds><datatype='1A'var='INDS1.DSCHARA'>x</data><datatype='1A'var='INDS1.DSCHARB'>y</data><datatype='7p4'var='INDS1.DSDEC1'>66.6666</data><datatype='12p2'var='INDS1.DSDEC2'>77777.77</data></ds></parm><return><datatype='10i0'>0</data></return></pgm></script>

Make sure you edit the XML to point to the library where your version of ZZCALL program resides.

Step 5: Database Connector Part 1

Create the Connector configuration by way of a Generic Database Configuration using the AS400JDBCDriver from the JTOpen project.

jdbc:as400://IPADDRESSORHOSTNAME;user=USERNAME;password=USERPASSWORD

com.ibm.as400.access.AS400JDBCDriver

Add the database URL which contains the endpoint (IP address and host name) and login credentials. If you have concerns around security, please see Securely Connect Mule to IBM i/AS400.

Remember to test the connection!

You will most likely get an error detailing that the driver can not be found.

Therefore, download the IBM i DB2 JDBC driver jt400.jar from jt400.sourceforge.net and add it to project build path.

If you are having problems, see this MuleSoft Support Article.

Make sure to set up Connection Pooling with the Min Pool Size set to 1.

• If you fail to set Min Pool Size, you will get "java.sql.SQLException: The connection does not exist." when materializing the CLOB.

You preferably would use a Spring bean to configure a data source to give you more control over connection threads, specifically around the way the stale connections are invalidated and removed from the pool. See Dmitriy Kuznetsov's (who else!) Using Database Connector to Connect to as/400.

Step 6: Database Connector Part 2

Stored Procedure Operation Parametized.

{ call XMLSERVICE.PLUG512K(:IPC,:CTL,:CI,:CO) }

If the stored procedure can not be found at run time you will get the following cryptic SQL exception:

A problem occurred while trying to acquire a cached PreparedStatement in a background thread. (java.sql.SQLException).

Download IBM Data Studio and have a play first with the various stored procedures. As you can see here, I did not even download XMLSERVICE as a version; it comes with the one that had already been installed on my test IBM i.

Step 7: Set Payload Transformer

Lastly, we need to materialize the returned character large object (CLOB), which is an address of the string we want. Under debug, you will see that the returned object is com.ibm.as400.access.AS400JDBCClobLocator, which has some interesting methods such as length() and getSubString(long, int).

#[payload.CO.getSubString(1,payload.CO.length())]

Step 8: Logger

Dump the payload to the log.

Step 9

Use MUnit to create a quick Test Harness so that we can trigger our subflow under debug.

Step 10

Debug to see the fruits of your labor!

Conclusion

Simple! The purpose of this article was to alert the MuleSoft community that there is an easy way to call your IBM i PGM and unlock, leverage, and protect your IBM i investment.

Acknowledgements

• Dmitriy Kuznetsov of Infoview Systems. (specializing in MuleSoft for IBM i).
• Marinus Van Sandwyk of TEMBO Technology Lab (Pty) Ltd (specializing in the development of database modernization solutions for IBM Power Systems running IBM i). Marinus kindly gave me access to an IBM i environment to test this idea.
• Tony Cairns, Senior Programmer for IBM in Rochester and the person behind XMLSERVICE. Thank you for answering my questions.