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.

[db2inst1@localhost ~]$ db2 get db cfg for MyDB | grep LOGBUFSZ
Log buffer size (4KB)                        (LOGBUFSZ) = 4096

STEP 2. Check the size of Database heap

[db2inst1@localhost ~]$ db2 get db cfg for MMS | grep DBHEAP
Database heap (4KB)                            (DBHEAP) = AUTOMATIC(4546)

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

STEP 3. Increase DBHEAP

[db2inst1@localhost ~]$ db2 -v update database cfg for MMS using DBHEAP 9092
[db2inst1@localhost ~]$ db2 -v terminate


[db2inst1@localhost ~]$ db2 -v update database cfg for MMS using LOGBUFSZ 8196
[db2inst1@localhost ~]$ db2 -v terminate

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 !


Leave a Reply

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