deleting records without writing to the transaction logs

[vb]

I need to delete records from several large tables - 1 million plus
rows. Some of these tables have nested foreign key relationships.
When I perform the delete statements, it can take forever as the
database works out the key relationships and commits things to the log.

Is there a way to speed up some of these deletes with out writing the
information to the database logs?

I am going to probably drop the RI on the tables and manually delete
from the child tables to improve things.

Any tips or suggestions would be appreciated. We have been using the
db2 loader with "replace" functionality but have new requirements that
prevent us from overwriting all the data.

Thanks,
Michael

[Serge Rielau]

[e-mail] wrote:

I need to delete records from several large tables - 1 million plus
rows. Some of these tables have nested foreign key relationships.
When I perform the delete statements, it can take forever as the
database works out the key relationships and commits things to the log.

Is there a way to speed up some of these deletes with out writing the
information to the database logs?

I am going to probably drop the RI on the tables and manually delete
from the child tables to improve things.

Any tips or suggestions would be appreciated. We have been using the
db2 loader with "replace" functionality but have new requirements that
prevent us from overwriting all the data.

Thanks,
Michael

You can try teh following (I have no clue if all steps will work):
1. Turn the RI into NOT ENFORCED (using ALTER TABLE).
2. ACTIVATE NOT LOGGED INITIALLY (be aware of what happens in case of
error!)
3. DELETE (make sure auto-commit is OFF if you use CLP
4. COMMIT
5. SET INTEGRITY OFF for the involved tables
6. Turn the RI back on
7 SET INTEGRITY using UNCHECKED

You can also just turn RI back on without SET INTEGRITY but DB2 will
double check which may or may not take too long for your taste.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Joachim Klassen]

How about unloading the rows which shall remain in the tables,reload them
with REPLACE and SET INTEGRITY IMMEDIATE UNCHECKED.
This works well when the amount of records to be kept is much smaller than
the amount which must be deleted.

HTH
Joachim
<[e-mail]> schrieb im Newsbeitrag
news:11**********************@c13g2000cwb.googlegr oups.com...

I need to delete records from several large tables - 1 million plus
rows. Some of these tables have nested foreign key relationships.
When I perform the delete statements, it can take forever as the
database works out the key relationships and commits things to the log.

Is there a way to speed up some of these deletes with out writing the
information to the database logs?

I am going to probably drop the RI on the tables and manually delete
from the child tables to improve things.

Any tips or suggestions would be appreciated. We have been using the
db2 loader with "replace" functionality but have new requirements that
prevent us from overwriting all the data.

Thanks,
Michael

[Buck Nuggets]

[e-mail] wrote:

I need to delete records from several large tables - 1 million plus
rows. Some of these tables have nested foreign key relationships.
When I perform the delete statements, it can take forever as the
database works out the key relationships and commits things to the log.
Is there a way to speed up some of these deletes with out writing the
information to the database logs?

Don't know if this will work for you, but in case you don't want to
drop your ri constraint & activate non-logging, and if your concern
about deletion time is primarily that it is slowing down a linear
workflow...

Then you can move the delete step out of the normal load workflow into
an independent process. This process would get scheduled to run at
frequent intervals (ex: every 5 minutes), would delete all candidate
rows up to a small amount (ex: 50,000 rows). This approach won't
lessen the load on the server, and won't delete those rows prior to
some other process. But it will do it with minimal impact to logs &
locking, and might allow you to complete the load process more quickly.
Of course, you'll want to be able to quickly find the candidate rows
with an index or mdc.

buck

Perhaps you could delete the rows using a process that is scheduled
indepdently , continually deletes (say) 50,000 rows at a time.