Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2004
    Posts
    11

    Unanswered: stored proc problem

    When i run this procedure it does not release locks acquired(it acquires row locks and escalate to table lock after a while, holding other processes up) and it takes 15 - 20 to update 40k rows.Can someone please tell me what i can do to speed it up...........
    CREATE PROCEDURE DB2INST1.UPDATE_REFNUM9()
    LANGUAGE SQL
    SPECIFIC SQL041031134714833
    MODIFIES SQL DATA
    NOT DETERMINISTIC
    INHERIT SPECIAL REGISTERS
    BEGIN NOT ATOMIC

    DECLARE V_JFF_REF_NUM CHAR(15);
    DECLARE V_PROC_CCID_IN CHAR(03);
    DECLARE V_BLOCKID_IN CHAR(07);
    DECLARE V_POST_YEAR CHAR(04);
    DECLARE V_DAYOFYEAR CHAR(03);
    DECLARE V_PART_HIERARCHY CHAR(04);
    DECLARE V_POST_DATE DATE;
    DECLARE V_SKEY VARCHAR(50);
    DECLARE V_ACCOUNT_NUMBER VARCHAR(50);
    DECLARE V_COUNTER BIGINT;
    -- FOR DEBUG
    DECLARE V_NUM SMALLINT DEFAULT 0;
    DECLARE V_MSG1 VARCHAR(4000);
    DECLARE V_MSG2 VARCHAR(1);
    -- END
    SET V_NUM=2000;
    SET V_MSG1='debugging start';
    SET V_NUM = 0;
    FOR v_C1_REC AS C1 CURSOR FOR

    SELECT SKEY, a.account_number, a.PROC_CCID, POST_DATE, a.blockid, b.hierarchy_num FROM COM.TBASE a,
    citicom.cards_to_hierarchy_map b WHERE SUBSTR(skey,11,1) = '_' and a.account_number = b.account_number and post_date = '2
    003-01-16'

    DO
    SET V_BLOCKID_IN = v_C1_REC.BLOCKID;
    SET V_PART_HIERARCHY = v_C1_REC.HIERARCHY_NUM;
    SET V_SKEY = v_C1_REC.SKEY;
    SET v_DAYOFYEAR = char(DAYOFYEAR(v_c1_rec.POST_DATE));
    SET V_POST_DATE = char(v_C1_REC.POST_DATE);
    SET v_DAYOFYEAR = char(DAYOFYEAR(v_C1_REC.POST_DATE));
    SET V_POST_YEAR = char(YEAR(v_C1_REC.POST_DATE));
    SET V_ACCOUNT_NUMBER = v_c1_rec.ACCOUNT_NUMBER;

    WHILE V_PART_HIERARCHY IN ('1410','1412','1413','1813','3354','3355','3356', '4627','4628')
    DO
    IF dayofyear(v_C1_REC.POST_DATE) < 10
    then
    UPDATE COM.TBASE
    SET JFF_REFERENCE_NUMBER = CONCAT('DEF',CONCAT(CONCAT(CONCAT(SUBSTR(V_POST_YE AR,3,2), '00'), substr(V_DAYOFYEAR,1,1)), BLO
    CKID)), SKEY =CONCAT('DEF',CONCAT(CONCAT(CONCAT(SUBSTR(V_POST_Y EAR,3,2), '00'), substr(V_DAYOFYEAR,1,1)), BLOCKID)) WHERE
    V_SKEY = SKEY;
    GOTO EXIT;
    ELSE
    IF dayofyear(v_C1_REC.POST_DATE) < 100
    THEN
    UPDATE COM.TBASE
    SET JFF_REFERENCE_NUMBER = CONCAT('DEF',CONCAT(CONCAT(concat(SUBSTR(V_POST_YE AR,3,2), '0'), substr(V_DAYOFYEAR, 1,2)), B
    LOCKID)), SKEY = CONCAT('DEF',CONCAT(CONCAT(concat(SUBSTR(V_POST_YE AR,3,2), '0'), substr(V_DAYOFYEAR,1,2)), BLOCKID)) WHER
    E V_SKEY = SKEY;

    GOTO EXIT;
    ELSEIF dayofyear(v_C1_REC.POST_DATE) > 99
    THEN
    UPDATE COM.TBASE
    SET TRX_REFERENCE_NUMBER = CONCAT('DEF',CONCAT(CONCAT(SUBSTR(V_POST_YEAR,3,2) , V_DAYOFYEAR), BLOCKID)), SKEY = CONCAT('DEF',CONCAT(CONCAT(SUBSTR(V_POST_YEAR,3,2) , V_DAYOFYEAR), BLOCKID)) WHERE V_SKEY = SKEY;
    GOTO EXIT;

    END IF;
    END IF;
    END WHILE;

    WHILE V_PART_HIERARCHY IN ('1814','3353','4625')
    DO
    IF dayofyear(v_C1_REC.POST_DATE) < 10
    THEN
    UPDATE COM.TBASE SET JFF_REFERENCE_NUMBER = CONCAT('DF1',concat(concat(CONCAT (SUBSTR(V_POST_YEAR,3,2)
    , '00' ), substr(V_DAYOFYEAR,1,1)), BLOCKID)), SKEY = CONCAT('DF1',CONCAT(CONCAT(concat(SUBSTR(V_POST_YE AR,3,2), '00'),
    substr(V_DAYOFYEAR,1,2)), BLOCKID)) WHERE V_SKEY = SKEY;

    GOTO EXIT;
    ELSE
    if dayofyear(v_C1_REC.POST_DATE) < 100
    THEN
    UPDATE COM.TBASE SET JFF_REFERENCE_NUMBER = CONCAT('DF1',concat(concat(CONCAT (SUBSTR(V_POST_YEAR,3,2),
    '0' ), substr(V_DAYOFYEAR,1,1)), BLOCKID)), SKEY = CONCAT('DF1',CONCAT(CONCAT(concat(SUBSTR(V_POST_YE AR,3,2), '0'),substr
    (V_DAYOFYEAR,1,2)), BLOCKID)) WHERE V_SKEY = SKEY;
    GOTO EXIT;
    ELSEIF
    dayofyear(v_C1_REC.POST_DATE) > 99
    THEN
    UPDATE COM.TBASE SET JFF_REFERENCE_NUMBER = CONCAT('DF1',concat(CONCAT (SUBSTR(V_POST_YEAR, 3,2), V_DAYOFYEAR)
    , BLOCKID)), SKEY = CONCAT('DF1',concat(CONCAT (SUBSTR(V_POST_YEAR, 3,2), V_DAYOFYEAR), BLOCKID)) WHERE V_SKEY = SKEY;
    GOTO EXIT;
    END IF;
    END IF;
    END WHILE;

    WHILE V_PART_HIERARCHY IN ('8240','8243','8244','8260','8261','8262','8263', '8264','8279')
    DO
    IF dayofyear(v_C1_REC.POST_DATE) < 10
    THEN UPDATE COM.TBASE SET JFF_REFERENCE_NUMBER = CONCAT('DF2',CONCAT(CONCAT(CONCAT(SUBSTR(V_POST_YE AR,3,2),
    '00'), SUBSTR(V_DAYOFYEAR,1,1)), BLOCKID)), SKEY = CONCAT('DF2',CONCAT(CONCAT(CONCAT(SUBSTR(V_POST_YE AR,3,2), '00'), SUBST
    R(V_DAYOFYEAR,1,1)), BLOCKID))WHERE V_SKEY = SKEY;
    GOTO EXIT;
    ELSE
    IF dayofyear(v_C1_REC.POST_DATE) < 100
    THEN UPDATE COM.TBASE SET JFF_REFERENCE_NUMBER = CONCAT('DF2',CONCAT(CONCAT(CONCAT(SUBSTR(V_POST_YE AR,3,
    2), '0'), substr( V_DAYOFYEAR,2,2)), BLOCKID)), SKEY = CONCAT('DF2',CONCAT(CONCAT(CONCAT(SUBSTR(V_POST_YE AR,3,2), '0'), su
    bstr( V_DAYOFYEAR,2,2)), BLOCKID)) WHERE V_SKEY = SKEY;
    GOTO EXIT;

    ELSEIF dayofyear(v_C1_REC.POST_DATE) > 99
    THEN
    UPDATE CITICOM.TBASE SET JFF_REFERENCE_NUMBER = CONCAT('DF2',CONCAT(CONCAT(SUBSTR(V_POST_YEAR,3,2) , V_DAYOFYEAR),
    BLOCKID)), SKEY = CONCAT('DF2',CONCAT(CONCAT(SUBSTR(V_POST_YEAR,3,2) , V_DAYOFYEAR), BLOCKID)) WHERE V_SKEY = SKEY;
    GOTO EXIT;
    END IF;
    end if;
    END WHILE;

    EXIT:
    SET V_COUNTER = V_COUNTER + 1;
    IF V_COUNTER = 1000 THEN
    COMMIT WORK;

    SET V_COUNTER = 0;
    END IF;
    END FOR;
    COMMIT WORK;
    END
    ;



    i WILL ALSO POST THE SECOND ONE SOON
    Last edited by actions123; 11-04-04 at 18:15.

  2. #2
    Join Date
    Mar 2004
    Posts
    448
    Did you check the *.nfy and db2diag.log file for any lock
    escalation


    regards

    Mujeeb

  3. #3
    Join Date
    Aug 2004
    Posts
    11
    Yes.I did.I actually saw it in the SmartDBA.There was an X lock on the rows and another X lock on the Table.

  4. #4
    Join Date
    Aug 2004
    Posts
    11
    Here is the second VERSION OF THE SP

    CREATE PROCEDURE UPDATE_REFNUM1()
    RESULT SETS 0
    LANGUAGE SQL
    MODIFIES SQL DATA
    BEGIN NOT ATOMIC

    DECLARE SQLCODE INT DEFAULT 0;
    DECLARE SQLSTATE CHAR ( 5 );
    DECLARE V_JFF_REF_NUM CHAR(15);
    DECLARE V_PROC_CCID_IN SMALLINT;
    DECLARE V_BLOCKID_IN VARCHAR(10);
    DECLARE V_POST_YEAR CHAR(04);
    DECLARE V_DAYOFYEAR CHAR(03);
    DECLARE V_PART_HIERARCHY CHAR(04);
    DECLARE V_HIERARCHY_IN VARCHAR(50);
    DECLARE V_POST_DATE_IN int default 0;
    DECLARE V_POST_DATE_II DATE;
    DECLARE V_POST_DATE DATE;
    DECLARE V_SKEY VARCHAR(50);
    DECLARE V_ACCOUNT_NUMBER VARCHAR(50);
    DECLARE V_COUNTER INT;
    -- FOR DEBUG
    DECLARE V_NUM SMALLINT DEFAULT 0;
    DECLARE V_MSG1 VARCHAR(4000);
    DECLARE V_MSG2 VARCHAR(1);
    DECLARE at_end INT DEFAULT 0 ;
    DECLARE not_found CONDITION FOR SQLSTATE '02000' ;

    -- END
    DECLARE C1 CURSOR with hold FOR
    SELECT SKEY, a.account_number, a.PROC_CCID, POST_DATE, a.blockid, b.hierarchy_num FROM COM.TBASE a,COM.
    cards_to_hierarchy_map b WHERE a.PROC_CCID = 3 and SUBSTR(SKEY,11,1) = '_' and POST_DATE = '2003-01-10' and a.proc_ccid =
    b.proc_ccid and a.account_number = b.account_number and substr(b.hierarchy_num,1,4) in('1410','1412','1413','1813','3354','
    3355','3356','4627','4628','1814','3353','4625','8 240','8243','8244','8260','8261','8262','8263','82 64','8279');

    DECLARE CONTINUE HANDLER FOR not_found
    SET at_end = 1 ;
    Open C1;
    IF (SQLSTATE = '00000')

    FETCH C1 INTO V_SKEY, V_ACCOUNT_NUMBER, V_PROC_CCID_IN, V_POST_DATE_II, V_BLOCKID_IN,V_HIERARCHY_IN;


    SET V_PART_HIERARCHY = substr(V_HIERARCHY_IN,1,4);
    SET V_POST_DATE = char(V_POST_DATE_II);
    SET V_POST_DATE_IN = dayofyear(V_POST_DATE_II);
    SET v_DAYOFYEAR = char(DAYOFYEAR(V_POST_DATE_II));
    SET V_POST_YEAR = char(YEAR(V_POST_DATE_II));

    WHILE V_PART_HIERARCHY IN ('1410','1412','1413','1813','3354','3355','3356', '4627','4628')
    DO
    IF V_POST_DATE_IN < 10
    then
    UPDATE COM.TBASE
    SET JFF_REFERENCE_NUMBER = CONCAT('DEF',CONCAT(CONCAT(CONCAT(SUBSTR(V_POST_YE AR,3,2), '00'), substr(V_DAYOFYEAR,1,1)), BLOCKI
    D)), SKEY = CONCAT('DEF',CONCAT(CONCAT(CONCAT(SUBSTR(V_POST_YE AR,3,2), '00'), substr(V_DAYOFYEAR,1,1)), BLOCKID));
    GOTO EXIT_p;
    ELSE
    IF V_POST_DATE_IN < 100
    THEN
    UPDATE COM.TBASE
    SET JFF_REFERENCE_NUMBER = CONCAT('DEF',CONCAT(CONCAT(concat(SUBSTR(V_POST_YE AR,3,2), '0'), substr(V_DAYOFYEAR,1,2)), BLOCKID)), SKEY = CONCAT('DEF',CONCAT(CONCAT(concat(SUBSTR(V_POST_YE AR,3,2), '0'), substr(V_DAYOFYEAR,1,2)), BLOCKID));

    GOTO EXIT_p;
    ELSEIF V_POST_DATE_IN > 99
    THEN
    UPDATE COM.TBASE
    SET JFF_REFERENCE_NUMBER = CONCAT('DEF',CONCAT(CONCAT(SUBSTR(V_POST_YEAR,3,2) , V_DAYOFYEAR), BLOCKID)), SKEY = CONCAT('DEF
    ',CONCAT(CONCAT(SUBSTR(V_POST_YEAR,3,2), V_DAYOFYEAR), BLOCKID));
    GOTO EXIT_p;

    END IF;
    END IF;
    END WHILE;

    WHILE V_PART_HIERARCHY IN ('1814','3353','4625')
    DO
    IF V_POST_DATE_IN < 10
    THEN
    UPDATE COM.TBASE SET JFF_REFERENCE_NUMBER = CONCAT('DF1',concat(concat(CONCAT (SUBSTR(V_POST_YEAR,3,2), '00'
    ), substr(V_DAYOFYEAR,1,1)), BLOCKID)), SKEY = CONCAT('DF1',CONCAT(CONCAT(concat(SUBSTR(V_POST_YE AR,3,2), '00'), substr(V_
    DAYOFYEAR,1,1)), BLOCKID));

    GOTO EXIT_p;
    ELSE
    if V_POST_DATE_IN < 100
    THEN
    UPDATE COM.TBASE SET JFF_REFERENCE_NUMBER = CONCAT('DF1',CONCAT(CONCAT(concat(SUBSTR(V_POST_YE AR,3,2), '0'
    ), substr(V_DAYOFYEAR,1,2)), BLOCKID)), SKEY = CONCAT('DF1',CONCAT(CONCAT(concat(SUBSTR(V_POST_YE AR,3,2), '0'), substr(V_D
    AYOFYEAR,1,2)), BLOCKID));
    GOTO EXIT_p;
    ELSEIF
    V_POST_DATE_IN > 99
    THEN
    UPDATE COM.TBASE SET JFF_REFERENCE_NUMBER = CONCAT('DF1',concat(CONCAT (SUBSTR(V_POST_YEAR, 3,2), V_DAYOFYEAR)
    , BLOCKID)), SKEY = CONCAT('DF1',concat(CONCAT (SUBSTR(V_POST_YEAR, 3,2), V_DAYOFYEAR), BLOCKID));
    GOTO EXIT_p;
    END IF;
    END IF;
    END WHILE;

    WHILE V_PART_HIERARCHY IN ('8240','8243','8244','8260','8261','8262','8263', '8264','8279')
    DO
    IF V_POST_DATE_IN < 10
    THEN
    UPDATE COM.TBASE SET JFF_REFERENCE_NUMBER = CONCAT('DF2',CONCAT(CONCAT(CONCAT(SUBSTR(V_POST_YE AR,3,2),'00'), S
    UBSTR(V_DAYOFYEAR,1,1)), BLOCKID)), SKEY = CONCAT('DF2',CONCAT(CONCAT(CONCAT(SUBSTR(V_POST_YE AR,3,2), '00'),SUBSTR(V_DAYOF
    YEAR,1,1)), BLOCKID));

    GOTO EXIT_p;
    ELSE
    IF V_POST_DATE_IN < 100
    THEN
    UPDATE COM.TBASE SET JFF_REFERENCE_NUMBER = CONCAT('DF2',CONCAT(CONCAT(CONCAT(SUBSTR(V_POST_YE AR,3,2), '0'), s
    ubstr( V_DAYOFYEAR,2,2)), BLOCKID)), SKEY = CONCAT('DF2',CONCAT(CONCAT(CONCAT(SUBSTR(V_POST_YE AR,3,2), '0'), substr( V_DAYOFYEAR,2,2)), BLOCKID)) ;
    GOTO EXIT_p;

    ELSEIF V_POST_DATE_IN > 99
    THEN
    UPDATE COM.TBASE SET JFF_REFERENCE_NUMBER = CONCAT('DF2',CONCAT(CONCAT(SUBSTR(V_POST_YEAR,3,2) , V_DAYOFYEAR),
    BLOCKID)), SKEY = CONCAT('DF2',CONCAT(CONCAT(SUBSTR(V_POST_YEAR,3,2) , V_DAYOFYEAR), BLOCKID));
    GOTO EXIT_p;
    END IF;
    end if;
    END WHILE;

    EXIT_p:
    SET V_COUNTER = V_COUNTER + 1;
    IF V_COUNTER = 500 THEN
    COMMIT WORK;
    SET V_COUNTER = 0;
    END IF;
    END WHILE;
    COMMIT WORK;
    CLOSE C1;
    END
    @

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I don't think you can find where the trouble is by just looking at the SP code. It's like trying to figure out why your car doesn't start by looking at it from your bedroom window.

    Have you looked at the execution plan? What does the application snapshot show? Do you have any triggers or referential constraints defined on the tables you're updating?

Posting Permissions

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