checklists are handy sometimes

As a DBA one of the common task is setting up new database(s) for a new application. So when the application is built from scratch you will be involved in the full life cycle of the project. Do you have a handy checklist to validate the configurations for the new databases. What if you dont have one?

Sometimes it is scary because a wrong configuration could result in a huge business loss and thereby putting your job at risk. An example scenario would be not checking on your database backup strategy. Lets say you are in a hurry and did not update trackmod or setup your database for online backups. Hmm You are in trouble now. So it is always good to have a complete checklist of items to be applied on a brand new application. Below is task list that I compiled and it could vary depending on your environment. You may take a print of this image for your reference when you work on building a new application.

First and important category is the kernel settings. Shmmax and shmall are two important parameters that influence the memory allocations at instance and database level. If they are not properly set you might encounter the infamous "Shared memory segments can not be allocated". Following links come handy for setting kernel parameters

Modifying kernel parameters (Linux)
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.db2.luw.qb.server.doc/doc/t0008238.html

Kernel parameter requirements ( Linux )
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.db2.luw.qb.server.doc/doc/c0057140.html

OS user limit requirements (Linux and UNIX)
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.db2.luw.qb.server.doc/doc/r0052441.html

maxfilop - Maximum database files open per application configuration parameter
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.db2.luw.admin.config.doc/doc/r0000280.html

Backup & Recovery settings are vital. If you forget to setup the database for archival logging or if you ignore trackmod ,you would run into issues for sure. Also decide upon the backup strategy well in advance.Do not forget to take offline backup once the database is setup for archival logging. Testing the full cycle of taking backups and restoring it back to fulfill several failure scenarios is essential step for critical applications. After all a DBA`s primary responsibility is to secure the critical data

Initial communication settings like svcename and DB2COMM are trivial but essential for application connections. Sometimes although you have svcename set and DB2COMM set , you might still notice communication issues. Look for db2tcpcm and ipccm in the 'db2pd -edus' output. If you dont see them there do a db2_kill and try again

LOCKTIMEOUT change from -1 : Setting lock timeout to -1 might result in non-terminating lock waits.

STMM configuration : STMM has to turned off/on depending on your workloads and needs

automatic maintainance : Automatic backups , runstats have to be configured

memory settings : Do you want your instance_memory to be automatic or set to a manual value. Things like these have to be decided upfront

diagpath change : diaglogs might grow huge with time and one should not ignore the storage requirements for diaglog and path for diaglog.

Capacity planning is crucial in the initial stages. Do you have adequate memory,cpu and storage resources? Is your applications scalable?

Other house keeping tasks like scheduling backups,runstats,reorgs are important. My list here is just an indicative list and not comprehensive enough to cover all the scenarios. My intention was to pen about the necessity of maintaining checklists as a DBA. I know we all hate documentation but believe me it saves our job sometimes

Shared memory issue::shared memory segments can not be allocated

SQL1084C Shared memory segments cannot be allocated

Have you seen above message anytime? It is annoying when you see this message.Basically first thing you can try is changing instance_memory and database_memory

Important kernel parameters to be considered here are shmmax and shmall.Shmmax is the maximum size of a shared memory segment and shmall is the maximum allocatable shared memory(sum of shared mem segments should be equal or less than this).Recommended value for shmmax is setting it equal to the RAM and for shmall it is 90% of physical memory

ipcs -l is the command to check the values of these kernel parameters

------ Shared Memory Limits --------
max number of segments = 4096 // SHMMNI
max seg size (kbytes) = 32768 // SHMMAX
max total shared memory (kbytes) = 8388608 // SHMALL
min seg size (bytes) = 1

Sometimes you need to increase the value of shmmax to accomodate bigger segment or even increase shmall value to help more overall shared memory.

How do we map shared memory on the box with shared memory parameters on the database and instance??

We use db2pd -memsets,db2pd -mempools to look at the shared memory segments allocated and we try to map it to the original values on the box.My next blog post will reveal the mapping mechanism and the usage of db2pd -memsets,db2pd -mempools commands