DB2 Version 9.7 for Linux, UNIX, and Windows

Functions

A function is an operation denoted by a function name followed by one or more operands that are enclosed in parentheses. A function 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, the TIMESTAMP function can be passed arguments of type DATE and TIME, and the result is a TIMESTAMP.

There are several ways to classify functions.

One way is to classify functions as ether built-in or user-defined.

Built-in functions are provided with the database manager. They return a single result value and are identified as part of the SYSIBM schema. Such functions include aggregate functions (for example, AVG), operator functions (for example, +), casting functions (for example, DECIMAL), and scalar functions (for example, CEILING).
User-defined functions are functions that are created using an SQL data definition statement and registered to the database manager in the catalog. User-defined schema functions are created using the CREATE FUNCTION statement. For more information, see "CREATE FUNCTION". A set of user-defined schema functions is provided with the database manager in a schema called SYSFUN. User-defined module functions are created using the ALTER MODULE ADD FUNCTION or ALTER MODULE PUBLISH FUNCTION statements. For more information, see "ALTER MODULE". A set of user-defined module functions is provided with the database manager in a set of modules in a schema called SYSIBMADM. A user-defined function resides in the schema in which it was created or in the module where it was added or published.
User-defined functions extend the capabilities of the database system by adding function definitions (provided by users or third party vendors) that can be applied in the database engine itself. Extending database functions lets the database exploit the same functions in the engine that an application uses, providing more synergy between application and database.

Another way to classify a user-defined function is as an external function, an SQL function, or a sourced function.

An external function is defined to the database with a reference to an object code library, and a function within that library that will be executed when the function is invoked. External functions cannot be aggregate functions.
An SQL function is defined to the database using only SQL statements, including at least one RETURN statement. It can return a scalar value, a row, or a table.
SQL functions cannot be aggregate functions. A sourced function is defined to the database with a reference to another built-in or user-defined function that is already known to the database. Sourced functions can be scalar functions or aggregate functions. They are useful for supporting existing functions with user-defined types.

Another way to classify functions is as a scalar, aggregate, row, or table functions, depending on the input data values and result values.

A scalar function is a function that returns a single-valued answer each time it is called. For example, the built-in function SUBSTR() is a scalar function. Scalar UDFs can be either external or sourced.
An aggregate function is one which conceptually is passed a set of like values (a column) and returns a single-valued answer. An example of an aggregate function is the built-in function AVG(). An external column UDF cannot be defined to DB2®, but a column UDF, which is sourced upon one of the built-in aggregate functions, can be defined. This is useful for distinct types. For example, if there is a distinct type SHOESIZE defined with base type INTEGER, a UDF AVG(SHOESIZE), which is sourced on the built-in function AVG(INTEGER), could be defined, and it would be an aggregate function.
A row function is a function that returns one row of values. It can be used in a context where a row expression is supported. It can also be used as a transform function, mapping attribute values of a structured type into values in a row. A row function must be defined as an SQL function.
A table function is a function that returns a table to the SQL statement which references it. It may only be referenced in the FROM clause of a SELECT statement. Such a function can be used to apply SQL language processing power to data that is not DB2 data, or to convert such data into a DB2 table. A table function can read a file, get data from the Web, or access a Lotus Notes® database and return a result table. This information can be joined with other tables in the database. A table function can be defined as an external function or as an SQL function. (A table function cannot be a sourced function.)

Function signatures

A schema function is identified by its schema name, a function name, the number of parameters, and the data types of its parameters. A module function is identified by its schema name, module name, a function name, the number of parameters, and the data types of its parameters. This identification of a schema function or a module function is called a function signature, which must be unique within the database; for example, TEST.RISK(INTEGER). There can be more than one function with the same name in a schema or a module, provided that the number of parameters or the data types of the parameters are different. A function name for which there are multiple function instances with the same number of parameters is called an overloaded function. A function name can be overloaded within a schema, in which case there is more than one function by that name with the same number of parameters in the schema. Similarly, a function name can be overloaded within a module, in which case there is more than one function by that name with the same number of parameters in the module. These functions must have different parameter data types. Functions can also be overloaded across the schemas of an SQL path, in which case there is more than one function by that name with the same number of parameters in different schemas of the SQL path. These functions do not necessarily have different parameter data types.

Function invocation

Each reference to a function conforms to the following syntax:


                      (1)                                            
