As mentioned in Using Functions, DB2 Universal Database provides built-in and user-defined functions (UDF). However, this set of functions will never satisfy all requirements. Often, you need to create customized functions for particular tasks. User-defined functions allow you to create customized functions.
There are four types of user-defined functions: sourced (or template), external scalar, external table, and OLE DB external table.
This section covers sourced and external scalar types. For more information regarding external table and OLE DB table types, see the SQL Reference.
Sourced user-defined functions allow for user-defined types to selectively reference another built-in or user-defined function that is already known to the database. You can use both scalar and column functions.
In the next example, a user-defined function (called MAX) is created that is based on the built-in MAX column function, which takes a DECIMAL data type as input. The MAX UDF takes a PAY type as input and returns a PAY type as output.
CREATE FUNCTION MAX(PAY) RETURNS PAY
SOURCE MAX(DECIMAL)
External user-defined functions are written by users in a programming language. There are external scalar functions and external table functions and both are discussed in the SQL Reference.
As another example, assuming that you have already written a function that counts the number of words in a string, you can register it with the database using the CREATE FUNCTION statement with the name WORDCOUNT. This function can then be used in SQL statements.
The following statement returns employee numbers and the number of words in the ASCII form of their resumes. WORDCOUNT is an external scalar function that has been registered with the database by the user and is now being used in the statement.
SELECT EMPNO, WORDCOUNT(RESUME)
FROM EMP_RESUME
WHERE RESUME_FORMAT = 'ascii'
For more detailed information on writing user-defined functions, refer to the Application Development Guide.