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
And 16 Gb system
kernel.sem=250 256000 32 4096
After editing sysctl.conf file you have to load these settings with
And test if DB2 starts without errors. In Ubuntu these settings are loaded automatically during boot up.
- WORK IN PROGRESS -