Send Data to IBM i (AS/400) Multi-Member Files With Mule
Check out this quick tutorial that demonstrates a way to transfer data to IBM i with the helping hand of Mule.
Join the DZone community and get the full member experience.Join For Free
On a recent call with a prospect, I ran into a use case where they needed to send data to AS400 multi-member files with MuleSoft Anypoint. IBM i (AS/400, iSeries, System i) operating environment includes an integrated DB2 database that is widely used as an application data store. Remote clients can access IBM i data via DB2 SQL Query Engine, JDBC, or ODBC interfaces, and this works well for regular files and DB2 tables. There is a special file type widely used in older IBM i applications that supports partitioning content into multiple "members" and provides methods for isolating the data for traditional programs. The challenge of interfacing with multi-member files is that they cannot be easily accessed via standard SQL clients.
There are several "brute force" options for remotely creating and working with multi-member files in Mule, including:
- Custom Java or .NET code to implement record level access to a specified member using IBM Toolbox for Java or IBM Access for Windows APIs.
- Custom Java or .NET code to create an SQL alias pointing to a specific member then use that alias instead of the file name for SQL operations.
- Use of third-party ETL tools such as GoAnywhere.
- Create standard IBM i DB2 staging files and transfer the data there, then create a custom IBM i program to copy data from the staging files to multi-member files on the backend.
All of the above options are pretty straightforward for an IBM i integration development team, but those approaches still require a lot of custom coding either on the Mule or IBM i side. Fortunately, the IBM i integrated file system (IFS) presents traditional IBM i files in the QSYS file system as directories and members as files. It can be illustrated in the Qshell screen by changing to the file:
> cd /QSYS.LIB/DKUZNETSOV.LIB/TESTFTP.FILE > ls -l total: 172 kilobytes -rwx---rwx 1 INFOPGMR 0 4008 Jul 27 15:14 TEST01.MBR -rwx---rwx 1 INFOPGMR 0 4008 Jul 28 11:50 TEST123.MBR -rwx---rwx 1 INFOPGMR 0 1002 Jul 27 22:49 TEST123CLS.MBR -rwx---rwx 1 INFOPGMR 0 4008 Jul 27 22:51 TEST124.MBR -rwx---rwx 1 INFOPGMR 0 4008 Jul 27 22:58 TEST125.MBR -rwx---rwx 1 INFOPGMR 0 0 Jul 27 14:53 TESTDIMA.MBR -rwx---rwx 1 INFOPGMR 0 0 Jul 27 14:41 TESTFTP.MBR $
Armed with this knowledge, I was able to use the standard Mule FTP connector to push the data directly to a specific IBM i file member by using a path of /QSYS.LIB/<my library>.LIB/<my file>.FILE/ and a target file name of <my member>.MBR
Finally, the AS/400 connector is used to dynamically create a new member before sending the data, then calling the IBM i processing program after the data transfer is completed.
To test the sample flow, execute the following CURL command or use an HTTP client such as Postman.
curl -X POST -H "Cache-Control: no-cache" -H "Content-Type: multipart/form-data" -F "file2member=@path_to_the_input_file" "http://localhost:8081/sendFile?memberName=new_member_name"
The Mule flow expects the attachment filename to be file2member — it can easily be modified to send any attached file.
The code for this article can be found here.
Published at DZone with permission of Dmitriy Kuznetsov. See the original article here.
Opinions expressed by DZone contributors are their own.