I'm not having any luck trying to call a newly-created DB2/400 user defined function. I hope some kind soul will help.

When I enter this command:

SELECT DEVTEST.PROPER5('hello') from devtest.employee

I get this error:

SQL0454 PROPER5 in DEVTEST type *N not found. Cause . . . . . : PROPER5 in DEVTEST type *N was not found. If this is an ALTER TABLE statement and the type is *N, a constraint or partition was not found. If this is not an ALTER TABLE statement and the type is *N, a function, procedure, or trigger was not found. If a function was not found, PROPER5 is the service program that contains the function. The function will not be found unless the external name and usage name do not match exactly. Examine the job log for a message that gives more details on which function name is being searched for and the name that did not match. Recovery . . . : Change the name and try the request again. If the object is a node group, ensure that the DB2 Multisystem product is installed on your system and create a nodegroup with the CRTNODGRP CL command. If an external function was not found, be sure that the case of the EXTERNAL NAME on the CREATE FUNCTION statement exactly matches the case of the name exported by the service program.

The function PROPER5 is defined as follows:

CREATE FUNCTION DEVTEST.PROPER5 (
X CHAR(50) )
RETURNS CHAR(50)
LANGUAGE SQL
SPECIFIC DEVTEST.PROPER5
DETERMINISTIC
CONTAINS SQL
CALLED ON NULL INPUT
NO EXTERNAL ACTION
RETURN UPPER ( X ) ;

If I call the built-in UPPER() function directly, instead of using DEVTEST.PROPER5(), it returns HELLO as expected. I can create another function, which I didn't write, and run it just fine. See function TAN4 below. It has the same permissions, etc on all tabs when I look at properties. Here's how that function is created and called:

CREATE FUNCTION DEVTEST.TAN4 (
X DOUBLE PRECISION )
RETURNS DOUBLE PRECISION
LANGUAGE SQL
SPECIFIC DEVTEST.TAN4
DETERMINISTIC
CONTAINS SQL
CALLED ON NULL INPUT
NO EXTERNAL ACTION
RETURN SIN ( X ) / COS ( X ) ;

select DEVTEST.TAN4(12345) from devtest.employee

....LOTS OF ROWS WITH '-8.917....'