RE: DB2/400 ( V5R3) User Defined Function call Error - Message:[SQL0204]

Is XSDB in your library list and are you using system naming (*SYS)?

If using SQL naming, you can fully qualify your UDF, i.e. XSDB.TEST()

HTH

Elvis

Celebrating 10-Years of SQL Performance Excellence

-----Original Message-----
 Subject: RE: DB2/400 ( V5R3) User Defined Function call Error -
Message:[SQL0204] 

 I created a user defined function and when I try to call it from a
SELECT statement I am getting the SQL State: 42704 error.

Please see the function, SELECT statement and Error message below.

Any help is appreciated. Thanks in Advance.

SET PATH *LIBL ; 

CREATE FUNCTION XSDB/TEST ( ) 

                RETURNS VARCHAR(25)   

                LANGUAGE SQL 

                SPECIFIC XSDB/TEST 

                NOT DETERMINISTIC 

                READS SQL DATA 

                CALLED ON NULL INPUT 

                DISALLOW PARALLEL 

                NOT FENCED 

                BEGIN 

                                                                RETURN (
SELECT CAST( RTRIM ( HRNAME ) AS VARCHAR(25))

 
FROM XSDB / HROOM 

 
WHERE HRROOM = 'B' ) ; 

 
END  ;

 

SELECT RTRIM ( HRNAME ), TEST()

 

                FROM XSDB/HROOM 

 

                WHERE HRROOM ='B' ;

 

 

Error Message

 

SQL State: 42704

Vendor Code: -204

Message: [SQL0204] TEST in *LIBL type *N not found. Cause . . . . . :
TEST in *LIBL 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, TEST 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.

 

Processing ended because the highlighted statement did not complete
successfully

 

 

Thanks,

Stanly Abraham