Cases when Db2 casts arguments for a user-defined function

In certain situations, when you invoke a user-defined function, Db2 casts your input argument values to different data types and lengths.

Whenever you invoke a user-defined function, Db2 assigns your input argument values to parameters with the data types and lengths in the user-defined function definition.

When you invoke a user-defined function that is sourced on another function, Db2 casts your arguments to the data types and lengths of the sourced function.

The following example demonstrates what happens when the parameter definitions of a sourced function differ from those of the function on which it is sourced.

Suppose that external user-defined function TAXFN1 is defined like this: 

CREATE FUNCTION TAXFN1(DEC(6,0))
  RETURNS DEC(5,2)
  PARAMETER STYLE SQL
  LANGUAGE C
  EXTERNAL NAME TAXPROG;

Sourced user-defined function TAXFN2, which is sourced on TAXFN1, is defined like this: 

CREATE FUNCTION TAXFN2(DEC(8,2))
  RETURNS DEC(5,0)
  SOURCE TAXFN1;

You invoke TAXFN2 using this SQL statement: 

UPDATE TB1
  SET SALESTAX2 = TAXFN2(PRICE2);

TB1 is defined like this: 

CREATE TABLE TB1
  (PRICE1    DEC(6,0),
   SALESTAX1 DEC(5,2),
   PRICE2    DEC(9,2),
   SALESTAX2 DEC(7,2));

Now suppose that PRICE2 has the DECIMAL(9,2) value 0001234.56. Db2 must first assign this value to the data type of the input parameter in the definition of TAXFN2, which is DECIMAL(8,2). The input parameter value then becomes 001234.56. Next, Db2 casts the parameter value to a source function parameter, which is DECIMAL(6,0). The parameter value then becomes 001234. (When you cast a value, that value is truncated, rather than rounded.)

Now, if TAXFN1 returns the DECIMAL(5,2) value 123.45, Db2 casts the value to DECIMAL(5,0), which is the result type for TAXFN2, and the value becomes 00123. This is the value that Db2 assigns to column SALESTAX2 in the UPDATE statement.

Casting of parameter markers

You can use untyped parameter markers in a function invocation. However, Db2 cannot compare the data types of untyped parameter markers to the data types of candidate functions. Therefore, Db2 might find more than one function that qualifies for invocation. If this happens, an SQL error occurs. To ensure that Db2 picks the right function to execute, cast the parameter markers in your function invocation to the data types of the parameters in the function that you want to execute. For example, suppose that two versions of function FX exist. One version of FX is defined with a parameter of type of DECIMAL(9,2), and the other is defined with a parameter of type INTEGER. You want to invoke FX with a parameter marker, and you want Db2 to execute the version of FX that has a DECIMAL(9,2) parameter. You need to cast the parameter marker to a DECIMAL(9,2) type by using a CAST specification: 

  SELECT FX(CAST(? AS DECIMAL(9,2))) FROM T1;