Learn how user-defined functions are used within Sybase IQ.
Sybase IQ allows user defined functions (UDFs), which execute within the database container. The UDF execution feature is available as an optional component for use within Sybase IQ or within the RAPStore component of RAP - The Trading Edition™ R3.
The use of these external C/C++ UDFs interfaces requires the IQ_UDF license.
These external C/C++ UDFs differ from the Interactive SQL UDFs available in earlier versions of Sybase IQ. Interactive SQL UDFs are unchanged and do not require a special license.
UDFs that execute within Sybase IQ take advantage of the extreme performance of the IQ server, while also providing users the flexibility of analyzing their data with the flexibility of a programmatic solution. User-Defined Functions consist of two components:
- UDF declaration, and
- UDF executable code
A UDF is declared in the SQL environment through a SQL function or stored procedure which describes the parameters and provides a reference to the external library.
The actual executable portion of the UDF is contained within an external (shared object or dynamic load) library file, which is automatically loaded by the IQ server upon the first invocation of a UDF Declaration function or stored procedure associated with that library. Once loaded, the library remains resident in the IQ server for rapid access through subsequent invocations of SQL functions or stored procedures that reference the library.
The Sybase IQ user-defined function architecture is represented in the diagram below.
Sybase IQ supports high-performance in-process external C/C++ user-defined functions. This style of UDF supports functions written in C or C++ code that adhere to the interfaces described in this guide.
The C/C++ source code for the UDFs is compiled into one or more external libraries that are subsequently loaded into the IQ server's process space when needed. The UDF calling mechanism is defined to the Sybase IQ server through a SQL function. When the SQL function is invoked from a SQL query, the IQ server loads the corresponding library if it has not already been loaded.
For simplicity of managing the UDF installation, Sybase recommends that UDF developers package many UDF functions within a single library.
To facilitate the construction of UDFs, Sybase IQ includes a C-based API. The API comprises a set of predefined entry points for the UDFs, a well-defined context data structure, and a series of SQL callback functions that provide a communication mechanism from the UDF back to the Sybase IQ server. The Sybase IQ UDF API allows software vendors and expert end-users to develop, package, and sell their own UDFs.