Multiple functions can with the same name can exist in the same schema or in different
schemas. You can take certain actions so that Db2 chooses the correct function to
execute.
About this task
The combination of the function name and the parameter list form the signature that
Db2 uses to identify a function. For
detailed information about the rules and process that Db2 uses to identify the function to invoke, see
Function resolution.
If the signatures of two functions match, including built-in and user-defined functions, you must
take appropriate action to ensure that Db2
invokes the correct intended function.
Procedure
To simplify the resolution of built-in and user-defined functions, use the following
techniques:
-
When you invoke a function, use the qualified name.
This causes
Db2 to search for
functions only in the schema you specify. This approach has the following advantages:
- Db2 is less likely to choose a function
that you did not intend to use. Several functions might fit the invocation equally well. Db2 picks the function whose schema name is listed
first in the SQL path, which might not be
the function you want.
- The number of candidate functions is smaller, so Db2 takes less time for function resolution.
-
Cast parameters in a user-defined function invocation to the types in the user-defined function
definition. For example, if an input parameter for user-defined function FUNC is defined as
DECIMAL(13,2), and the value you want to pass to the user-defined function is an integer value, cast
the integer value to DECIMAL(13,2):
For example, if an input parameter for user-defined function FUNC is defined as DECIMAL(13,2),
and the value you want to pass to the user-defined function is an integer value, cast the integer
value to DECIMAL(13,2):
SELECT FUNC(CAST (INTCOL AS DECIMAL(13,2))) FROM T1;
-
Use the data type BIGINT for numeric parameters in a user-defined function.
When you invoke the function, you can pass in SMALLINT, INTEGER, or BIGINT values. If you use
SMALLINT or REAL as the parameter type, you must pass parameters of the same types. For example, if
user-defined function FUNC is defined with a parameter of type SMALLINT, only an invocation with a
parameter of type SMALLINT resolves correctly. The following call does not resolve to FUNC because
the constant 123 is of type INTEGER, not SMALLINT:
SELECT FUNC(123) FROM T1;
-
Avoid defining user-defined function string parameters with fixed-length string types.
If you define a parameter with a fixed-length string type (CHAR, GRAPHIC, or BINARY), you can
invoke the user-defined function only with a fixed-length string parameter. However, if you define
the parameter with a varying-length string type (VARCHAR, VARGRAPHIC, or VARBINARY), you can invoke
the user-defined function with either a fixed-length string parameter or a varying-length string
parameter.
If you must
define parameters for a user-defined function as CHAR or BINARY, and you call the user-defined
function from a C program or SQL procedure, you need to cast the corresponding parameter values in
the user-defined function invocation to CHAR or BINARY to ensure that
Db2 invokes the correct function. For example,
suppose that a C program calls user-defined function CVRTNUM, which takes one input parameter of
type CHAR(6). Also suppose that you declare host variable empnumbr as
char
empnumbr[6]. When you invoke CVRTNUM, cast empnumbr to CHAR:
UPDATE EMP
SET EMPNO=CVRTNUM(CHAR(:empnumbr))
WHERE EMPNO = :empnumbr;