Sourced functions

A sourced function is a function that invokes another function that already exists at the server. The function inherits the attributes of the underlying source function. The source function can be built-in, external, SQL, or sourced. Sourced functions can be used to extend built-in aggregate and scalar functions for use on distinct types.

You can use sourced functions to build upon existing built-in functions or other user-defined functions. Sourced functions are useful to extend built-in aggregate and scalar functions for use on distinct types.

To implement a sourced function, issue a CREATE FUNCTION statement and identify the function upon which you want to base the sourced function in the SOURCE clause.

Example: Definition of a sourced user-defined function

Suppose you need a user-defined function that finds a string in a value with a distinct type of BOAT. BOAT is a distinct type based on a BLOB data type. User-defined function FINDSTRINGBLOB has already been defined to take a BLOB data type as input and perform the required function, but it cannot be invoked with a value of the BOAT data type. The specific name for FINDSTRING is FINDSTRINGBLOB.

You can define a sourced user-defined function based on FINDSTRING to do the string search on values of type BOAT. Db2 implicitly casts the BOAT argument to a BLOB when the source function, FINDSTRING that accepts a BLOB value, is invoked. This CREATE FUNCTION statement defines the sourced user-defined function:

CREATE FUNCTION FINDSTRING (BOAT, VARCHAR(200))
  RETURNS INTEGER
  SPECIFIC FINDSTRINGBOAT
  SOURCE SPECIFIC FINDSTRINGBLOB;