User-Defined Routine (Server)

Home | Previous Page | Next Page DataBlade API Overview > Using the DataBlade API > DataBlade API Module >

User-Defined Routine (Server)

A user-defined routine (UDR) is a routine that you can invoke within an SQL statement or another UDR. UDRs are building blocks for the development of DataBlade modules. Possible uses for a UDR follow:

Support function for an opaque data type
Cast function to cast data from one data type to another
End-user routine for use in SQL statements
Operator function to implement an operation on a particular data type

For a more complete list, see Uses of a C UDR.

When you write a UDR in an external language (a language other than SPL), the UDR is called an external routine. An external routine that is written in the C language is called a C UDR. A C UDR uses the server-side implementation of the DataBlade API to communicate with the database server.

This section provides the following information about C UDRs. For general information about UDRs, see the IBM Informix: User-Defined Routines and Data Types Developer's Guide.

Types of UDRs

You can write the following types of C UDRs.

Type of UDR	Description	C Implementation
User-defined function	Returns one or more values and therefore can be used in SQL expressions For example, the following query returns the results of a UDR named area( ) as part of the query results: SELECT diameter, area(diameter) FROM shapes WHERE diameter > 6;	A C function that returns some data type other than void (usually a DataBlade API data type)
User-defined procedure	Does not return any values and cannot be used in SQL expressions because it does not return a value You can call a user-defined procedure directly, however, as the following example shows: EXECUTE PROCEDURE myproc(1, 5);	A C function that returns void

Type of UDR

Description

C Implementation

User-defined function

Returns one or more values and therefore can be used in SQL expressions

For example, the following query returns the results of a UDR named area( ) as part of the query results:

SELECT diameter, area(diameter)
       FROM shapes 
       WHERE diameter > 6;

A C function that returns some data type other than void (usually a DataBlade API data type)

User-defined procedure

Does not return any values and cannot be used in SQL expressions because it does not return a value

You can call a user-defined procedure directly, however, as the following example shows:

EXECUTE PROCEDURE myproc(1, 5);

A C function that returns void

Benefits of UDRs

C UDRs provide the following benefits over UDRs written in SPL:

Performance
UDRs process data on the server computer and send just the results to the client application. This division of processing often reduces the amount of data that needs to be sent to the client application.
Optimization
UDRs process just the data that needs to be processed depending on what the optimizer determines is most efficient.
Configuration management
C UDRs centralize shared code, which allows many users to access a single copy of the routine.

Using UDRs

You can write a UDR in C by using the DataBlade API functions to communicate with the database server. You can also write subroutines in C that a UDR calls as it executes. These subroutines must follow the same rules as the UDR with respect to the use of DataBlade API functions.

Tip:

Because of the subject matter of this manual, the manual uses the terms "C UDR" and "UDR" interchangeably.

You compile UDRs into shared-object files. You then register the UDR in the system catalog tables so that the database server can locate the code at runtime. The database server dynamically loads the shared-object files into memory when the UDR executes.

For more information on how to create C UDRs, see the following chapters of this manual:

Developing a User-Defined Routine, provides an overview to the development process, including information on compilation, registration, execution, and debugging.
Writing a User-Defined Routine, describes specific features and tasks of a C UDR.
Managing Memory, describes how to manage memory allocation within a C UDR.
Creating Special-Purpose UDRs, describes how to create special kinds of UDRs, such as iterator functions, user-defined aggregates, and optimization functions.