Hi,

I am using the below pasted stored procedure in one of my Informatica mappings. The SP is working fine and returning values as expected when i run it in DB2. I am calling it from an Informatica mapping and the mapping runs fine for 1 or 2 records. But, When I run the mapping with full volume of data, the mapping does not move forward after a particular point. Say, after reading some 20,000 records from the source, the mapping stops reading any further records. After 3 or 4 hours, it throws the following error message and gets failed.

CMN_1022 : Database driver error...
CMN_1022 [
[IBM][CLI Driver][DB2/LINUXX8664] SQL0973N Not enough storage is available in the "APP_CTL_HEAP_SZ" heap to process the statement. SQLSTATE=57011
sqlstate = 57011

Database driver error...
Function Name : ExecuteSP

DB2 Fatal Error
Database driver error...
Function Name : ExecuteSP

I do not know what is the problem with the stored procedure or mapping. DBA says that there are mappings in the environment that read a trillion records and yet run without throwing any storage space error.

Please help me to debug and fix this issue

Thanks,

Shanthi


-- Start of generated script for hodwh02-podwh02-PODWH021 (n046938)
-- Nov-25-2008 at 05:19:38

SET SCHEMA N046938 ;

SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","N0469 38";

CREATE PROCEDURE N046938.ADVC_PRINT_DATE_WEEKLY (IN I_CUST_GRP_SK DECIMAL(15,0),
IN I_FIL_DATE DATE,
OUT O_ADVC_VLU_DT DATE )
SPECIFIC N046938.ADVC_PRINT_DATE_WE
LANGUAGE SQL
NOT DETERMINISTIC
CALLED ON NULL INPUT
MODIFIES SQL DATA
INHERIT SPECIAL REGISTERS

--| ---------------------------------------------------------------------------------|--
--| FILENAME = ADVC_PRINT_DATE_WEEKLY.SQL |--
--| FILE_VERSION_NUMBER = 1.0 |--
--| ---------------------------------------------------------------------------------|--
--| PROCEDURE : ADVC_PRINT_DATE_WEEKLY |--
--| DESCRIPTION : |--
--| ------------------------------------DEVELOPMENT HISTORY -------------------------|--
--| AUTHOR : |--
--| DATE : 24/NOV/2008 |--
--| ORG VERSION : 1.0 |--
--| ---------------------------------------------------------------------------------|--

BEGIN


-- VARIABLE DECLARATION

DECLARE V_PRINT_DATE DATE ;
DECLARE V_CUST_ID_NBR DECIMAL(9,0) ;
DECLARE V_ABCM_AMT DECIMAL(11,0) ;
DECLARE V_TOT_ABCM_AMT DECIMAL(11,0) ;
DECLARE V_PROC_DT DATE ;

-- CURSOR DECLARATION

--// FETCH THE DATES USED FOR ABC AMOUNT CALCULATION
DECLARE C1 CURSOR FOR
SELECT
ADVC_DT_VLU.ADVC_VLU_DT ADVC_VLU_DT
FROM
SALES.ADVC_CURR_RVSE ADVC_CURR_RVSE,
SALES.ADVC_DT_VLU ADVC_DT_VLU,
SALES.ADVC_AMT_VLU ADVC_AMT_VLU
WHERE
(ADVC_CURR_RVSE.ADVC_SK = ADVC_AMT_VLU.ADVC_SK AND
ADVC_AMT_VLU.ADVC_RVSE_LVL_NBR = ADVC_CURR_RVSE.ADVC_RVSE_LVL_NBR) AND
(ADVC_DT_VLU.ADVC_SK = ADVC_CURR_RVSE.ADVC_SK AND
ADVC_DT_VLU.ADVC_RVSE_LVL_NBR = ADVC_CURR_RVSE.ADVC_RVSE_LVL_NBR) AND
ADVC_CURR_RVSE.ADVC_STS_CDE = 'CMPLT' AND
ADVC_CURR_RVSE.TOOL_SRC_RCD_SK = 3 AND
ADVC_DT_VLU.TOOL_DT_DEF_CDE = 'PLNFD' AND
ADVC_DT_VLU.ADVC_VLU_DT IS NOT NULL AND
ADVC_AMT_VLU.TOOL_SRC_RCD_SK = 3 AND
ADVC_AMT_VLU.TOOL_AMT_DEF_CDE = 'NTWRT' AND
ADVC_AMT_VLU.ADVC_AMT_VLU > 0 AND
ADVC_DT_VLU.ADVC_VLU_DT >= i_FIL_DATE AND
ADVC_CURR_RVSE.CUST_GRP_SK = i_CUST_GRP_SK
UNION
SELECT
ADVC_CURR_RVSE.TRNS_EFF_DT ADVC_VLU_DT
FROM
SALES.ADVC_CURR_RVSE ADVC_CURR_RVSE
WHERE
ADVC_CURR_RVSE.TOOL_SRC_RCD_SK IN (5, 6, 20) AND
ADVC_CURR_RVSE.ADVC_STS_CDE = 'CMPLT' AND
ADVC_CURR_RVSE.CUST_GRP_SK = i_CUST_GRP_SK
UNION
SELECT
ADVC_DT_VLU.ADVC_VLU_DT ADVC_VLU_DT
FROM
SALES.ADVC_CURR_RVSE ADVC_CURR_RVSE,
SALES.ADVC_DT_VLU ADVC_DT_VLU,
SALES.ADVC_CHAR_VLU ADVC_CHAR_VLU
WHERE
ADVC_CURR_RVSE.ADVC_SK = ADVC_DT_VLU.ADVC_SK AND
ADVC_CURR_RVSE.ADVC_RVSE_LVL_NBR = ADVC_DT_VLU.ADVC_RVSE_LVL_NBR AND
ADVC_CURR_RVSE.ADVC_SK = ADVC_CHAR_VLU.ADVC_SK AND
ADVC_CURR_RVSE.TOOL_SRC_RCD_SK = 4 AND
ADVC_CURR_RVSE.ADVC_STS_CDE = 'CMPLT' AND
ADVC_CURR_RVSE.CUST_GRP_SK IS NOT NULL AND
ADVC_CHAR_VLU.TOOL_CHAR_DEF_CDE LIKE 'PG%' AND
(ADVC_CHAR_VLU.ADVC_CHAR_TXT LIKE 'pFinancialIndependence%' OR ADVC_CHAR_VLU.ADVC_CHAR_TXT LIKE 'pEdGoals%' OR
ADVC_CHAR_VLU.ADVC_CHAR_TXT LIKE 'pSurvCap%' OR
ADVC_CHAR_VLU.ADVC_CHAR_TXT LIKE 'pLTC%' OR
ADVC_CHAR_VLU.ADVC_CHAR_TXT LIKE 'pAccumulationNeeds%' OR
ADVC_CHAR_VLU.ADVC_CHAR_TXT LIKE 'pDisability%' ) AND
ADVC_DT_VLU.ADVC_VLU_DT is not NULL and
ADVC_DT_VLU.TOOL_DT_DEF_CDE = 'SRFDT' AND
ADVC_DT_VLU.TOOL_SRC_RCD_SK = 4 AND
ADVC_DT_VLU.ADVC_VLU_DT >= i_FIL_DATE AND
ADVC_CURR_RVSE.CUST_GRP_SK = i_CUST_GRP_SK
ORDER BY
ADVC_VLU_DT
WITH UR ;

--// OFFSET THE ABC AMOUNT BY DIVIDING INTO POSITIVE AND NEGATIVE GROUPS


--// SELECT THE ABC AMOUNT FROM CREDIT TABLE


DECLARE GLOBAL TEMPORARY TABLE SESSION.ADVC_SALE_CR_DTL_DM_SP (SVC_EMP_ID_NBR VARCHAR(10),BEN_CTRC_TYP_CDE VARCHAR(5),
BEN_CTRC_NBR VARCHAR(15),ABCM_AMT DECIMAL(11,0), PROC_DATE DATE) WITH REPLACE;

INSERT INTO SESSION.ADVC_SALE_CR_DTL_DM_SP (SVC_EMP_ID_NBR,BEN_CTRC_TYP_CDE, BEN_CTRC_NBR,ABCM_AMT,PROC_DATE)
SELECT EMPL_ID_NBR_DRT,BEN_CTRC_TYP_CDE, BEN_CTRC_NBR,SC_AMT,PROC_DATE
FROM
SCD.SCDTL01T
WHERE
(((CTF_CR_COUNT IN (1,-1) or AGT_INDIC_CODE='3' or (AGT_INDIC_CODE='1' and CTF_CR_COUNT =0))) or PLAN_CODE='RB1BR')
AND YEAR(UPDT_DT_TIME_STMP) >= (YEAR(CURRENT_DATE)-1)
AND CUST_ID_NBR IN
(SELECT CUST_ID_NBR
FROM MEMBER.CUST_GRP_MBR
WHERE CUST_GRP_SK = i_CUST_GRP_SK
AND CUST_GRLN_END_DT IS NULL);


OPEN c1;

FETCH FROM c1 INTO V_PRINT_DATE;

IF(V_PRINT_DATE < (CURRENT_DATE - 180 DAYS)) THEN

BEGIN


WHILE SQLCODE <> 100
DO

--// *** LOOK FOR THIS SYMBOL TO IDENTIFY THE ELSE PART
--//IF THE PRINT IS A EXPIRED PRINT , THEN CONTINUE TO CHECK FOR ABC >250 ELSE OUTPUT THE PRINT DATE AND EXIT


BEGIN

SELECT
sum(case when(A.PROC_DATE between (V_PRINT_DATE - 5 DAYS) AND (V_PRINT_DATE + 180 DAYS))
then A.ABCM_AMT
else 0
END) into V_TOT_ABCM_AMT
FROM
(SELECT
SVC_EMP_ID_NBR, BEN_CTRC_TYP_CDE,BEN_CTRC_NBR,ABCM_AMT,PROC_DATE,
ROW_NUMBER()OVER (PARTITION BY SVC_EMP_ID_NBR, BEN_CTRC_TYP_CDE,BEN_CTRC_NBR,ABCM_AMT
ORDER BY SVC_EMP_ID_NBR, BEN_CTRC_TYP_CDE,BEN_CTRC_NBR,ABCM_AMT) CNT
FROM
SESSION.ADVC_SALE_CR_DTL_DM_SP
WHERE
ABCM_AMT>0
) A
WHERE
NOT EXISTS
(SELECT
SVC_EMP_ID_NBR, BEN_CTRC_TYP_CDE,BEN_CTRC_NBR,ABCM_AMT,CNT
FROM
(SELECT
SVC_EMP_ID_NBR, BEN_CTRC_TYP_CDE,BEN_CTRC_NBR,ABS(ABCM_AMT) ABCM_AMT,
ROW_NUMBER()OVER (PARTITION BY SVC_EMP_ID_NBR, BEN_CTRC_TYP_CDE,BEN_CTRC_NBR,ABCM_AMT
ORDER BY SVC_EMP_ID_NBR, BEN_CTRC_TYP_CDE,BEN_CTRC_NBR,ABCM_AMT) CNT
FROM
SESSION.ADVC_SALE_CR_DTL_DM_SP
WHERE
ABCM_AMT<0
)B
WHERE
A.SVC_EMP_ID_NBR = B.SVC_EMP_ID_NBR
AND A.BEN_CTRC_TYP_CDE =B.BEN_CTRC_TYP_CDE
AND A.BEN_CTRC_NBR = B.BEN_CTRC_NBR
AND A.ABCM_AMT = B.ABCM_AMT
AND A.CNT =B.CNT
)
WITH UR;

--// IF THE SUM > 250, THEN RETURN THE DATE AND EXIT
IF( V_TOT_ABCM_AMT >= 250) THEN
SET O_ADVC_VLU_DT = V_PRINT_DATE ;
RETURN;
ELSE
--// ELSE CONTINUE THE WHILE LOOP
FETCH FROM C1 INTO V_PRINT_DATE;
END IF;
END;
END WHILE;

END;

--// *** THIS IS THE ELSE PART OF THE FIRST IF
ELSE
SET O_ADVC_VLU_DT = V_PRINT_DATE ;
RETURN;
END IF;
CLOSE C1;


END;

#SYNC 10;



-- End of generated script for hodwh02-podwh02-PODWH021 (n046938)

are those mappings that run fine with a trillion records inserting to a temp table as well? If you take a look in the manuals or search in the info center you will get hits explaining when and why this heap is used and how to increase it along with why you might increase it.