Determined to Boost your UDF Performance?

I started off this year highlighting how you can streamline the performance of SQL functions, procedures, and trigger, so it seems natural to end the year by spotlighting another option that can improve user-defined function (UDF) performance. 

 

Based on the SQL performance assessments that my IBM Lab Services team performs for clients, the usage of UDFs by IBM i developers is on the rise. This increased usage of both SQL and external user-defined functions makes sense since UDFs are a vehicle to facilitate good modular programming and to enable SQL access to calculations and transformations already written in high-level language programs.

 

The SQL performance assessments also reveal that IBM i developers are often creating their UDFs with default options which unfortunately are not the best performing options. The default options of NOT DETERMINISTIC and FENCED limit Db2 for i’s ability to deliver maximum performance when a UDF is invoked. It’s definitely worth your while from a performance perspective to not just accept those default settings, but to explore if those options can be changed on your UDF definitions.

 

I discovered that while I was off working on IBM Watson that the Db2 for i development team delivered a new flavor of the deterministic option known as STATEMENT DETERMINISTIC. This new deterministic flavor provides more flexibility as you consider moving your UDF away from the NOT DETERMINISTIC default. This article that I wrote explains this newer deterministic option in more detail. 

 

If you need help determining how to streamline the performance of your functions - deterministic or not, IBM Lab Services is here to help.

 

That’s a wrap on 2021 - have a Merry Christmas & wonderful holiday season and we’ll talk in 2022!