DB2, Linux and Memory

It is extremely possible that you have problems running DB2 under Ubuntu Lucid. Installation and everything went smoothly, but DB2 crashes or just will not start. Problem is that default settings for shared memory etc. are not sufficient, and you have to manually set these. This is the only annoying thing in DB2 9.7 installation. I did not encounter problems of this kind while using 9.1, and I certainly did not have to manually set any memory parameters.


If you do not want to fiddle with parameter optimization, I strongly advise you to use DB2 9.1 instead of version 9.5 or 9.7 - especially if you are using Linux. I have tested extensively both 9.1 and 9.7 on a very large and complex database, and my statistics show that 9.1 outperforms 9.7. In general, execution time for queries in 9.7 was doubled, and in certain cases even worse. I did not find any query where 9.7 was faster than 9.1. There were other problems, too. Database creation and data transfer scripts used in DB2 9.1 (where they executed flawlessly in 15 minutes) took hours in 9.7 and often crashed the whole machine.


Why? First of all, STMM (Self-Tuning Memory Management) does not work properly. Also, the change from processes to threads possibly affected performance. If you do not need those new goodies 9.7 offers then just use 9.1, which is solid performer.


Unfortunally, what parameter values you have to use, depend totally on your machines physical memory. So it is impossible to give here exact values for every situation. These are only examples, and it might be necessary for you to adjust them.

Depending on physical memory of your system you should edit kernel parameters in /etc/sysctl.conf file according to these guidelines

IPC kernel parameter	9.7 Fix Pack 1 or earlier	9.7 Fix Pack 2 or later fix packs
kernel.shmmni (SHMMNI)	no value is enforced	256 *(size of RAM in GB)
kernel.shmmax (SHMMAX)	268 435 456 (32-bit kernel) 1 073 741 824 (64-bit kernel)	(size of RAM in bytes)
kernel.shmall (SHMALL)	no value is enforced	2 *(size of RAM in bytes) (setting is in 4K pages)
kernel.sem (SEMMNI)	1 024	256 *(size of RAM in GB)
kernel.sem (SEMMSL)	no value is enforced	250
kernel.sem (SEMMNS)	no value is enforced	256 000
kernel.sem (SEMOPM)	no value is enforced	32
kernel.msgmni (MSGMNI)	1 024	1 024 *(size of RAM in GB)
kernel.msgmax (MSGMAX)	65 536	65 536
kernel.msgmnb (MSGMNB)	65 536	65 536

Here is an example of 4 Gb system

kernel.sem=250 256000 32 1024
kernel.shmmax=2147483648
kernel.shmmni=4096
kernel.shmall=773541
kernel.msgmnb=65536
kernel.msgmax=65536
kernel.msgmni=1024


And 16 Gb system


kernel.shmmni=4096
kernel.shmmax=17179869184
kernel.shmall=8388608
#kernel.sem=
kernel.sem=250 256000 32 4096
kernel.msgmni=16384
kernel.msgmax=65536
kernel.msgmnb=65536


After editing sysctl.conf file you have to load these settings with

sysctl -p

And test if DB2 starts without errors. In Ubuntu these settings are loaded automatically during boot up.

– WORK IN PROGRESS –