Update performing very slow/hung
Posted: Mon Sep 03, 2012 9:53 am
Hi,
We have a sequencer which first(1st job) inserts x records and then it updates(2nd job) x records.
Update happens thru shellscript and called by an execute command stage.
Table: DB2
Table Volume:125 million
Table is not partitioned
Table has index and the update column where clause cols are also indexed.
sql:
UPDATE H a SET C_IND='N'
WHERE EXISTS (SELECT 1 FROM H_upd b
WHERE a.S_ID=b.S_ID and
a.C_CD=b.C_CD and
a.E_DT=b.E_DT and
a.L_NBR=b.L_NBR and
a.UPDT_TMSTMP=b.UPDT_TMSTMP)
Now, the job/update runs forever and it can't even update 1000's of records. We even try to do the runstats against each run, no luck.
can you please suggest me the best approach in order to resolve this issue.
We have a sequencer which first(1st job) inserts x records and then it updates(2nd job) x records.
Update happens thru shellscript and called by an execute command stage.
Table: DB2
Table Volume:125 million
Table is not partitioned
Table has index and the update column where clause cols are also indexed.
sql:
UPDATE H a SET C_IND='N'
WHERE EXISTS (SELECT 1 FROM H_upd b
WHERE a.S_ID=b.S_ID and
a.C_CD=b.C_CD and
a.E_DT=b.E_DT and
a.L_NBR=b.L_NBR and
a.UPDT_TMSTMP=b.UPDT_TMSTMP)
Now, the job/update runs forever and it can't even update 1000's of records. We even try to do the runstats against each run, no luck.
can you please suggest me the best approach in order to resolve this issue.