471,896 Members | 1,868 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,896 software developers and data experts.

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 5917
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

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.