Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2008
    Posts
    1

    Unanswered: DB2 Stored Procedure (Informatica) - storage space issue

    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)

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •