Parallel Lines

Had kind of a wild ride with this one liturgical conundrum, but I think it's resolved now. It was deep, but the ultimate solution was very simple.

The main program is written in ILE RPG and it does all of it's I/O using SQL. The main result set consists of multiple sub-selects that are UNIONed together into one giant pile. In the WHERE and HAVING clauses of most of these sub-selects it utilizes a user defined function (UDF) that does some data massaging.

This all ran perfectly fine on v5.4 of i5/OS, but after the upgrade to v7.1 of IBM i the job started behaving strangely. Sometimes it would work fine. Sometimes it would run inside the first FETCH statement all day long and take up huge amounts of CPU. Other times the UDF would function check because of invalid data in the strangest places, like on the RETURN statements.

To try and stem the function checking I added MONITOR/ENDMON blocks to all of the RETURN statements in the UDF. When an error occurred they would return *LOVAL instead. Then it started function checking on the statements that were calling the functions with the aforementioned function checking RETURN statements. It was very weird.

Over a two month period I was sending all kinds of traces, SERVICEDOCS, and job logs to Ib'm support angels. The last thing we did was set up a job watcher in iDoctor and the output found that when the FETCH was looping and it was doing so in one of the functions in the UDF. That's when they asked me if it the service program was specified to support multi-threading.

Doh!

As it turns out the SQL to create the functional stubs that called the RPG service program functions defaulted to PARALLEL while the program did not. So remember, if you're writing (or have written) an SQL UDF in a language other than SQL, don't forget that you might have to make your program or service program multi-thread capable. In RPG it means adding the keyword THREAD to the control specifications (aka H-specs) with either the *CONCURRENT or *SERIALIZE attribute. (THREAD(*CONCURRENT) did the trick for me.) Be sure also to thoroughly read the sections in the RPG and SQL manuals on multi-threaded processing.