A function is an operation denoted by a function name followed by one or more operands that are enclosed in parentheses. It represents a relationship between a set of input values and a set of result values. The input values to a function are called arguments. For example, a function can be passed two input arguments that have date and time data types and return a value with a timestamp data type as the result.
There are several ways to classify functions. One way to classify functions is as built-in, user-defined, or user-defined functions that are generated for distinct types.
A user-defined function is either SQL, external, or sourced. An SQL function is defined to the database using only SQL statements. An external function is defined to the database with a reference to an external program or service program that is executed when the function is invoked. A sourced function is defined to the database with a reference to a built-in function or another user-defined function. Sourced functions can be used to extend built-in column and scalar functions for use on distinct types.
A user-defined function resides in the collection in which it was created. The collection cannot be QSYS, QSYS2, or QTEMP.
The generated cast functions reside in the same collection as the distinct type for which they were created. The collection cannot be QSYS, QSYS2, or QTEMP. For more information on the functions that are generated for a distinct type, see CREATE DISTINCT TYPE.
Another way to classify functions is as column or scalar functions, depending on the input data values and result values.
A column function receives a set of values for each argument (such as the values of a column) and returns a single-value result for the set of input values. Column functions are sometimes called aggregating functions. Built-in functions and user-defined sourced functions can be column functions.
A scalar function receives a single value for each argument and returns a single-value result. Built-in functions and user-defined functions can be scalar functions. The functions that are created for distinct types are also scalar functions.
Each reference to a scalar or column function (either built-in or user-defined) conforms to the following syntax:
>>-function-name--(--+----------+---+--------------------+---)--><
+-ALL------+ | .-,------------. |
'-DISTINCT-' | V | |
'----expression---+--'
|
The ALL or DISTINCT keyword can only be specified for a column function or a user-defined function that is sourced on a column function.
A function is invoked by its function name, which is implicitly or explicitly qualified with a collection name, followed by parentheses that enclose the arguments to the function. Within the database, each function is uniquely identified by its function signature, which is its collection name, function name, the number of parameters, and the data types of the parameters. Thus, a collection can contain several functions that have the same name but each of which have a different number of parameters, or parameters with different data types. Or, a function with the same name, number of parameters, and types of parameters can exist in multiple collections. When you invoke any function, the database manager must determine which function to execute. This process is called function resolution.
Function resolution is similar for functions that are invoked with a qualified or unqualified function name with the exception that for an unqualified name, the database manager needs to search more than one collection.
Qualified function resolution: When a function is invoked with a function name and a collection name, the database manager only searches the specified collection to resolve which function to execute. The database manager finds the appropriate function instance when all of the following conditions are true:
This comparison of data types results in one best fit, which is the choice for execution (see Method of Finding the Best Fit). For information on the promotion of data types, see Promotion of Data Types.
If no function in the collection meets these criteria, an error occurs.
Unqualified function resolution: When a function is invoked with only a function name, the database manager needs to search more than one collection to resolve the function instance to execute. The SQL path contains the list of collections to search. For each collection in the path (for information on paths see Collections and the SQL Path), the database manager selects a candidate function based on the following criteria:
This comparison of data types results in one best fit, which is the choice for execution (see Method of Finding the Best Fit). For information on the promotion of data types, see Promotion of Data Types.
If no function in the collection meets these criteria, an error occurs.
A candidate function is not selected for a collection if one or more of the criteria is not met.
After the database manager identifies the candidate functions, it selects the candidate with the best fit as the function instance to execute (see Method of Finding the Best Fit). If more than one collection contains the function instance with the best fit (the function signatures are identical except for the collection name), the database manager selects the function whose collection is earliest in the SQL path.
Function resolution applies to all functions, including built-in functions. Built-in functions logically exist in collection QSYS2. If collection QSYS2 is not explicitly specified in the SQL path, the collection is implicitly assumed at the front of the path. Therefore, when an unqualified function name is specified, ensure that the path is specified so that the intended function is selected.
There might be more than one function with the same name that is a candidate for execution. In that case, the database manager determines which function is the best fit for the invocation by comparing the argument and parameter data types. Note that neither the data type of the result of the function nor the type of function (column or scalar) under consideration enters into this determination.
If the data types of all the parameters for a given function are the same as those of the arguments in the function invocation, that function is the best fit. If there is no exact match, the database manager compares the data types in the parameter lists from left to right, using the following method:
The following examples illustrate function resolution.
Example 1: Assume that MYSCHEMA contains two functions, both named FUNA, that were created with these partial CREATE FUNCTION statements.
CREATE FUNCTION MYSCHEMA.FUNA (VARCHAR(10), INT, DOUBLE) ... CREATE FUNCTION MYSCHEMA.FUNA (VARCHAR(10), REAL, DOUBLE) ...
Also assume that a function with three arguments of data types VARCHAR(10), SMALLINT, and DECIMAL is invoked with a qualified name:
MYSCHEMA.FUNA( VARCHARCOL, SMALLINTCOL, DECIMALCOL ) ...
Both MYSCHEMA.FUNA functions are candidates for this function invocation because they meet the criteria specified in Function Resolution. The data types of the first parameter for the two function instances in the collection, which are both VARCHAR, fit the data type of the first argument of the function invocation, which is VARCHAR, equally well. However, for the second parameter, the data type of the first function (INT) fits the data type of the second argument (SMALLINT) better than the data type of second function (REAL). Therefore, the database manager selects the first MYSCHEMA.FUNA function as the function instance to execute.
Example 2: Assume that functions were created with these partial CREATE FUNCTION statements:
1. CREATE FUNCTION SMITH.ADDIT (CHAR(5), INT, DOUBLE) ... 2. CREATE FUNCTION SMITH.ADDIT (INT, INT, DOUBLE) ... 3. CREATE FUNCTION SMITH.ADDIT (INT, INT, DOUBLE, INT) ... 4. CREATE FUNCTION JOHNSON.ADDIT (INT, DOUBLE, DOUBLE) ... 5. CREATE FUNCTION JOHNSON.ADDIT (INT, INT, DOUBLE) ... 6. CREATE FUNCTION TODD.ADDIT (REAL) ... 7. CREATE FUNCTION TAYLOR.SUBIT (INT, INT, DECIMAL) ...
Also assume that the SQL path at the time an application invokes a function is "TAYLOR", "JOHNSON", "SMITH". The function is invoked with three data types (INT, INT, DECIMAL) as follows:
SELECT ... ADDIT(INTCOL1, INTCOL2, DECIMALCOL) ...
Function 5 is chosen as the function instance to execute based on the following evaluation:
Example 3: Assume that functions were created with these partial CREATE FUNCTION statements:
1. CREATE FUNCTION BESTGEN.MYFUNC (INT, DECIMAL(9,0)) ... 2. CREATE FUNCTION KNAPP.MYFUNC (INT, NUMERIC(8,0))... 3. CREATE FUNCTION ROMANO.MYFUNC (INT, FLOAT) ...
Also assume that the SQL path at the time an application invokes a function is "ROMANO", "KNAPP", "BESTGEN". The function is invoked with two data types (SMALLINT, DECIMAL) as follows:
SELECT ... MYFUNC(SINTCOL1, DECIMALCOL) ...
Function 2 is chosen as the function instance to execute based on the following evaluation:
Once the function is selected, there are still possible reasons why the use of the function may not be permitted. Each function is defined to return a result with a specific data type. If this result data type is not compatible within the context in which the function is invoked, an error will occur. For example, given functions named STEP defined with different data types as the result:
STEP(SMALLINT) RETURNS CHAR(5) STEP(DOUBLE) RETURNS INTEGER
and the following function reference (where S is a SMALLINT column):
SELECT ... 3 +STEP(S)
then, because there is an exact match on argument type, the first STEP is chosen. An error occurs on the statement because the result type is CHAR(5) instead of a numeric type as required for an argument of the addition operator.
In cases where the arguments of the function invocation were not an exact match to the data types of the parameters of the selected function, the arguments are converted to the data type of the parameter at execution using the same rules as assignment to columns (see Assignments and Comparisons). This includes the case where precision, scale, length, or CCSID differs between the argument and the parameter.