>>-function-name--(------+----------+--+----------------------+--)-><
                         +-ALL------+  | .-,----------------. |      
                         '-DISTINCT-'  | V                  | |      
                                       '-----| argument |---+-'      

argument

|--+--------------------+--+-expression-----+-------------------|
   '-parameter-name--=>-'  +-row-expression-+   
                           '-DEFAULT--------'

Notes:

The ALL or DISTINCT keyword can be specified only for an aggregate function or a user-defined function that is sourced on an aggregate function.

In the above syntax, expression and row-expression cannot include an aggregate function. See "Expressions" for other rules for expression.

A function is invoked by referring (in an allowable context) to its qualified or unqualified function name followed by the list of arguments enclosed in parentheses. The possible qualifiers for a function name are:

A schema name
An unqualified module name
A schema-qualified module name

The qualifier used when invoking a function determines the scope used to search for a matching function.

If a schema-qualified module name is used as the qualifier, the scope is the specified module.
If a single identifier is used as the qualifier, the scope includes:
- The schema that matches the qualifier
- One of the following modules:
  - The invoking module, if the invoking module name matches the qualifier
  - The first module in a schema in the SQL path that matches the qualifier
If no qualifier is used, the scope includes the schemas in the SQL path and, if the function is invoked from within a module object, the same module from which the function is invoked.

For static SQL statements, the SQL path is specified using the FUNCPATH bind option. For dynamic SQL statements, the SQL path is the value of the CURRENT PATH special register.

When any function is invoked, the database manager must determine which function to execute. This process is called function resolution and applies to both built-in and user-defined functions. It is recommended that function invocations intending to invoke a user-defined function be fully qualified. This improves performance of function resolution and prevents unexpected function resolution results as new functions are added or privileges granted.

An argument is a value passed to a function upon invocation or the specification of DEFAULT. When a function is invoked in SQL, it is passed a list of zero or more arguments. They are positional in that the semantics of an argument are determined by its position in the argument list. A parameter is a formal definition of an input to a function or an output from a function. When a function is defined to the database, either internally (a built-in function) or by a user (a user-defined function), its parameters (zero or more) are specified, and the order of their definitions defines their positions and their semantics. Therefore, every parameter is a particular positional input to a function or an output from a function. On invocation, an argument is assigned to a parameter using either the positional syntax or the named syntax. If using the positional syntax, an argument corresponds to a particular parameter according to its position in the list of arguments. If using the named syntax, an argument corresponds to a particular parameter by the name of the parameter. When an argument is assigned to a parameter using the named syntax, then all the arguments that follow it must also be assigned using the named syntax (SQLSTATE 4274K). The name of a named argument can appear only once in a function invocation (SQLSTATE 4274K). In cases where the data types of the arguments of the function invocation are not a 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 time using the same rules as assignment to columns. This includes the case where precision, scale, or length differs between the argument and the parameter. In cases where the arguments of the function invocation are the specification of DEFAULT, the actual value used for the argument is the value specified as the default for the corresponding parameter in the function definition. If no default value was defined for the parameter, the null value is used. If an untyped expression (a parameter marker, a NULL keyword, or a DEFAULT keyword) is used as the argument, the data type associated with the argument is determined by the parameter data type of the parameter of the selected function.

Access to schema functions is controlled through the EXECUTE privilege on the schema functions. If the authorization ID of the statement invoking the function does not have EXECUTE privilege, the schema function will not be considered by the function resolution algorithm, even if it is a better match. Built-in functions (SYSIBM functions) and functions in the SYSFUN schema have the EXECUTE privilege implicitly granted to PUBLIC.

Access to module functions is controlled through EXECUTE privilege on the module for all functions within the module. The authorization ID of the statement invoking the function might not have EXECUTE privilege on a module. In such cases, module functions within that module, unlike schema functions, are still considered by the function resolution algorithm even though they cannot be executed.

When the user-defined function is invoked, the value of each of its arguments is assigned, using storage assignment, to the corresponding parameter of the function. Control is passed to external functions according to the calling conventions of the host language. When execution of a user-defined scalar function or a user-defined aggregate function is complete, the result of the function is assigned, using storage assignment, to the result data type. For details on the assignment rules, see "Assignments and comparisons".

Table functions can be referenced only in the FROM clause of a subselect. For more details on referencing a table function, see "table-reference".

