Send data to IBM i (AS/400) multi member files with MuleSoft Anypoint

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 provide 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 specified members 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 back end

All of the above options are pretty straightforward for an IBM i integration development team, however, 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 file members as files. It can be seen in the QSHELL screen by typing:

[code]
QSHELL
cd /QSYS.LIB/DKUZNETSOV.LIB/TESTFTP.FILE
[/code]

For the multi-member file DKUZNETSOV/TESTFTP it will show a list of members:

[code]
> 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
$

[/code]

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.

[code]
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"

[/code]

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: https://github.com/infoviewsystems/IBMi-Mule-Send-To-Multimember-File-