Recently, a client reported performance issues with some of his new SQLRPGLE programs that were retrieving data via REST APIs provided by the other party.
Now, after doing a bit of analysis, we immediately identified the cause of that slowness: the SYSTOOLS HTTP functions were being used to call the APIs. These functions were fine, but that was before the introduction in 2021 of the new functions in QSYS2, which allow you to achieve the same result in much, much less time. Here’s an example: as you can see, the version using SYSTOOLS (with the same results) is significantly longer.
The reason for the slowness lies in the fact that the functions in SYSTOOLS are based on Java services that are used specifically to process the data exposed by the endpoint. Of course, it’s also important to keep in mind that on IBM i, only one JVM is allowed per job—a detail to keep in mind when launching JVMs with custom parameters.
So there’s the performance issue, but there’s also a security-related problem. In fact, in an increasingly interconnected world, even APIs rightly respond over HTTPS, and this requires that the CA of our endpoint be trusted. The problem, however, is that the functions in SYSTOOLS are tied to Java, so the CAs must be imported into the Java keystore, which, by default, is replaced every time Java Group PTFs are installed. Functions in QSYS2, on the other hand, use the DCM keystore or a custom keystore; in either case, the entries imported into it remain persistent, at least until the certificates become deprecated, etc.
But now, how can I tell if I have any programs or jobs that use these functions in SYSTOOLS? It’s simple, let’s put these objects under audit.
The theory is simple: you run the CHGOBJAUD command on the objects in SYSTOOLS called HTTP….. Too, too easy—because, as shown in the screenshot, there are two types of functions with the same name: the wrapper function, which is called by passing an XML parameter, and the actual function, which actually refers to a Java class, meaning it’s an externally defined procedure. In this case, there is no object in SYSTOOLS, so…
However, I have verified that in this case, the Java classes are located at the path /qibm/userdata/OS400/SQLLib/Function/jar/SYSTOOLS/DB2RESTUDF.jar
So the solution is running following commands: CHGOBJAUD OBJ(SYSTOOLS/HTTP) OBJTYPE(ALL) OBJAUD(*ALL) and CHGAUD OBJ('/qibm/userdata/OS400/SQLLib/Function/jar/SYSTOOLS/DB2RESTUDF.jar') OBJAUD(*ALL)
The good news is that our objects are now being audited, so I can query the QAUDJRN to check if the objects are being used… The bad news is that, since they’re in a JVM, the entry is generated only once per job, so it’s not possible to determine if the function is used multiple times within the same job.
This SQL query retrieves all ZR-type entries (object reads) from the last hour; as you can see, one of them is from me:
SELECT ENTRY_TIMESTAMP,
USER_NAME,
QUALIFIED_JOB_NAME,
PROGRAM_LIBRARY,
PROGRAM_NAME,
REMOTE_ADDRESS,
LIBRARY_NAME,
OBJECT_NAME,
OBJECT_TYPE,
PATH_NAME
FROM TABLE (
SYSTOOLS.AUDIT_JOURNAL_ZR(STARTING_TIMESTAMP =>CURRENT TIMESTAMP - 1 HOURS)
)
And you, what SQL function do you use to call REST APIs?
Andrea