Function resolution

After a function is invoked, the database manager must determine which function to execute. This process is called function resolution and applies for both built-in and user-defined functions.

The database manager first determines the set of candidate functions based on the following information:

The qualification of the name of the invoked function
The context that invokes the function
The unqualified name of the invoked function
The number of arguments specified
Any argument names that are specified
The authorization of schema functions.

See Determining the set of candidate functions for details.

The database manager then determines the best fit from the set of candidate functions based on the data types of the arguments of the invoked function as compared with the data types of the parameters of the functions in the set of candidate functions. The SQL path and number of parameters is also considered. See Determining the best fit for details.

Once a function is selected, it is still possible for an error to be returned for one of the following reasons:

If a module function is selected and either the function is invoked from outside a module or the function is invoked from within a module object and the qualifier does not match the context module name, the authorization ID of the statement that invoked the function must have EXECUTE privilege on the module that contains the selected function (SQLSTATE 42501).
If a function is selected, its successful use depends on it being invoked in a context in which the returned result is allowed. For example, if the function returns a table where a table is not allowed, an error is returned (SQLSTATE 42887).
If a cast function is selected, either built-in or user-defined, and any argument would need to be implicitly cast (not promoted) to the data type of the parameter, an error is returned (SQLSTATE 42884).
If a function invocation involves an argument with an unnamed row type, an error is returned (SQLSTATE 42884) if either of the following conditions occur:
- The number of fields of the argument does not match the number of fields of the parameter.
- The data types of the fields of the argument are not assignable to the corresponding data type of the fields of the parameter.

Determining the set of candidate functions

Let A be the number of arguments in a function invocation.
Let P be the number of parameters in a function signature.
Let N be the number of parameters in a function signature without a defined default.

Candidate functions for resolution of a function invocation are selected based on the following criteria:

Each candidate schema function has a matching name and applicable number of parameters. An applicable number of parameters satisfies the condition N ≤ A ≤ P.
Each candidate module function has parameters such that for each named argument in the function invocation there exists a parameter with a matching name that does not already correspond to a positional (unnamed) argument.
Each parameter of a candidate function that does not have a corresponding argument in the function invocation, specified by either position or name, is defined with a default.
Each candidate function from a set of one or more schemas has the EXECUTE privilege associated with the authorization ID of the statement invoking the function.
Each candidate function from a module other than the context module is a published module function.

The functions selected for the set of candidate functions are from one or more of the following search spaces.

The context module, that is, the module which contains the module object that invoked the function
A set of one or more schemas
A module other than the context module

The specific search spaces considered are affected by the qualification of the name of the invoked function.

Qualified function invocation: When a function is invoked with a function name and a qualifier, the database manager uses the qualifier and, in some cases, the context of the invoked function to determine the set of candidate functions.
1. If a function is invoked from within a module object using a function name with a qualifier, the database manager considers if the qualifier matches the context module name. If the qualifier is a single identifier, then the schema name of the module is ignored when determining a match. If the qualifier is a two-part identifier, then it is compared to the schema-qualified module name when determining a match. If the qualifier matches the context module name, the database manager searches the context module for candidate functions.
  If one or more candidate functions are found in the context module, then this set of candidate functions is processed for best fit without consideration of possible candidate functions in any other search space (see "Determining the best fit"). Otherwise, continue to the next search space.
2. If the qualifier is a single identifier, the database manager considers the qualifier as a schema name and searches that schema for candidate functions.
  If one or more candidate functions are found in the schema, then this set of candidate functions is processed for best fit without consideration of possible candidate functions in any other search space (see "Determining the best fit"). Otherwise, continue to the next search space, if applicable.
3. If the function is invoked from outside a module or the qualifier does not match the context module name when it is invoked from within a module object, the database manager considers the qualifier as a module name. Without considering EXECUTE privilege on modules, the database manager then selects the first module that matches based on the following criteria:
  - If the module name is qualified with a schema name, select the module with that schema name and module name.
  - If the module name is not qualified with a schema name, select the module with that module name that is found in the schema earliest in the SQL path.
  - If the module is not found using the SQL path, select the module public alias with that module name.
  If a matching module is not found, then there are no candidate functions. If a matching module is found, the database manager searches the selected module for candidate functions.
  If one or more candidate functions are found in the selected modules, then this set of candidate functions is processed for best fit (see "Determining the best fit").
