UDF problems in DB2 V7 on Z/OS

dav1mo · #1 (**permalink**) 02-11-09, 08:45

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)

sathyaram_s · #2 (**permalink**) 02-11-09, 09:03

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)

sathyaram_s · #3 (**permalink**) 02-11-09, 09:08

Here is a more detailed explanation of -450 on LUW ... See if this describes something you might have missed

Quote:

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

tonkuma · #4 (**permalink**) 02-11-09, 11:10

Quote:

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech