Need to delete one million records in DB2 production database

which platform of db2?
which version?

how many records are in the table?
how do you identify the records you need to delete?

I want to delete all the records < 2010 year

db2 version 8. We have total more than 3 million records..

Currently  I am deleting 1000 records at time..

Delete FROM  DB2INST1.BOD where bod_guid in (select bod_guid from DB2INST1.BOD WHERE YEAR(ARCHIVEDATETIME) < 2010 fetch first 1000 row only);

But it is taking long time for me..

The "FETCH FIRST n ROWS" clause can cause some strange performance issues.

Deleting one million rows out of a total of 3 million rows shouldn't really take THAT long. Try the simple case:

Delete FROM DB2INST1.BOD
 where YEAR(ARCHIVEDATETIME) < 2010;

HTH,
DaveSlash

Select allOpen in new window

If my previous suggestion takes exorbitantly long, try copying out the records you want to KEEP into another table. Then clear the original table altogether and copy the desired records back into the original table.

Lastly, if the previous two suggestions don’t work for you, I have one last suggestion that has worked for me in the past. Try dropping all dependent files (such as indexes and views) and try the full DELETE again. After it completes, recreate your indexes and views. Just make sure you have the definition of the indexes and views before you drop them.

All that being said, I still think the “simple-case” delete will work fine.

I have so many dependent tables..
Is there any other way?
Creating sp commiting 1000 rows at time..

Thank You..