Unqualified function invocation: When a function is invoked without a qualifier, the database manager considers the context of the invoked function to determine the sets of candidate functions.
1. If a function is invoked with an unqualified function name from within a module object, the database manager searches the context module for candidate functions.
  If one or more candidate functions are found in the context module, then these candidate functions are included with any candidate functions from the schemas in the SQL path (see next item).
2. If a function is invoked with an unqualified function name, either from within a module object or from outside a module, the database manager searches the list of schemas in the SQL path to resolve the function instance to execute. For each schema in the SQL path (see "SQL path"), the database manager searches the schema for candidate functions.
  If one or more candidate functions are found in the schemas in the SQL path, then these candidate functions are included with any candidate functions from the context module (see previous item). This set of candidate functions is processed for best fit (see "Determining the best fit").

If the database manager does not find any candidate functions, an error is returned (SQLSTATE 42884).

Determining the best fit

The set of candidate functions may contain one function or more than one function with the same name. In either case, the data types of the parameters, the position of the schema in the SQL path, and the total number of parameters of each function in the set of candidate functions are used to determine if the function meets the best fit requirements.

If the set of candidate functions contains more than one function and named arguments are used in the function invocation, the ordinal position of the parameter corresponding to a named argument must be the same for all candidate functions (SQLSTATE 4274K).

The term set of parameters is used to refer to all of the parameters at the same position in the parameter lists (where such a parameter exists) for the set of candidate functions. The corresponding argument of a parameter is determined based on how the arguments are specified in the function invocation. For positional arguments, the corresponding argument to a parameter is the argument in the same position in the function invocation as the position of the parameter in the parameter list of the candidate function. For named arguments, the corresponding argument to a parameter is the argument with the same name as the parameter. In this case, the order of the arguments in the function invocation is not considered while determining the best fit. If the number of parameters in a candidate function is greater than the number of arguments in the function invocation, each parameter that does not have a corresponding argument is processed as if it does have a corresponding argument that has the DEFAULT keyword as the value.

The following steps are used to determine the function that is the best fit:

Step 1: Considering arguments that are typed expressions

The database manager determines the function, or set of functions, that meet the best fit requirements for the invocation by comparing the data type of each parameter with the data type of the corresponding argument.

When determining whether the data type of a parameter is the same as the data type of its corresponding argument:

Synonyms of data types match. For example, FLOAT and DOUBLE are considered to be the same.
Attributes of a data type such as length, precision, scale, and code page are ignored. Therefore, CHAR(8) and CHAR(35) are considered to be the same, as are DECIMAL(11,2), and DECIMAL(4,3).

A subset of the candidate functions is obtained by considering only those functions for which the data type of each argument of the function invocation that is not an untyped expression matches or is promotable to the data type of the corresponding parameter of the function instance. If the argument of the function invocation is an untyped expression, the data type of the corresponding parameter can be any data type. The precedence list for the promotion of data types in Promotion of data types shows the data types that fit (considering promotion) for each data type in best-to-worst order. If this subset is not empty, then the best fit is determined using the Promotable process on this subset of candidate functions. If this subset is empty, then the best fit is determined using the Castable process on the original set of candidate functions.

Promotable process

This process determines the best fit only considering whether arguments in the function invocation match or can be promoted to the data type of the corresponding parameter of the function definition. For the subset of candidate functions, the parameter lists are processed from left to right, processing the set of parameters in the first position from the subset of candidate functions before moving on to the set of parameters in the second position, and so on. The following steps are used to eliminate candidate functions from the subset of candidate functions (only considering promotion):

If one candidate function has a parameter where the data type of the corresponding argument fits (only considering promotion) the data type of the parameter better than other candidate functions, those candidate functions that do not fit the function invocation equally well are eliminated. The precedence list for the promotion of data types in Promotion of data types shows the data types that fit (considering promotion) for each data type in best-to-worst order.
If the data type of the corresponding argument is an untyped expression, no candidate functions are eliminated.
These steps are repeated for the next set of parameters from the remaining candidate functions until there are no more sets of parameters.

Castable process

This process determines the best fit first considering, for each parameter, if the data type of the corresponding argument in the function invocation matches or can be promoted to the data type of the parameter of the function definition. Then, for each set of parameters where no corresponding argument has a data type that was promotable, the database manager considers, for each parameter, if the data type of the corresponding argument can be implicitly cast for function resolution to the data type of the parameter.

