Why does DB2 take so long to run a simple SQL logic?

[pradchal1]

I have a simple query that runs against a DB2 database table with 200,000 rows of data. The table has about 10 columns. But my SQL query takes too long to run almost filling up the log. It is a simple logic. Can anyone suggest me anything I can do to make this any faster?
My SQL is:

delete from B;
insert into B
select * from A source where effdt =
( select max(effdt) from A sub
where sub.TRNSFR_SRC_ID = source.TRNSFR_SRC_ID
and sub.COMP_SUBJECT_AREA = source.COMP_SUBJECT_AREA
and sub.TRNSFR_CMP_SEQ = source.TRNSFR_CMP_SEQ
and sub.TRNSFR_EQVLNCY_CMP = source.TRNSFR_EQVLNCY_CMP
and effdt <= CURRENT DATE);

Basically, I want the highest effective dated row where the keys (TRNSFR_SRC_ID, COMP_SUBJECT_AREA, TRNSFR_CMP_SEQ AND TRNSFR_EQVLNCY_CMP) match.

Any suggestion or help would be greatly appreciated.

 

[TysonLPrice]

I don't know if DB2 allows you to Truncate a table versus delete. In SQL it's faster with less logging and you might try that.

If you are on an IBM mainframe try posting this on

http://www.mvshelp.com.

Your first few posts are free but after that you have to pay.