Long running transactions

Frequently there are jobs to do on the database which affect quiet a couple of rows, like deleting yesterdays log events from the log table, turning into a long running transaction. While being executed, this may lead to certain lock events, blocking other processes. So what to do about those long runners?

Rule #1 above all others: Avoid long runners. This may be done by turning them into a couple of short runners. Based on the above example, a delete of yesterdays events could be broken down to 24 steps, starting with a
"DELETE ... WHERE event_date=(current_date-1 day) AND event_time>='23:00:00' "
and ending with
"... even_time>='00:00:00' ".

If there's no possibility to go that way, the execution may gain speed by one or more of the following steps:

1. Try to do this in off peak hours to have more resources for your long runner.
2. Increase the size of the buffer pool(s) and the NUM_IOCLEANERS if necessary/possible.
3. Log files should not be placed on the same physical file system as the database itself.
4. Sometimes increasing the parameter LOGBUFSZ has a possitive effect. (Attention: LOGBUFSZ is allocated within DBHEAP so you have to change this value, too.)
   

For point 2-4 you'll need DB2 admin rights on the database. Besides, moving the log files to a different disk often improves all DB2 write actions to the specific database, and so does adding memory to the buffer pools. But when adding memory to those, try to add it step by step and monitor the gain of performance. Usually you'll get to a breaking point somewhere when adding memory doesn't earn you much performance any more. You may also use the high water marks and the log_space_used value when monitoring the usage of memory.