For the set of candidate functions, the parameters in the parameter lists are processed from left to right, processing the set of parameters in the first position from all the candidate functions before moving on to the set of parameters in the second position, and so on. The following steps are used to eliminate candidate functions from the set of candidate functions (only considering promotion):

If one candidate function has a parameter where the data type of the corresponding argument fits (only considering promotion) the data type of the parameter better than other candidate functions, those candidate functions that do not fit the function invocation equally well are eliminated. The precedence list for the promotion of data types in Promotion of data types shows the data types that fit (considering promotion) for each data type in best-to-worst order.
If the data type for the corresponding argument is not promotable (which includes the case when the corresponding argument is an untyped expression) to the data type of the parameter of any candidate function, no candidate functions are eliminated.
These steps are repeated for the next set of parameters from the remaining candidate functions until there are no more sets of parameters.

If at least one set of parameters has no corresponding argument that fit (only considering promotion) and the corresponding argument for the set of parameters has a data type, the database manager compares each such set of parameters from left to right. The following steps are used to eliminate candidate functions from the set of candidate functions (considering implicit casting).

If all the data types of the set of parameters for all remaining candidate functions do not belong to the same data type precedence list, as specified in Promotion of data types, an error is returned (SQLSTATE 428F5).
If the data type of the corresponding arguments cannot be implicitly cast to the data type of the parameters, as specified in Implicit casting for function resolution, an error is returned (SQLSTATE 42884).
If one candidate function has a parameter where the data type of the corresponding argument fits (considering implicit casting) the data type of the parameter better than other candidate functions, those candidate functions that do not fit the function invocation equally well are eliminated. The data type list in Implicit casting for function resolution shows the data type that fits (considering implicit casting) better.
These steps are repeated for the next set of parameters which has no corresponding argument that fit (only considering promotion) and the corresponding argument for the set of parameters has a data type until there are no more such sets of parameters or an error occurs.

Step 2: Considering SQL path

If more than one candidate function remains and a context module exists that still includes candidate functions, the database manager selects those functions. If there is no context module or no candidate functions remain in the context module, the database manager selects those candidate functions whose schema is earliest in the SQL path.

Step 3: Considering number of arguments in the function invocation

If more than one candidate function remains and if one candidate function has a number of parameters that is less than or equal to the number of parameters of the other candidate functions, those candidate functions that have a greater number of parameters are eliminated.

Step 4: Considering arguments that are untyped expressions

If more than one candidate function remains and at least one set of parameters has a corresponding argument that is an untyped expression, the database manager compares each such set of parameters from left to right. The following steps are used to eliminate candidate functions from the set of candidate functions:

If all the data types of the set of parameters for all remaining candidate functions do not belong to the same data type precedence list, as specified in Promotion of data types, an error is returned (SQLSTATE 428F5).
If the data type of the parameter of one candidate function is further left in the data type ordering for implicit casting than other candidate functions, those candidate functions where the data type of the parameter is further right in the data type ordering are eliminated. The data type list in "Implicit casting for function resolution" shows the data type ordering for implicit casting.

If there are still multiple candidate functions, an error is returned (SQLSTATE 428F5).

Implicit casting for function resolution

Implicit casting for function resolution is not supported for arguments with a user-defined type, reference type, or XML data type. It is also not supported for built-in or user-defined cast functions. It is supported for the following cases:

A value of one data type can be cast to any other data type that is in the same data type precedence list, as specified in Promotion of data types.
A numeric or datetime data type can be cast to a character or graphic string data type, except for LOBs
A character or graphic string type, except LOBs, can be cast to a numeric or datetime data type
A character FOR BIT DATA can be cast to a BLOB and a BLOB can be cast to a character FOR BIT DATA
A TIMESTAMP data type can be cast to a TIME data type
An untyped argument can be cast to any data type.

Similar to the data type precedence list for promotion, for implicit casting there is an order to the data types that are in the group of related data types. This order is used when performing function resolution that considers implicit casting. Table 1 shows the data type ordering for implicit casting for function resolution. The data types are listed in best-to-worst order (note that this is different than the ordering in the data type precedence list for promotion). Note, when function resolution selects a built-in function and implicit casting is necessary for some argument, if the built-in function supports both character input and graphic input for the parameter, the argument is implicitly cast to character.

