Re: Result sets in SQLRPGLE

Hi Stefan,

Am 20.07.2024 um 09:53 schrieb stefan@xxxxxxxxxx:

Daniel wrote:

The more modern approach would be to write a user defined table function (UDTF) - with that you can even use the returned data set inside of CTEs or any SQL construct (aside from updates/deletes).

Thanks a lot for pointing me in this direction - seems to be exactly what I need!

Thanks - I feel flattered - but I have to give the credits back to "giants" like Scott, who wrote about those thing years before I even knew it was possible.

If you create the UDTF with CREATE FUNCTION you have to think hard about some parameters:

NOT DETERMINISTIC vs DETERMINISTIC vs STATEMENT DETERMINISTIC - means, does the function always return the same results if called with the same parameters - or at least when called multiple time inside one statement - this gives a massive performance boost, because the engine can cache the results
MODIFIES SQL DATA vs READS SQL DATA vs CONTAINS SQL vs NO SQL - the engine does not allow UPDATE or DELETE or INSERT when not created with MODIFIES SQL DATA - it doesn't even allow SQL when NO SQL is used - the optimizer can use this to implement the best query strategy
DISALLOW PARALLEL vs ALLOW PARALLEL - well, also a massive performance booster - but ALLOW PARALLEL works only if the RPG program/service program is built with THREAD(*CONCURRENT) and is considering multi-threading completely (but of course some procedures can be made SERIALIZED) - in fact ALLOW PARALLEL can give a massive performance boost with queries that use UDTFs, because the engine doesn't have to wait for the UDTF to finish, before calling it again (especially when JOINing the UDTF to a table)

I experimented a lot with those keywords - they have massive influence on the performance of the queries that use these functions. So try them out as far as you can.

HTH and kind regards,
Daniel