Optimizing DELETE operations on big tables in #DB2

By | November 21, 2017

Deletes go very slow if the log buffer size is too small and we delete lots of data from big tables. It is very important to have the right settings for log buffer size to avoid having to frequent writes to the disk, writes that cause a high IO traffic that will slow down the DELETE.

STEP 1. Check the size of the log buffer size.

This parameter allows you to specify the amount of the database heap (defined by the DBHEAP parameter) to use as a buffer for log records before writing these records to disk.

STEP 2. Check the size of Database heap

Note that to increase LOGBUFSZ you must increase also DBHEAP (LOGBUFSZ is allocated from DBHEAP).

STEP 3. Increase DBHEAP

STEP 4. Increase LOGBUFSZ

STEP 5. Test

Several tests with different values have to be made to ensure that just the right size is set. It is important to note that a value too big will start to affect other things sooner or latter.

Contribute to this site maintenance !

This is a self hosted site, on own hardware and Internet connection. The old, down to earth way 🙂. If you think that you found something useful here please contribute. Choose the form below (default 1 EUR) or donate using Bitcoin (default 0.0001 BTC) using the QR code. Thank you !

€1,00

Advertisements

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.