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!