Table 1. Data type ordering for implicit casting for function resolution
Data type group	Data type list for implicit casting for function resolution (in best-to-worst order)
Numeric data types	DECFLOAT, double, real, decimal, BIGINT, INTEGER, SMALLINT
Character and graphic string data types	VARCHAR or VARGRAPHIC, CHAR or GRAPHIC, CLOB or DBCLOB
Datetime data types	TIMESTAMP, DATE

Notes:

The lower case types above are defined as follows:
- decimal = DECIMAL (p,s) or NUMERIC(p,s)
- real = REAL or FLOAT(n) where n is not greater than 24
- double = DOUBLE, DOUBLE-PRECISION, FLOAT or FLOAT(n), where n is greater than 24
Shorter and longer form synonyms of the listed data types are considered to be the same as the listed form.
For a Unicode database only, the following are considered to be equivalent data types:
- CHAR or GRAPHIC
- VARCHAR and VARGRAPHIC
- CLOB and DBCLOB

Table 2. Derived length of an argument when invoking a built-in scalar function in cases where implicit casting is needed.
Source Data Type	Target Type and Length
Source Data Type	CHAR	GRAPHIC	VARCHAR	VARGRAPHIC	CLOB	DBCLOB	BLOB	TIMESTAMP	DECFLOAT
UNTYPED	127	127	254	254	32767	32767	32767	12	34
SMALLINT	6	6	6	6	-	-	-	-	-
INTEGER	11	11	11	11	-	-	-	-	-
BIGINT	20	20	20	20	-	-	-	-	-
DECIMAL(`p`,`s`)	2+`p`	2+`p`	2+`p`	2+`p`	-	-	-	-	-
REAL	24	24	24	24	-	-	-	-	-
DOUBLE	24	24	24	24	-	-	-	-	-
DECFLOAT	42	42	42	42	-	-	-	-	-
CHAR(`n`)	-	-	-	-	-	-	min(`n`,254)	12	34
VARCHAR(`n`)	min(`n`,254)	min(`n`,127)	-	-	-	-	min(`n`,32672)	12	34
CLOB(`n`)	min(`n`,254)	min(`n`,127)	min(`n`,32672)	min(`n`,16336)	-	-	-	-	-
GRAPHIC(`n`)	-	-	-	-	-	-	-	12	34
VARGRAPHIC(`n`)	min(`n`,254)	min(`n`,127)	-	-	-	-	-	12	34
DBCLOB(`n`)	min(`n`,254)	min(`n`,127)	min(`n`,32672)	min(`n`,16336)	-	-	-	-	-
BLOB(`n`)	min(`n`,254)	-	min(`n`,32672)	-	-	-	-	-	-
TIME	8	8	8	8	-	-	-	-	-
DATE	10	10	10	10	-	-	-	-	-
TIMESTAMP(`p`)	if `p`=0 then 19 else `p`+20	if `p`=0 then 19 else `p`+20	if `p`=0 then 19 else `p`+20	if `p`=0 then 19 else `p`+20	-	-	-	-	-

SQL path considerations for built-in functions

Built-in functions reside in a special schema called SYSIBM. Additional functions are available in the SYSFUN, SYSPROC, and SYSIBMADM schemas as well as in modules within the SYSIBMADM schema, but are not considered built-in functions because they are developed as user-defined functions and have no special processing considerations. Users cannot define additional functions in the SYSIBM, SYSFUN, SYSPROC, or SYSIBMADM schemas (or in any other schema whose name begins with the letters 'SYS', except SYSTOOLS).

As already stated, the built-in functions participate in the function resolution process exactly as do the user-defined functions. One difference between built-in and user-defined functions, from a function resolution perspective, is that the built-in functions must always be considered during function resolution. Therefore, omission of SYSIBM from the path results in the assumption (for function and data type resolution) that SYSIBM is the first schema on the path.

For example, if a user's SQL path is defined as:

"SHAREFUN","SYSIBM","SYSFUN"

and there is a LENGTH function defined in schema SHAREFUN with the same number and types of arguments as SYSIBM.LENGTH, then an unqualified reference to LENGTH in this user's SQL statement will result in selecting SHAREFUN.LENGTH. However, if the user's SQL path is defined as:

"SHAREFUN","SYSFUN"

