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.
Need to delete one million records in DB2 production database
Please help me in this one..
Thanks for your time..
Thanks for your time..
Do more with
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?
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..
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:
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
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.
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.
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
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial