Hi,

we have a table with about 120 millions of records that grows at 2.5 millions of records per week.

For not letting this table explode we would like to delete old records once a week. Therefore we have an SQL like

As this delete affects about 2.5 million rows it is really slow which seems to be up to the transaction log that gets really big.

Which is the best, so the most performant, way for scuch a mass delete operation?

First came in my mind was to create an index on creationDate column.

Have you given a thought to range partition this table ?

The creationDate column is indexed of course. What slows the statement down seems to be the writing to the transaction log which is independent of the time for selecting the rows.

As the application on this database is a Hibernate one (which should be independent of the db system used) partitioning is not an option as it is not supported by all db systems.

How about UNION ALL view?

Can you please give me some more information on how a UNION ALL view works and might help. Unfortunately I'm not really a DB expert.

Or MDC with DB2_MDC_ROLLOUT registry variable/ CURRENT MDC ROLLOUT MODE special register

I think using a table with data patition instead of your table will be a better solution.
You can use detach command to rollout data quickly .

all doc/info is at
IBM DB2 9.5 Information Center for Linux, UNIX, and Windows
I really do not understand that company's have their db handled by people that do not know anything about it.. and just executing what people from the forum suggest..

temporary alter the table with ACTIVATE NOT LOGGED INITIALLY attribute

Yes agree. On 9.7 the DETACH is much more smooth. The local indexes take away your REORG worries.

You want to have a general solution that works with lot's of different database systems (because you use Hibernate)? Then you have to stick to the common functionality, which is DELETE and which logs all changes. This will be slower than the here proposed solutions to do some sort of partitioning or deactivating logging. All those are approaches specific to DB2. So you have to decide what you want.