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

Did you check the *.nfy and db2diag.log file for any lock
escalation


regards

Mujeeb

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.

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
@

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?