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 ;
