db2 trigger and foreign key constraint perfomace issue

[devangmehta]

Hi,

I m very new to DB2.
I m facing problem while creating a trigger on a table which has 5 milllions row

I am using stored procedure to update this table like this

update tan_detail set lst_id = 'RECFDM' where ack_no = in_parameter;

where ack_no has index created on it.

And calling this procedure using jdbc and it is taking 1 to 2 millisecond to complete (time calulated using java date time function).

But when i create simple trigger on this table like



CREATE TRIGGER PLT_STUPD_UPD AFTER UPDATE ON TAN_DETAIL
REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL WHEN (N.LST_ID = 'HDDELV')
BEGIN ATOMIC

DECLARE LLST_ID VARCHAR(6);
DECLARE CNT INTEGER DEFAULT 0;

-- SET LLST_ID = LTRIM(RTRIM(VALUE(N.LST_ID,'')));



END
@

The time taken to run this procedure is increased to 250 to 400 millisecond and
we need to call this procedure 50000 thousand time during EOD and BOD. So it is not affordable.

Same perfomace degradation is noticed when i crate foreing key on lst_id column references to lcm_status_master which has 40 rows and reverse scan index also created on lst_id column

Any suggetion to solve this problem is very helpful or by defaul DB2 suffers performacne laking when creating trigger or foreing key?

Thx in advance

Devang.