Ensuring that DB2 executes the intended user-defined function

Multiple functions with the same name can exist in the same schema or in different schemas. You should take certain actions to ensure that DB2® chooses the correct function to execute.

About this task

When you use the following techniques, you can simplify function resolution:

When you invoke a function, use the qualified name. This causes DB2 to search for functions only in the schema you specify. This has two 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 earliest 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):
```
SELECT FUNC(CAST (INTCOL AS DECIMAL(13,2))) FROM T1;
```
Use the data type BIGINT for numeric parameters in a user-defined function. If you use BIGINT as the parameter type, 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;
```

Parent topic: Invoking a user-defined function