db2 ese 9.5 fp8 on P/linux
we have a large table that needs to be cleaned all the time
this tables keeps requests for executions and needs to be cleaned if exec is finished
we use delete from batch.message where id in (select id from batch.message where work_id=? fetch first 10000 rows only)
we added the fetch otherwise we get -964 (logfull) and we already have many logfiles for this db
the delete takes about 200 sec
the table has been declared as volatile as the size is changing in size all the time and stats would never be up-to-date

any good idea to speed-up this process ?

Write a stored procedure to do the deletes. A cursor WITH HOLD option should be created, but using UR islolation level. Then delete rows that match your criteria (separate delete and not "where current of cursor"). Do commits often to minimize lock contention on the delete (original cursor will stay open if WITH HOLD is used).

Thanks Marcus
just a small remark : we have noticed when doing delete from store proc, although using intermediate commit the logfiles are not released - we could see more and more logfiles allocated and finally out of logfiles..
it seems the call .. is seen as 1 uow..

I do not see a point in declaring a cursor.
The delete can be made simpler like

delete from (select * from batch.message where work_id=? fetch first 10000 rows only)

and repeat till SQLCODE is 100.

Interesting observation about logs not being released!

Cheers
Sathy

Purpose of declaring cursor is:

• to minimize lock contention by doing frequent intermediate commits
• reduce active log size by doing frequent intermediate commits

There should not be a problem with active log size if the stored proc is used with frequent commits, although you will have to prune the archive logs frequently.

Consider using an MDC, organized by work_id. It would be especially efficient in DB2 9.7 though, where you'd have asynchronous block roll-out and index maintenance.