Create the routine sequence

The routine sequence contains dynamic information that is necessary to execute an instance of the routine in the context of an SQL or SPL statement. The routine manager receives information about the UDR from the query parser. With this information, the routine manager creates a routine sequence for the associated UDR. Each instance of a UDR, implicit or explicit, in an SQL or SPL statement creates at least one independent routine sequence. Sometimes, a routine sequence consists of the single call to the UDR, as follows:
EXECUTE PROCEDURE update_log(log_name)
However, often a UDR can be invoked on more than a row. For example, in the following SELECT statement, the running_avg() function is called for each matching row of the query:
SELECT name, running_avg(price) 
FROM stock_history
WHERE running_avg(price) > 5.00

In the preceding query, the WHERE clause causes the database server to invoke two functions: the running_avg() UDR and, implicitly, the built-in greaterthan() function. The database server calls the running_avg() function for each row that it processes and executes the function in its own separate routine sequence, independent from the routine sequence for running_avg() in the SELECT clause.

For a fragmented stock_history table, the routine instance in the WHERE clause might have more than one routine sequence if running_avg() was created with the PARALLELIZABLE option. For example, if the stock_history table has four fragments, the database server uses five routine sequences to execute running_avg() in the WHERE clause:
  • One routine sequence for the primary thread
  • Four routine sequences, one for each fragment in the table, for the secondary PDQ threads

Each individual call to a UDR within a routine sequence is called a routine invocation.

The routine manager creates a routine-state space to hold UDR information that the routine sequence needs. The database server obtains this information from the query parser and passes it to the routine manager. The routine-state space holds the following information about a UDR:
  • Argument information:
    • The number of arguments passed to the UDR
    • The data types of each argument
  • Return-value information (user-defined functions only):
    • The number of return values passed from the UDR
    • The data type of each return value
Important: This argument information in the routine-state space does not include the actual argument values. It contains information only about the argument data types.

The routine-state space also includes private user-state information for use by later invocations of the routine in the same routine sequence. The UDR can use this information to optimize the subsequent invocations. The user-state information is stored in the routine-state space.

For a C UDR, the routine manager creates an MI_FPARAM structure to hold information about routine arguments and return values. The MI_FPARAM structure that the routine manager creates to hold information about routine arguments and return values can also contain a pointer to user-state information. For more information, see the topics about how to execute UDRs in the HCL OneDB™ DataBlade® API Programmer's Guide.

For a Java™ UDR, the UDREnv interface provides most of the information that MI_FPARAM provides for a C UDR. This interface has public methods for returning the SQL data types of the return values, for iterator use, and for the user-state pointer. The interface also provides facilities for logging and tracing. For more information, refer to the HCL® J/Foundation Developer's Guide.