Need to delete one million records in DB2 production database

vvsrk76
vvsrk76 used Ask the Experts™
on
Please help me in this one..


Thanks for your time..
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
which platform of db2?
which version?

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

Author

Commented:
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..
Dave FordSoftware Developer / Database Administrator

Commented:
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

Open in new window

Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Dave FordSoftware Developer / Database Administrator

Commented:
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.

Author

Commented:
I have so many dependent tables..
Is there any other way?
Creating sp commiting 1000 rows at time..
deleting all million in one shot is a bad idea,
if for some reason an error happens, rolling it back will be a disaster

if you have many dependent tables, i would try to delete the records from those tables before i delete them from the master table (i assume your foreign keys are defined with on delete cascade)

also, i would make sure that all delete statements are using indexes,
your 1000 at a time approach is the way to go,
if you manually delete from the dependent tables first, you might want to try deleting 5000 every time,
just play with it and see what works best

but never,
never never
1 million in a single statement

after that, you should run reorg on all those tables

Author

Commented:
Thank You..

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial