The following is a discussion of the important concepts you need to know prior to coding UDFs and methods:
The full name of a function is <schema-name>.<function-name>. You can use this full name anywhere you refer to a function. For example:
SLICKO.BOAT_COMPARE SMITH.FOO SYSIBM.SUBSTR SYSFUN.FLOOR
However, you may also omit the <schema-name>., in which case, DB2 must identify the function to which you are referring. For example:
BOAT_COMPARE FOO SUBSTR FLOOR
The concept of function path is central to DB2's resolution of unqualified references that occur when you do not use the schema-name. For the use of function path in DDL statements that refer to functions, refer to the SQL Reference. The function path is an ordered list of schema names. It provides a set of schemas for resolving unqualified function references to UDFs and methods as well as UDTs. In cases where a function reference matches functions in more than one schema in the path, the order of the schemas in the path is used to resolve this match. The function path is established by means of the FUNCPATH option on the precompile and bind commands for static SQL. The function path is set by the SET CURRENT FUNCTION PATH statement for dynamic SQL. The function path has the following default value:
"SYSIBM","SYSFUN","<ID>"
This applies to both static and dynamic SQL, where <ID> represents the current statement authorization ID.
Function names can be overloaded, which means that multiple functions, even in the same schema, can have the same name. Two functions cannot, however, have the same signature, which can be defined to be the qualified function name concatenated with the defined data types of all the function parameters in the order in which they are defined. For an example of an overloaded function, see Example: BLOB String Search.
It is the function selection algorithm that takes into account the facts of overloading and function path to choose the best fit for every function reference, whether it is a qualified or an unqualified reference. Even references to the built-in functions and the functions (also IBM-supplied) in the SYSFUN schema are processed through the function selection algorithm.
Each user-defined function is classified as a scalar, column or table function. A scalar function returns a single value answer each time it is called. For example, the built-in function SUBSTR() is a scalar function. Scalar UDFs and methods can either be external (coded in a programming language such as C), or sourced (using the implementation of an existing function).
A column function receives a set of like values (a column of data) and returns a single value answer from this set of values. These are also called aggregating functions in DB2. An example of a column function is the built-in function AVG(). An external column UDF cannot be defined to DB2, but a column UDF that is sourced on one of the built-in column functions can be defined. This is useful for distinct types. For example, if a distinct type SHOESIZE exists that is defined with base type INTEGER, you could define a UDF, AVG(SHOESIZE), as a column function sourced on the existing built-in column function, AVG(INTEGER).
A table function returns a table to the SQL statement that references it. A table function can only be referenced in the FROM clause of a SELECT statement. Such a function can be used to apply the SQL language to non-DB2 data, or to capture such data and put it into a DB2 table. For example, it could dynamically convert a file consisting of non-DB2 data into a table, or it could retrieve data from the World Wide Web or an operating system and and return the data as a table. A table function can only be an external function.
The concept of function path, the SET CURRENT FUNCTION PATH statement, and the function selection algorithm are discussed in detail in the SQL Reference. The FUNCPATH precompile and bind options are discussed in detail in the Command Reference.
For information about the concept of mapping UDFs and methods and built-in functions to data source functions in a federated system, refer to the SQL Reference. For guidelines on creating such mappings, refer to Invoking Data Source Functions.