Optimizing DELETE operations on big tables in #DB2

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

STEP 4. Increase LOGBUFSZ

[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.