Here would be my recommendation.

Export the 2.4 million rows you want to keep using the select * statement.

Load replace the data that you exported. This will eliminate the need to
log the deletes. Loading 2.4 million rows is a lot faster than deleting 5.7
million rows.

You should be able to tell how far the process is by doing a get snapshot
(if it is almost done, then you can forget the above .... but consider for
the future).

If you kill the process now, you will incure the expense of rolling back
what deletes you have done.

RI is a two way street - do you have other tables that reference this one
in their RI structure?


hth












"L Dba "
<db2udbdba-ezmlms
hield-x56418554.x To
[Email Address Removed] "LazyDBA Discussion"
a.com> <[Email address protected]
cc
07/14/2004 10:09
AM Subject
Slow deletes










Hi all,

We are attempting to delete 5.7 million rows from an 8.1 million row table
that only contains a primary key index using the following delete
statement:

delete from tablename where nonindexed_field in (1, 2, 3) and
nonindexed_field > 8

This command has now been running for more than 26 hours. All foreign keys
that accessed the table were dropped previously. We were able to select
the data before the delete started using the following statement:

select * from tablename where nonindexed_field in (1, 2, 3) and
nonindexed_field > 8

This query took less than 3 minutes to return the full data set.

Can anyone give me any tips on how to troubleshoot this issue? What could
be slowing down this delete?

Thanks In Advance,
D


---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: db2udbdba-[Email address protected]


ForwardSourceID:NT0001A2B6


DB2 & UDB email list listserv db2-l LazyDBA home page