469,282 Members | 2,134 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,282 developers. It's quick & easy.

ISeries DB2 UDFs and Stored Procedure

We are running into a problem with a Communication Link failure when
calling an External Stored procedure written in ILE Cobol from an SQL
UDF. When calling the stored procedure by itself and not within the
UDF it runs fine, it's only when we excute the UDF that we get the
communication link failure. I have setup EXTERNAL ACTION on the the
UDF, the COBOL program doesn't execute any SQL statements.

Here is the function and how it is defined (Yes, I know that some of
this can be written as UDFs itself). The crash is occuring when
calling the ACVBYE external stored procedure:

CREATE FUNCTION UCRCDB.GETBYE (
IN_BYE numeric(6,0))
RETURNS Date
LANGUAGE SQL
SPECIFIC UCRCDB.GETBYE
DETERMINISTIC
READS SQL DATA
FENCED
EXTERNAL ACTION
DISALLOW PARALLEL
BEGIN
-- RETURN A BYE DATE FROM A NUMERIC(6,0)
-- CREATED BY DAN HOLT, FEB 2004, COMPUNAME
-- MODIFIED BY DAVID CARVER, COMPUWARE

-- Cobol Parms

DECLARE Func CHAR(1) DEFAULT '2'; -- 1 to 1

-- function 1
DECLARE bybMmddyySun CHAR(6) DEFAULT ' '; -- 2 to 7
DECLARE bybMmddyySat CHAR(6) DEFAULT ' '; -- 8 to 13

-- function 2
DECLARE byeMmddyySun CHAR(6) DEFAULT ' '; -- 14 to 19
DECLARE byeMmddyySat CHAR(6) DEFAULT ' '; -- 20 to 25

-- function 3
DECLARE bybWwyy CHAR(4) DEFAULT ' '; -- 26 to 29

-- function 4
DECLARE byeWwyy CHAR(4) DEFAULT ' '; -- 30 to 33

-- function5
DECLARE gregorian CHAR(6) DEFAULT ' '; -- 34 to 39

-- function6
DECLARE julianNnnyy CHAR(5) DEFAULT ' '; -- 40 to 44

DECLARE basePeriod CHAR(20) DEFAULT '00000000000000000000'; -- 45 to
64

DECLARE altBasePer CHAR(20) DEFAULT '00000000000000000000'; -- 65 to
84

DECLARE bdateError CHAR(1) DEFAULT ' '; -- 85 to 85

DECLARE returnDate CHAR(10) DEFAULT '0000000000'; -- 86 to 95

DECLARE parm CHAR(95) DEFAULT ' '; -- 1 to 95

-- misc

DECLARE strBYE CHARACTER(10) DEFAULT ' ';
DECLARE strBYE8 CHARACTER(8) DEFAULT ' ';
DECLARE byeWwyyn NUMERIC(4,0) DEFAULT 0;
DECLARE chgDate CHARACTER(6) DEFAULT ' ';
DECLARE CL_byedate DATE;

Declare cnvDate Decimal(8,0);

SET byeWwyy = SUBSTR(cast(digits(IN_BYE) As Char(6)),1,2) ||
SUBSTR(cast(digits(IN_BYE) As Char(6)),5,2) ;

SET func = '4';

SET PARM = func || bybmmddyysun || bybmmddyysat || byemmddyysun ||
byemmddyysat || bybwwyy || byewwyy || gregorian || juliannnnyy ||
baseperiod || altbaseper || bdateerror || returndate;

CALL UcrcDb.callACVBYE ( PARM );

Set chgDate = SUBSTRING(PARM FROM 24 FOR 2) || SUBSTRING(PARM FROM 20
FOR 4);

--CALL UcrcDb.CcYyMmDd (chgDate);
-- Begin CcYyMmDd (chgDate) code
Set CnvDate = cast(chgDate as Decimal);

If CnvDate > 500101 Then

Set CnvDate = CnvDate + 19000000;

Else

Set CnvDate = CnvDate + 20000000;

End If;

Set strBYE8 = cast(CnvDate as VarChar(8));

Set strBYE = cast(substr(strBYE8, 1,4) || '-' || substr(strBYE8, 5, 2)
|| '-' || substr(strBYE8, 7,2) As Date);

Set CL_byeDate = cast(strBYE as Date);
-- End CcYyMmDd (chgDate) code

RETURN CL_byedate ;

END ;
Nov 12 '05 #1
3 5794
UDF invocations have a default timeout of 30 or 60 seconds. Does the Stored
Procedure run for a long time possibly?

--
Kent Milligan, DB2 & BI team
PartnerWorld for Developers, iSeries
km***@us.eye-bee-m.com (spam trick) GO HAWKEYES!!
www.iseries.ibm.com/db2

(opinions stated are not necessarily those of my employer)
Nov 12 '05 #2
I'd also suggest changing the UDF to be NOT FENCED - it has the potential for
better performance.

--
Kent Milligan, DB2 & BI team
PartnerWorld for Developers, iSeries
km***@us.eye-bee-m.com (spam trick) GO HAWKEYES!!
www.iseries.ibm.com/db2

(opinions stated are not necessarily those of my employer)
Nov 12 '05 #3
Kent Milligan <km***@us.eye-bee-m.com> wrote in message news:<40***************@us.eye-bee-m.com>...
I'd also suggest changing the UDF to be NOT FENCED - it has the potential for
better performance.


Kent, thanks for the suggestions. I think I tried it both Fenced and
Not Fenced and received the same result. Anyway, I'll mess around
with it again and see what happens.

Thanks.

Dave
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

17 posts views Thread by Gregor Kovač | last post: by
3 posts views Thread by gherrell | last post: by
2 posts views Thread by jmuehe | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.