and the same SHAREFUN.LENGTH function exists, then an unqualified reference to LENGTH in this user's SQL statement will result in selecting SYSIBM.LENGTH, because SYSIBM implicitly appears first in the path.

To minimize potential problems in this area:

Never use the names of built-in functions for user-defined functions.
If, for some reason, it is necessary to create a user-defined function with the same name as a built-in function, be sure to qualify any references to it.

Note: Some invocations of built-in functions do not support SYSIBM as an explicit qualifier and resolve directly to the built-in function without considering the SQL path. Specific cases are covered in the description of the built-in function.

Examples of function resolution

The following are examples of function resolution. (Note that not all required keywords are shown.)

This is an example illustrating the SQL path considerations in function resolution. For this example, there are eight ACT functions, in three different schemas, registered as:
```
CREATE FUNCTION AUGUSTUS.ACT (CHAR(5), INT, DOUBLE) SPECIFIC ACT_1 ...
CREATE FUNCTION AUGUSTUS.ACT (INT, INT, DOUBLE) SPECIFIC ACT_2 ...
CREATE FUNCTION AUGUSTUS.ACT (INT, INT, DOUBLE, INT) SPECIFIC ACT_3 ...
CREATE FUNCTION JULIUS.ACT (INT, DOUBLE, DOUBLE) SPECIFIC ACT_4 ...
CREATE FUNCTION JULIUS.ACT (INT, INT, DOUBLE) SPECIFIC ACT_5 ...
CREATE FUNCTION JULIUS.ACT (SMALLINT, INT, DOUBLE) SPECIFIC ACT_6 ...
CREATE FUNCTION JULIUS.ACT (INT, INT, DECFLOAT) SPECIFIC ACT_7 ...
CREATE FUNCTION NERO.ACT (INT, INT, DEC(7,2)) SPECIFIC ACT_8 ...
```
The function reference is as follows (where I1 and I2 are INTEGER columns, and D is a DECIMAL column):
```
SELECT ... ACT(I1, I2, D) ...
```
Assume that the application making this reference has an SQL path established as:
```
"JULIUS","AUGUSTUS","CAESAR"
```
Following through the algorithm...
- The function with specific name ACT_8 is eliminated as a candidate, because the schema NERO is not included in the SQL path.
- The function with specific name ACT_3 is eliminated as a candidate, because it has the wrong number of parameters. ACT_1 and ACT_6 are eliminated because, in both cases, the first argument cannot be promoted to the data type of the first parameter.
- Because there is more than one candidate remaining, the arguments are considered in order.
- For the first argument, the remaining functions, ACT_2, ACT_4, ACT_5, and ACT_7 are an exact match with the argument type. No functions can be eliminated from consideration; therefore the next argument must be examined.
- For this second argument, ACT_2, ACT_5, and ACT_7 are exact matches, but ACT_4 is not, so it is eliminated from consideration. The next argument is examined to determine some differentiation among ACT_2, ACT_5, and ACT_7.
- For the third and last argument, neither ACT_2, ACT_5, nor ACT_7 match the argument type exactly. Although ACT_2 and ACT_5 are equally good, ACT_7 is not as good as the other two because the type DOUBLE is closer to DECIMAL than is DECFLOAT. ACT_7 is eliminated..
- There are two functions remaining, ACT_2 and ACT_5, with identical parameter signatures. The final tie-breaker is to see which function's schema comes first in the SQL path, and on this basis, ACT_5 is the function chosen.
This is an example of a situation where function resolution will result in an error (SQLSTATE 428F5) since more than one candidate function fits the invocation equally well, but the corresponding parameters for one of the arguments do not belong to the same type precedence list.
For this example, there are only three function in a single schema defined as follows:
```
CREATE FUNCTION CAESAR.ACT (INT, VARCHAR(5), VARCHAR(5))SPECIFIC ACT_1 ...
CREATE FUNCTION CAESAR.ACT (INT, INT, DATE)     SPECIFIC ACT_2 ...
CREATE FUNCTION CAESAR.ACT (INT, INT, DOUBLE)   SPECIFIC ACT_3 ...
```
The function reference is as follows (where I1 and I2 are INTEGER columns, and VC is a VARCHAR column):
```
SELECT ... ACT(I1, I2, VC) ...
```
Assume that the application making this reference has an SQL path established as:
```
"CAESAR"
```
Following through the algorithm ...
- Each of the candidate functions is evaluated to determine if the data type of each input argument of the function invocation matches or is promotable to the data type of the corresponding parameter of the function instance:
  - For the first argument, all the candidate functions have an exact match with the parameter type.
  - For the second argument, ACT_1 is eliminated because INTEGER is not promotable to VARCHAR.
  - For the third argument, both ACT_2 and ACT_3 are eliminated since VARCHAR is not promotable to DATE or DOUBLE, so no candidate functions remain.
- Since the subset of candidate functions from above is empty, the candidate functions are considered using the castable process:
  - For the first argument, all the candidate functions have an exact match with the parameter type.
  - For the second argument, ACT_1 is eliminated since INTEGER is not promotable to VARCHAR. ACT_2 and ACT_3 are better candidates.
  - For the third argument, the data type of the corresponding parameters of ACT_2 and ACT_3 do not belong to the same data type precedence list, so an error is returned (SQLSTATE 428F5).
This example illustrates a situation where function resolution will succeed using the castable process. For this example, there are only three function in a single schema defined as follows:
```
CREATE FUNCTION CAESAR.ACT (INT, VARCHAR(5), VARCHAR(5))SPECIFIC ACT_1 ...
CREATE FUNCTION CAESAR.ACT (INT, INT, DECFLOAT)     SPECIFIC ACT_2 ...
CREATE FUNCTION CAESAR.ACT (INT, INT, DOUBLE)   SPECIFIC ACT_3 ...
```
The function reference is as follows (where I1 and I2 are INTEGER columns, and VC is a VARCHAR column):
```
SELECT ... ACT(I1, I2, VC) ...
```
Assume that the application making this reference has an SQL path established as:
```
"CAESAR"
```
Following through the algorithm ...
- Each of the candidate functions is evaluated to determine if the data type of each input argument of the function invocation matches or is promotable to the data type of the corresponding parameter of the function instance:
  - For the first argument, all the candidate functions have an exact match with the parameter type.
  - For the second argument, ACT_1 is eliminated because INTEGER is not promotable to VARCHAR.
  - For the third argument, both ACT_2 and ACT_3 are eliminated since VARCHAR is not promotable to DECFLOAT or DOUBLE, so no candidate functions remain.
- Since the subset of candidate functions from above is empty, the candidate functions are considered using the castable process:
  - For the first argument, all the candidate functions have an exact match with the parameter type.
  - For the second argument, ACT_1 is eliminated since INTEGER is not promotable to VARCHAR. ACT_2 and ACT_3 are better candidates.
  - For the third argument, both DECFLOAT and DOUBLE are in the same data type precedence list and VARCHAR can be implicitly cast to both DECFLOAT and DOUBLE. Since DECFLOAT is a better fit for the purpose of implicit casting, ACT_2 is the best fit
This example illustrates that during function resolution using the castable process that promotion of later arguments takes precedence over implicit casting. For this example, there are only three function in a single schema defined as follows:
```
CREATE FUNCTION CAESAR.ACT (INT, INT, VARCHAR(5))SPECIFIC ACT_1 ...
CREATE FUNCTION CAESAR.ACT (INT, INT, DECFLOAT) SPECIFIC ACT_2 ...
CREATE FUNCTION CAESAR.ACT (INT, INT, DOUBLE)  SPECIFIC ACT_3 ...
```
The function reference is as follows (where I1 is an INTEGER column, and VC1 is a VARCHAR column and C1 is a CHAR column):
```
SELECT ... ACT(I1, VC1, C1) ...
```
Assume that the application making this reference has an SQL path established as:
```
"CAESAR"
```
Following through the algorithm:
- Each of the candidate functions is evaluated to determine if the data type of each input argument of the function invocation matches or is promotable to the data type of the corresponding parameter of the function instance:
  - For the first argument, all the candidate functions have an exact match with the parameter type.
  - For the second argument, all candidate functions are eliminated since VARCHAR is not promotable to INTEGER, so no candidate functions remain.
- Since the subset of candidate functions from above is empty, the candidate functions are considered using the castable process
  - For the first argument, all the candidate functions have an exact match with the parameter type.
  - For the second argument, none of the candidate functions have a parameter to which the corresponding argument can be promoted, so no candidate functions are eliminated.
  - Since the third argument can be promoted to the parameter of ACT_1, but not to the parameters of ACT_2 or ACT_3, ACT_1 is the best fit.