Avatar of vvsrk76
vvsrk76

asked on 

Need to delete one million records in DB2 production database

Please help me in this one..


Thanks for your time..
DB2

Avatar of undefined
Last Comment
vvsrk76
Avatar of momi_sabag
momi_sabag
Flag of United States of America image

which platform of db2?
which version?

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

ASKER

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..
Avatar of Member_2_2484401
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

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.
Avatar of vvsrk76
vvsrk76

ASKER

I have so many dependent tables..
Is there any other way?
Creating sp commiting 1000 rows at time..
ASKER CERTIFIED SOLUTION
Avatar of momi_sabag
momi_sabag
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of vvsrk76
vvsrk76

ASKER

Thank You..
DB2
DB2

IBM DB2 is a family of relational database server products developed by IBM that have been extended to support object-relational features and non-relational structures like JSON and XML. There are three main products in the DB2 family: DB2 for Linux, UNIX and Windows (informally known as DB2 LUW), DB2 for z/OS (mainframe), and DB2 for i (formerly OS/400), plus a version for IBM's Virtual Storage Extended (VSE) operating system.

6K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo