The ProgramCall class allows a user to call an IBM i system program, pass parameters to it (input and output), and access data returned in the output parameters after the program runs. Package com.ibm.as400.access Description. Provides classes that represent various IBM i data and resources. The ProgramCall class allows a user to call an IBM i system program, pass parameters to it (input and output), and access data returned in the output parameters.
We can access the IBM DB2 for i data using the MuleSoft Database Connector, but as we know after 20 - 40 years 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 implementation and 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 and XMLSERVICE Offers New Life for RPG Programs
- Unclear how to connect to the IBM i using Mule? Building IBM i Data API with Mulesoft Anypoint
- Worried about Security? Securely connect Mule to IBM i / AS400
- 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 which as an integrator is what you want to read.
Mulesoft Implementation
Caveat: 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 real world you would look to use Maven, spring bean, templates, dataweave, exception strategies, place holders and even your very own DevKit Connector
This illustration will use XMLSERVICES demo example so we aim to call a RPG program called ZZCALL via the PLUG512K stored procedure (see here the sub heading XMLSERVICE APIs)
----------------------------------------------------------------------------------------------------------
This Mulesoft project can be found on GitHub here
Step 1. Create 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 - Message Properties Transformer. Create PLUG512K stored procedure input parameters and values
- Set Scope to invocation
- Add 3 Flow Variables to represent the 3 input parameters to the stored procedure. See Interface description here
1- inputForSP_IPC value is set to #['NA'] .This input parameter is ignored due to subsequent inputForSP_CTL setting as we are making a public connection (See Connection public or private). Not setting the parameter leads to the following run-time error:
2- inputForSP_CTL value is set to #['*here'] (See Connection public or private)
3- 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.
Make sure you edit the XML to point to the library where your version of ZZCALL program resides.
Notepad++ does not recognize <script> tag so change to <myscript>
----------------------------------------------------------------------------------------------------------
Step 5 - Database Connector Part 1. Create the Connector configuration by way of a Generic Database Configuration using the AS400JDBCDriver from the JTOpen Project
- Add the database URL which contains the end point (IP Address / 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 IBM i DB2 JDBC driver jt400.jar from jt400.sourceforge.net and add it to project build path.
- If you are having problems see Mulesoft Support Article
- Make sure to set up Connection Pooling with 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 materialising 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 (who else!) Using database connector to connect to as/400
----------------------------------------------------------------------------------------------------------
Step 6 - Database Connector Part 2. Stored Procedure Operation Parametized.
If the stored procedure can not be found at run time you will get the following cryptic SQL exception:
Down load 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 comes with the Zend Server which 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 the returned object is com.ibm.as400.access.AS400JDBCClobLocator which has available some interesting methods such as length() and getSubString(long, int).
----------------------------------------------------------------------------------------------------------
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 sub flow under debug.
----------------------------------------------------------------------------------------------------------
Step 10 - Debug to see the fruits of your labour.
---------------------------------------------------------------------------------------------------------
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/protect your IBM i investment.
Acknowledgements
- Dmitriy Kuznetsov of Infoview Systems your one stop shop for your Mulesoft and IBM i needs.
- 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.
We can access the IBM DB2 for i data using the MuleSoft Database Connector, but as we know after 20 - 40 years 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 implementation and 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 and XMLSERVICE Offers New Life for RPG Programs
- Unclear how to connect to the IBM i using Mule? Building IBM i Data API with Mulesoft Anypoint
- Worried about Security? Securely connect Mule to IBM i / AS400
- 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 which as an integrator is what you want to read.
Mulesoft Implementation
Caveat: 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 real world you would look to use Maven, spring bean, templates, dataweave, exception strategies, place holders and even your very own DevKit Connector
This illustration will use XMLSERVICES demo example so we aim to call a RPG program called ZZCALL via the PLUG512K stored procedure (see here the sub heading XMLSERVICE APIs)
----------------------------------------------------------------------------------------------------------
This Mulesoft project can be found on GitHub here
Step 1. Create 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 - Message Properties Transformer. Create PLUG512K stored procedure input parameters and values
- Set Scope to invocation
- Add 3 Flow Variables to represent the 3 input parameters to the stored procedure. See Interface description here
1- inputForSP_IPC value is set to #['NA'] .This input parameter is ignored due to subsequent inputForSP_CTL setting as we are making a public connection (See Connection public or private). Not setting the parameter leads to the following run-time error:
2- inputForSP_CTL value is set to #['*here'] (See Connection public or private)
3- 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.
Make sure you edit the XML to point to the library where your version of ZZCALL program resides.
Notepad++ does not recognize <script> tag so change to <myscript>
----------------------------------------------------------------------------------------------------------
Step 5 - Database Connector Part 1. Create the Connector configuration by way of a Generic Database Configuration using the AS400JDBCDriver from the JTOpen Project
- Add the database URL which contains the end point (IP Address / 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 IBM i DB2 JDBC driver jt400.jar from jt400.sourceforge.net and add it to project build path.
- If you are having problems see Mulesoft Support Article
- Make sure to set up Connection Pooling with 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 materialising 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 (who else!) Using database connector to connect to as/400
----------------------------------------------------------------------------------------------------------
Step 6 - Database Connector Part 2. Stored Procedure Operation Parametized.
If the stored procedure can not be found at run time you will get the following cryptic SQL exception:
Down load 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 comes with the Zend Server which 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 the returned object is com.ibm.as400.access.AS400JDBCClobLocator which has available some interesting methods such as length() and getSubString(long, int).
----------------------------------------------------------------------------------------------------------
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 sub flow under debug.
----------------------------------------------------------------------------------------------------------
Step 10 - Debug to see the fruits of your labour.
---------------------------------------------------------------------------------------------------------
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/protect your IBM i investment.
Acknowledgements
- Dmitriy Kuznetsov of Infoview Systems your one stop shop for your Mulesoft and IBM i needs.
- 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.