Query optimization and UDX calls

A Db2® instance has internal performance algorithms to make queries as fast and as efficient as possible. These internal algorithms, in combination with the UDX registration settings and query design, influence the behavior of the UDX. For example:
  • If you define a UDX as RETURNS NULL ON NULL INPUT, if the system detects a NULL input value, it skips the UDX and automatically returns a NULL value.
  • If you define a UDX as DETERMINISTIC, the system might call the UDX only once during statement preparation time rather than once for each row it operates on during the query execution. This only happens if the UDX takes all literal arguments or no arguments, or if the UDX RETURNS NULL ON NULL INPUT and it is given at least one literal NULL as an argument.
  • If you register the UDX as NOT DETERMINISTIC, the system always invokes the function to obtain a value.
  • If you register the UDX as CALLED ON NULL INPUT, the system invokes the function even if it is passed one or more NULL input values. Your function must then be designed to handle input NULL values appropriately.

Carefully consider the performance implications for these settings. If your UDX really is DETERMINISTIC or it returns NULL on NULL input, there are performance benefits to the resulting query optimizations. You might want to use different settings for these registration options in your test environment than in the production environment.