Results 1 to 1 of 1
  1. #1
    Join Date
    Oct 2005
    Posts
    1

    Unanswered: Can't run db2/400 user defined function

    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....'
    Last edited by robpinion; 10-10-05 at 19:18. Reason: corrections

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •