Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5

    Unanswered: UDF problems in DB2 V7 on Z/OS

    I sent this out to the DB2-L server yesterday, but didn't get much in the way of responses, maybe someone on here can help shed some light on my woes. We wrote a UDF in COBOL(udf external scalar) that given some inputs will go out and find the person id that those values were entered for. The underlying purpose of this UDF was to enable us to use the UDF in a before insert trigger for denormalizing some tables to eliminate a ton of I/O. When I try to use the UDF in an insert, I get a -450 which states I'm overlaying storage. The data types and lengths of the values have been verified several times in both the table definitions, the UDF definition and in the COBOL working storage and linkage.
    The UDF definition is:
    CREATE FUNCTION SQLTOOLS.GET_PERSON
    (col1 INTEGER,
    col2 CHAR ( 1 ) FOR SBCS DATA CCSID EBCDIC,
    col3 INTEGER,
    col4 SMALLINT)
    RETURNS SYSIBM.INTEGER
    SPECIFIC SQLTOOLS.GET_PERSON
    EXTERNAL NAME 'UDFGTPRS'
    LANGUAGE COBOL
    PARAMETER STYLE DB2SQL
    DETERMINISTIC
    FENCED
    RETURNS NULL ON NULL INPUT
    NO SQL
    NO EXTERNAL ACTION
    SCRATCHPAD 16
    FINAL CALL
    DISALLOW PARALLEL
    DBINFO
    COLLID DB2U85
    WLM ENVIRONMENT DBT1WLMU
    STAY RESIDENT YES
    PROGRAM TYPE MAIN
    SECURITY DB2
    INHERIT SPECIAL REGISTERS


    The trigger definition is:
    CREATE TRIGGER mytrig NO CASCADE BEFORE INSERT
    ON my_ins_table REFERENCING NEW AS NROW
    FOR EACH ROW MODE DB2SQL
    BEGIN ATOMIC
    SET NROW.I_PERSON = SQLTOOLS.GET_PERSON( NROW.col1 ,
    NROW.col2 ,NROW.col3 ,NROW.col4);
    END ~
    --#SET TERMINATOR ;

    When I try to select a row(s) from a table using the UDF, I get a -440 function does not exist. The query is of the type:
    SELECT * FROM mytable
    WHERE I_PERSON = SQLTOOLS.GET_PERSON(2007064,'7',6828,1)

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Do you have Execute rights on the function ?
    Quote Originally Posted by dav1mo
    When I try to select a row(s) from a table using the UDF, I get a -440 function does not exist. The query is of the type:
    SELECT * FROM mytable
    WHERE I_PERSON = SQLTOOLS.GET_PERSON(2007064,'7',6828,1)
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Here is a more detailed explanation of -450 on LUW ... See if this describes something you might have missed

    SQL0450N Routine "<routine-name>" (specific name
    "<specific-name>") has generated a result value,
    SQLSTATE value, message text, or scratchpad which is
    too long.
    Explanation:
    Upon return from routine "<routine-name>" (specific name
    "<specific-name>"), DB2 has detected that more bytes were
    returned than were allocated for one of the following:
    o the result value (based upon the routine definition). There
    are several possible causes:
    - Too many bytes were moved to the result buffer.
    - The data type is one where the data value must be delimited
    by a null, such as VARCHAR(n), and the delimiting null
    was not within the range of the defined size.
    - DB2 is expecting a two- or four- byte length value preceding
    the value, and this length exceeds the defined size of
    the result.
    - A LOB locator is returned by the routine, and the length of
    the LOB value associated with this locator exceeds the
    defined size of the result.

    The definition of the result argument in the Routine must
    conform to the requirement for the data type. Refer to the
    Application Development Guide for more information.
    o the SQLSTATE value (6 bytes including the null terminator)
    o the message text (71 bytes including the null terminator).
    o the scratchpad content (length declared on CREATE
    FUNCTION).

    This is not permitted.
    This error will also be returned if the length field of the
    scratchpad is altered by the routine.
    User Response:
    See your Database Administrator, or the author of the routine.
    sqlcode : -450
    sqlstate : 39501
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    When I try to select a row(s) from a table using the UDF, I get a -440 function does not exist. The query is of the type:
    SELECT * FROM mytable
    WHERE I_PERSON = SQLTOOLS.GET_PERSON(2007064,'7',6828,1)
    Your trigger would not be fired for a SELECT statement. Because, the trigger was defined as BEFORE INSERT.

    I thought that the error 440 was caused by referencing SQLTOOLS.GET_PERSON in WHERE clause.

    There's no UDF with having (BIG) INTEGER for it's fourth parameter.
    You specified integer constant "1" for the fourth argument in your query.
    Data type of integer constant would be assumed as BIG integer.
    Data type of col4 in your UDF was defined as SMALLINT.

Posting Permissions

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