IBM DB2 for i: Tips

"Function not found" is a common error condition when invoking a User-Defined Function (UDF) for the first time and is often easily corrected.

How the "Function not found" error can occur
DB2 for i checks the signature (ie, the data types) when it attempts to resolve a call to a UDF. This signature is subject to data type promotion rules. If any of the data types for this UDF are incompatible, the user will receive the "Function not found" error.

The most common programming error with UDF data type promotion involves the character parameters (CHAR). This situation is best understood with an example UDF, which we've named countpart.

CREATE FUNCTION countpart(partcode CHAR(10) )
  RETURNS INTEGER
LANGUAGE SQL 
BEGIN
  DECLARE partcnt INTEGER;
  SET partcnt = SELECT count(*) FROM parts WHERE code=PARTCODE;
  RETURN partcnt;
END;

In this example, the countpart UDF is created with an input parameter defined to be a CHAR(10). Each time the application tries to invoke the function as follows,

  SELECT * FROM orders WHERE countpart