Introduction
Some time ago we had to develop and deploy a new system side application for our customers. As our customer base is using mainly Windows and System i, and our whole new system configurations have been developed in c# we had to decide which way we go. Either rewrite the current codebase in c++ and port it to System i or find another solution. As c++ is very specific on the System i, it would have been an own development on its own.
After some digging in the internet I came across some articles (sadly I haven't saved them, so I cannot reference these) which described the execution of scripts by using sql (strsql) on the System i.
Prerequisites
I installed the IBM i Access client solution on my dev workstation, including the ODBC-Drivers for DB2 and started up Visual Studio. As a side note: at this time there was no native .net DB2 driver and later on I never got it really running - however as we want to control the System i and not transfer insert/update many data in the database, I did not care about using ODBC.
The whole integration ended up in using:
- ODBC to access the database and execute commandos (Access to QSYS.QCMDEXC required
- The IFS (integrated file system) for transferring files by SMB/CIFS
A specific connection string should be used, to set the required library list:
Driver={Client Access ODbC Driver (32-bit)};System=i5system;Uid=user;Pwd=password;DefaultLibraries=ITLIB1,ITLIB2
Executing cl procedures
By executing a normal 'non query' and QSYS.QCMDEXC we can execute any command. Here we will execute a CL procedure in ITLIB2 which is called TESTCN. The CL procedure which we call will receive 2 parameters and the 'native' call on the System i is stored in the variable clCmd:
string clCmd = "CALL PGM(ITLIB2/TESTCN) PARM('1stParam' '2condParam')";
The call itself, please note that we need to escape the single quotes ' when using them in the sql query. Furthermore, QCMDEXC does require the length of the command string in 15,5 packed format.
string pgmParm = $"CALL QSYS.QCMDEXC('{cmdtext.Replace("'", "''")}', {clCmd.Length.ToString("0000000000.00000").Replace(',', '.')})";
Error handling
An error at calling will throw an exception (OdbcException) which can then be examined for the exact error code.
Spool files
For reading spool files with SQL, SYSTOOLS with SPOOLED_FILE_DATA is available.
Conclusion
Naturally, a native port would have been much better implementation BUT by creating some CL procedures and building up an architecture where the platform calls are handled by specific procedures/functions we got a fluent integration which can run from any windows operating system.