Executing a UDR

After you register a UDR as an external routine in the database, it can be called in one of the following ways:

• In a client application or SPL routine, through SQL statements:
  • In the select list of a SELECT statement
  • In the WHERE clause of a SELECT, UPDATE, or DELETE statement
  • In the VALUES clause of an INSERT statement
  • In the SET clause of an UPDATE statement
  • With the EXECUTE PROCEDURE or EXECUTE FUNCTION statement
• In a C UDR, as an SQL statement that one of the following DataBlade API statement-execution functions sends to the database server:
  • mi_exec( )
  • mi_exec_prepared_statement( )
  • mi_open_prepared_statement( )

  For more information on how to use statement-execution functions, see Executing SQL Statements.

• Through an implicit UDR call

  An implicit UDR is a UDR that the database server calls automatically in response to some SQL task. For example, in the following SELECT statement, the database server calls the a_to_int( ) cast function when it executes the SELECT statement:

  CREATE IMPLICIT CAST (a AS INTEGER WITH a_to_int);
  ...
  SELECT a:int FROM tab1 WHERE b > 6;

• Through the Fastpath interface

  The Fastpath interface of the DataBlade API allows you to call a UDR directly from within another UDR. For more information, see Calling UDRs with the Fastpath Interface.

Each occurrence of a UDR, implicit or explicit, in an SQL or SPL statement is a routine instance. One routine instance might involve several routine invocations. A routine invocation is one execution of the UDR. For example, if the following query selects five matching rows, the query has one routine instance of the a_func( ) user-defined function and five routine invocations for this function:

SELECT a_func(x) FROM table1 WHERE y > 7;

Similarly an iterator function might contain many invocations in a single routine instance.

To execute a UDR instance in an SQL statement, the database server takes the following steps:

1. The query parser breaks the SQL statement into its syntactic parts and performs any routine resolution required.

   The query optimizer develops a query plan, which efficiently organizes the execution of the SQL-statement parts.

2. The query executer calls the routine manager, which handles execution of the UDR instance and any invocations.

The following sections provide information about how the steps of UDR execution can affect the way that you write the UDR. For more general information, see the chapter on how a UDR runs in the IBM Informix: User-Defined Routines and Data Types Developer's Guide.