db2 - delete in batches

[Ramya Ramaswamy]

Hi,

We have a requirement to do a mass delete on huge tables (One of tables has
~ 400000000 rows). We need to clear out data older than a particular date.
Deleting the rows at one shot does not work, the operation is rolled back.

Any pointers as to how to perform the delete in batches would be helpful.

Would it be possible to delete the rows without logging? Will that allow
huge mass deletes?

Thanks!!
Ramya

----------------------------------------------------------------------
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to ***@bama.ua.edu with the message: GET IBM-MAIN INFO
Search the archives at http://bama.ua.edu/archives/ibm-main.html

[E***@ibm-main.lst]

In a message dated 1/8/2006 8:56:02 A.M. Central Standard Time,
***@GMAIL.COM writes:

Would it be possible to delete the rows without logging? Will that allow
huge mass deletes?
Off the top, Unload the table, drop the table, create new table,
load the table with NOLOG and date greater than.

----------------------------------------------------------------------
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to ***@bama.ua.edu with the message: GET IBM-MAIN INFO
Search the archives at http://bama.ua.edu/archives/ibm-main.html

[Isaac Yassin]

Hi,
1. REORG DISCARD
2. If it's partitioned TS look at LOAD of DUMMY file into the partition to be deleted.
3. Don't delete in 1 SQL. Write a program that will commit every X rows. (X ~~ 500) . However, you'll log in this case.


Isaac Yassin

-----Original Message-----
From: IBM Mainframe Discussion List [mailto:IBM-***@ibm-main.lst
Sent: Sunday, January 08, 2006 4:55 PM
To: IBM-***@BAMA.UA.EDU
Subject: db2 - delete in batches

Hi,

We have a requirement to do a mass delete on huge tables (One of tables has ~ 400000000 rows). We need to clear out data older than
a particular date.
Deleting the rows at one shot does not work, the operation is rolled back.

Any pointers as to how to perform the delete in batches would be helpful.

Would it be possible to delete the rows without logging? Will that allow huge mass deletes?

Thanks!!
Ramya

----------------------------------------------------------------------
For IBM-MAIN subscribe / signoff / archive access instructions, send email to ***@bama.ua.edu with the message: GET IBM-MAIN
INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html

----------------------------------------------------------------------
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to ***@bama.ua.edu with the message: GET IBM-MAIN INFO
Search the archives at http://bama.ua.edu/archives/ibm-main.html

[Mike Bell]

DB2 listserv is http://www.idugdb2-l.org/archives/db2-l.html

Various answers

1. Since at least V6, the REORG utility has a DISCARD capability. The
selection criteria capability varies by release.
2. Batch SQL delete without rollback, use LOCK TABLE EXCLUSIVE. That
will prevent lock escalation but deletes are still logged.
NOTE - no other user can use the table while this is running.
NOTE!!!!!!! be very carefull if you decide to do this, You can create
millions of deleted records on the DB2 logs. Your logs may not be
prepared to handle this.
3. Write a program - delete 100 rows, commit, delete next 100,
repeat until complete. this allows other users to access the table but does
NOT remove the logging requirement.

Mike

Post by Ramya Ramaswamy
Hi,
We have a requirement to do a mass delete on huge tables (One of tables has
~ 400000000 rows). We need to clear out data older than a particular date