Use NOT FENCED UDFs for Better Db2 for i Performance

Dawn May explains why it's important to understand your options beyond the "slow and safe" default

Editor’s note: Dawn May would like to thank IBM’s Scott Forstie for his assistance in writing this article.

I’m seeing more and more IBM i shops embracing user-defined functions (UDFs).

When creating a function, there are a set of options that are used. These options are often overlooked and the defaults taken. The Db2 for i default behavior for functions are “slow and safe.” I think you should take the time to understand the options, and position your functions to perform as fast as possible.

In particular, I want to talk about FENCED. FENCED is the default when creating a function. It means that the function will run in a secondary (separate) thread from the job’s primary thread. This is good for isolation and reduces the likelihood of cursor name collision. However, running the function in a separate thread adds overhead to create the thread, orchestrate the work between the threads and then ultimately destroy the thread.

The reason FENCED is the default on CREATE or REPLACE FUNCTION is to maintain compatibility with other Db2 implementations. User defined functions (as well as procedures) allow user-written code to be added to the database. On other Db2 implementations (such as Db2 LUW), where the database is not an integrated part of the operating system, the introduction of user-written code into the database carries risk, such as corruption or crashes. As such, functions and procedures are created as FENCED by default to reduce this risk.

Db2 for i, along with the architecture of IBM i, are different and this risk does not exist. FENCED is the default simply to be consistent with the other Db2 implementations (and FENCED is ignored on procedures). NOT FENCED is better for performance and NOT FENCED will not crash Db2 for i.

If you are creating functions, consider whether you can create them as NOT FENCED to reduce overhead of secondary threading.

To understand the “FENCED” posture of existing functions, query the QSYS2.SYSFUNCS database catalog.

-
-
- What functions are defined as FENCED within the COOLSTUFF schema?
--
SELECT routine_schema, routine_name, "FENCED", "INLINE", is_deterministic, parallelizable
  FROM qsys2.sysfuncs
  WHERE "FENCED" = 'YES' AND routine_schema = 'COOLSTUFF'
  order by routine_name;

If you notice an existing function that can be changed to run within the thread of the consumer, the ALTER FUNCTION (SQL) statement can be used to change the fenced attribute.

--
-- Change an existing function to be NOT FENCED
--
alter function COOLSTUFF.ADDIT_SLOW alter not fenced;

Highly used FENCED functions can generate a lot of secondary threads. Take the time to review your SQL functions and use NOT FENCED where possible.

---