Extending Informix Dynamic Server 2000 Running a User-Defined Routine

When you invoke a routine, the database server must execute it. To execute a UDR in one of these SQL statements, the database server takes the following steps:

1. Calls the query parser to break the SQL statement into its syntactic parts
2. Calls the query optimizer to develop a query plan, which efficiently organizes the execution of the parts of the SQL statement
3. Executes the UDR:
• For external routines, the routine manager executes the UDR in the appropriate external language. The routine manager is the specific part of the database server that manages the execution of user-defined routines.
• For SPL routines, the routine manager executes the SPL p-code that the database server has compiled and stored in the sysprocbody system catalog table.

The query parser breaks the SQL statement into its syntactic parts. If the statement contains a UDR, the query parser performs the following steps on the SQL statement:

• Parses the routine call to obtain the routine signature
• Performs any necessary routine resolution on the UDR calls to determine which UDR to execute

For a description of routine resolution, refer to Understanding Routine Resolution.

Once the query parser has separated the SQL statement into its syntactic parts, the query optimizer can create a query plan for entire SQL statement. The query optimizer formulates a query plan to fetch the data rows that are required to process a query.

For more information on the query optimizer, see Optimizing a User-Defined Routine.

The routine manager performs the following steps to handle execution of external UDRs:

• Loads the shared-object files
• Creates a routine sequence
• Manages the actual execution of the UDR

To execute a user-defined routine written in an external language, its shared-object file must reside in database server memory. On the first invocation of a user-defined routine, the routine manager loads the shared-object file that contains the UDR into memory. It locates the shared-object file from the path column in the sysprocedures system catalog table.

Use the onstat command-line utility with the -g dll option to view the dynamically loaded libraries in which your user-defined routines reside. For information about the onstat command, refer to the Administrator's Reference.

Once the routine manager has loaded a shared-object file into memory, this file remains in memory until it is explicitly unloaded or the database server is shut down. For more information, see Unloading the Shared-Object File.

When you create an SPL routine or procedure, database server parses the SPL routine, compiles it, and stores the executable code in the sysprocbody system catalog table. When a statement invokes an SPL routine, the database server executes the SPL routine from the compiled code. Therefore, the routine manager does not need to load or unload SPL routines.

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 user-defined routine. Every mention of a user-defined routine, implicit or explicit, in an SQL or SPL statement creates a single independent routine sequence. Sometimes, a routine sequence consists of the single call to the user-defined routine, as follows:

EXECUTE PROCEDURE update_log(log_name)

However, often a user-defined routine 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 running_avg(price) FROM stock_history

WHERE symbol = 'IFMX'

All invocations of the running_avg() function in this query make up a single routine sequence. In the preceding query, the implicit equal() function, which the WHERE clause invokes, is also called for each matching row. This equal() function is in its own separate routine sequence. Each individual call to a UDR within a routine sequence is called a routine invocation.

Each invocation logically has a distinct routine instance. In the preceding SELECT statement, a call to running_avg() for one matching row in the stock_history table is one routine instance.

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 does not include the actual argument values. It only contains information 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 chapter on how to execute UDRs in the DataBlade API Programmer's Manual.

For a routine written in Java, the UDREnv interface provides most of the information that MI_FPARAM provides for a UDR written in C. 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 Creating UDRs in Java.

Once the routine sequence exists, the routine manager can execute the UDR, as follows:

1. It pushes arguments onto the stack for use by the routine.
2. It invokes the routine.
3. It handles the return of any UDR result.

All invocations of the same UDR within the same routine sequence have access to the same routine-state space.

Unlike an external routine, whose executable code resides in a shared-object file, the executable code for an SPL routine is stored directly in the sysprocbody system catalog table of the database. When you execute an SPL routine with the EXECUTE FUNCTION, EXECUTE PROCEDURE, or CALL statement, the database server performs the following tasks:

• Retrieves the p-code, execution plan, and dependency list from the system catalog and converts them to binary format
• Parses and evaluates the arguments passed by the EXECUTE FUNCTION, EXECUTE PROCEDURE, or CALL statement
• Checks the dependency list for each SQL statement that will be executed

If an item in the dependency list indicates that reoptimization is needed, optimization occurs at this point. If an item needed in the execution of the SQL statement is missing (for example, a column or table has been dropped), an error occurs at this time.

• The interpreter executes the p-code instructions.

For an SPL routine, the routine-state space is internal to the database server. An SPL routine with the WITH RESUME clause of the RETURN statement causes multiple executions of the same SPL routine in the same routine sequence. However, an SQL routine does not have access to the user state of its routine sequence.