A user-defined function (UDF) is a way to create SQL functions and thus add to the set of built-in functions supplied with DB2. In particular, you can create UDFs that perform operations unique to image, audio, and video objects. For example, you can create UDFs to get the compression format of a video or return the sampling rate of an audio. This provides a way of defining the behavior of objects of a particular type. Video objects, for example, behave in terms of the functions created for the video type, and image objects behave in terms of the functions created for the image type.
You create UDFs with an SQL CREATE FUNCTION statement. The statement specifies, among other things, the data type to which the UDF can be applied. For example, the following statement creates a UDF named map_scale that calculates the scale of a map. Notice that the UDF identifies map as the data type to which it can be applied. The code that implements the function is written in C and is identified in the EXTERNAL NAME clause:
CREATE FUNCTION map_scale (map) RETURNS SMALLINT EXTERNAL NAME 'scale!map' LANGUAGE C PARAMETER STYLE DB2SQL NO SQL DETERMINISTIC NO EXTERNAL ACTION
UDFs can be used in an SQL statement in the same way as built-in functions. In the following example, the map_scale UDF is used in an SQL SELECT statement to return the scale of a map stored in a table column named grid:
SELECT map_scale (grid) FROM places WHERE location='SAN JOSE, CALIFORNIA'
Each DB2 extender creates a set of UDFs for its type, that is, image-specific, audio-specific, and video-specific UDFs. You use these UDFs in SQL statements to request extender functions such as storing an image in a table, getting the frame rate of a video, or adding comments about an audio.