Manage UDR execution

After the routine manager creates a routine sequence and pushes the arguments onto the stack, it invokes the UDR. It then manages the execution of the UDR associated with this routine sequence.

The number of times that the UDR is invoked depends on the following factors:
  • Does the UDR handle SQL NULL values?

    If an argument to the UDR is the SQL NULL value and the UDR does not handle NULL values (it was not registered with the HANDLESNULLS routine modifier), the routine manager does not invoke the UDR.

  • Is the UDR an iterator function?

    An iterator function has several iterations. It executes once to initialize the iterations, once for each iteration, and once to release iteration resources. For more information, see Write an iterator function.

  • Where is the UDR invoked within the SQL statement?
    • If the UDR is in the select list, it executes once per row that the WHERE clause qualifies.
    • If the UDR is in the WHERE clause, the exact number of times that it executes cannot be predicted. It might be less than or equal to the number of rows or it might not be executed at all. The query optimizer makes this determination.
    • If the UDR is in an EXECUTE FUNCTION or EXECUTE PROCEDURE statement, it executes once (unless it is an iterator function).
A C UDR executes in one or more virtual processors (VPs). VPs are grouped by the kind of task they perform into VP classes. The presence of the CLASS routine modifier in the UDR registration determines in which VP class the UDR executes, as follows:
  • If the UDR registration did not have a CLASS routine modifier or this CLASS routine modifier specified the CPU VP, the routine manager dispatches the UDR to a CPU VP for execution.
  • If the UDR registration has a CLASS routine modifier that specifies a user-defined VP class, the routine manager dispatches the UDR to a VP in the specified VP class for execution.
For more information about how VPs execute C UDRs, see Virtual processors.
Tip: The DataBlade® API does provide some functions to change the VP environment once the UDR begins execution; however, these are advanced functions. You must use them only under special circumstances. For more information, see Control the VP environment.