UDF Inside UDF

[Alex Krashevsky]

I've recently run into an interesting situation.

Developed a UDF that utilizes yet another UDF inside the SQL body.

CREATE FUNCTION DBREV105 (
@ITEM_NUMBER CHAR(15) ,
@MONTHS_BACK INTEGER )
RETURNS BIGINT
LANGUAGE SQL
SPECIFIC DBREV105
NOT DETERMINISTIC
MODIFIES SQL DATA
NO EXTERNAL ACTION
NOT FENCED
CONCURRENT ACCESS RESOLUTION DEFAULT
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE , 88

COMMIT = *NONE ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = *NONE ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
SRTSEQ = *HEX
RETURN
( SELECT
SUM ( ORVL40 )
FROM OEP40
WHERE
STAT40 NOT IN (<SOME GROUP>)
AND DTSO40 >= DBDATE70('m', -@MONTHS_BACK)
AND EXISTS
( SELECT * FROM INP15 WHERE CONO15 = CONO40
AND PRMT15 = <SOME CODE> AND PSAR15 = <SOME CODE>
AND LOCATE ( SUSP40 , PRMD15 ) > 0 )
AND EXISTS
( SELECT * FROM OEP55
WHERE CONO55 = CONO40
AND ORDN55 = ORDN40
AND CATN55 = @ITEM_NUMBER ) ) ;

At execution time, a severe error occurs (SQLSTATE 58004).

Per IBM support, 58004 means...

That's the "something happened that's so severe that you should call db2
service" error.

https://groups.google.com/forum/#!to...b2/2sKsDpijKSo

Yet, when I modify my UDF to exclude the "inner" UDF, it runs just fine. The UDF that may be causing trouble returns the "date" past the specified number of months in CYYMMDD format. It works OK as standalone UDF.

The UDF below works just fine.

CREATE FUNCTION DBREV105 (
@ITEM_NUMBER CHAR(15) ,
@START_DATE DECIMAL(7,0) )
RETURNS BIGINT
LANGUAGE SQL
SPECIFIC DBREV105
NOT DETERMINISTIC
MODIFIES SQL DATA
NO EXTERNAL ACTION
NOT FENCED
CONCURRENT ACCESS RESOLUTION DEFAULT
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE , 88

COMMIT = *NONE ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = *NONE ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
SRTSEQ = *HEX
RETURN
( SELECT
SUM ( ORVL40 )
FROM OEP40
WHERE
STAT40 NOT IN ( <SOME GROUP> )
AND DTSO40 >= @START_DATE
AND EXISTS
( SELECT * FROM INP15 WHERE CONO15 = CONO40
AND PRMT15 = <SOME CODE> AND PSAR15 = <SOME CODE>
AND LOCATE ( SUSP40 , PRMD15 ) > 0 )
AND EXISTS
( SELECT * FROM OEP55
WHERE CONO55 = CONO40
AND ORDN55 = ORDN40
AND CATN55 = @ITEM_NUMBER ) ) ;


I am not desperate, just curious

What might it be that caused that error in the first place?

[tomliotta]

I can only guess that the advice in your posted link is the best advice available (outside of IBM Support).