Determining the best fit

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 the data type of the result of the function or the type of function (aggregate, scalar, or table) under consideration does not enter 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. When determining whether the data types of the parameters are the same as the arguments:
  • Start of changeSynonyms of data types match. For example, DOUBLE and FLOAT are considered to be the same.End of change
  • Attributes of a data type such as length, precision, scale, and CCSID are ignored. Therefore, CHAR(8) and CHAR(35) are considered to be the same, as are DECIMAL(11,2), and DECIMAL(4,3).
  • The character and graphic types are considered to be the same. For example, the following are considered to be the same type: CHAR and GRAPHIC, VARCHAR and VARGRAPHIC, and CLOB and DBCLOB. CHAR(13) and GRAPHIC(8) are considered to be the same type.

If there is no match, the database manager compares the data types in the parameter lists from left to right, using the following method:

  1. Compare the data type of the first argument in the function invocation to the data type of the first parameter in each function. (The rules previously mentioned are used to determine whether the data types are the same.)
  2. For this argument, if one function has a data type that fits the function invocation better than the data types in the other functions, that function is the best fit. The precedence list for the promotion of data types in Promotion of data types shows the data types that fit each data type in best-to-worst order.
  3. If the data type of the first parameter for more than one candidate function fits the function invocation equally well, repeat this process for the next argument of the function invocation. Continue for each argument until a best fit is found.

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 schema, 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, NUMERIC(8,0))...
   4. CREATE FUNCTION ROMANO.MYFUNC (INT, FLOAT) ...          

Also assume that the SQL path at the time an application invokes a function is "ROMANO", "KNAPP", "BESTGEN" and that the authorization ID of the statement has the EXECUTE privilege to Functions 1, 2, and 4. 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: