How to set maximum number of connections to a database in db2

It is always very important to optimize the number of required connections to the database . This tutorial covers that how to optimize / set the number of database connections for your DB2 database. In DB2 , there are two main configuration parameters to be set to fix the number of required connections to a databse. The following parameters are to be configured which is applicable to the DB2 pre-Version 9.5
1. MAXAGENTS
2. MAXAPPLS
1. MAXAGENTS : the total number of connections allowed by all the databases available with any database instance. The acceptable value range of MAXAGENTS is 1 to 640000 . To get existing value of MAXAGENTS , give the following command.
For windows , with db2 prompt
db2 get dbm cfg | find "MAXAGENTS"
For Linux ,
db2 get dbm cfg | grep MAXAGENTS
Output of the above command in windows :

  
  Max number of existing agents               (MAXAGENTS) = 400
  Max number of coordinating agents     (MAX_COORDAGENTS) = (MAXAGENTS - NUM_INITAGENTS)

the above output shows (MAXAGENTS) = 400 , that means , the maximum number of database manager agents, including coordinator agents or subagents, available to accept requests by application .
For example , you have 3 databases , suppose you expect 100 connections for each database . Then you have to set MAXAGENTS to 300 + 30 = 330 , where 30 (10+10+10 for each db) additional connections to act as your buffer connections for doing any DB2 background processes. It may be less than 10 , which depends upon your requirement. Why you need to set 330 ? why not 64000 ? . As each connection (i.e. if MAXAGENTS = 64000, then 64000 connections ) is allocated a portion of private memory for an agent on your database server , if you set your MAXAGENTS to larger values which you may not require, leads to a negative performance . There is chance to get the error insufficient memory to allocate your shared memory segment , due to this database might not start . So set large value only after you determine what your optimal number of connections .
Now let us see , how to change the value of MAXAGENTS . To change the value to 330 , give the following command
db2 update dbm cfg using MAXAGENTS 330

2. MAXAPPLS : This parameter is used to define the total number of connections that are allowed by the particular database. The valid range of parameter MAXAPPLS is 1 to 60000. To get current value of the MAXAPPLS parameter for the database Employee , run the following commands

db2 connect to employee
For windows , with db2 prompt
db2 get db cfg for employee | find "MAXAPPLS"
For Linux ,
db2 get dbm cfg | grep MAXAPPLS
Output of the above command in windows :

  
Catalog cache size (4KB)              (CATALOGCACHE_SZ) = (MAXAPPLS*4)
 Package cache size (4KB)                   (PCKCACHESZ) = (MAXAPPLS*8)
 Max number of active applications            (MAXAPPLS) = 50 

To change the MAXAPPLS parameter value to 100 for the database Employee
db2 connect to Employee
db2 update db cfg for employee using MAXAPPLS 100 

When you change the value of MAXAPPLS , it should satisfy the formula "maxappls * maxlocks >= 100" , where maxlocks is the maximum percent (%) of lock list before escalation. When the number of locks held by any one application reaches this percentage , escalation will occur for the locks held by that application where escalation is the process to replace row locks with table locks, to reduce the number of locks in the list . Default value for maxlocks in windows is 22 and in linux is 10 . Valid range is 1 to 100 . The recommended formula to calculate max locks is maxlocks = 2 * 100 / maxappls 
The value of MAXAGENTS should be at least the sum of the values of MAXAPPLS in each database allowed to be accessed concurrently. Let us see the following example for setting the value to MAXAPPLS and MAXAGENTS

Suppose we have 2 databases . dbtest1 , db2test2 . Both databases are created under same instance. Let us assume that one server is connected to dbTest1 and expected connections to the database through the server is 70 and two servers are connected with db2Test2 database that requires 50 connections each . Now what will be the values for MAXAGENTS and MAXAPPLS to optimize number of database connections. The value for the parameter MAXAGENTS will be 170 + 20, where 70 connections are for dbTest1 , 100 connections are for dbTest2 . 10 buffer connections for each database and total of 20 connections for db2 backend process. Now we have to set the value to MAXAPPLS parameter . The value for for the MAXAPPLS parameter for the database dbTest1 will be 70 , dbTest2 will be 100 . Commands to do the above settings would be as follows

  
db2 update dbm cfg using MAXAGENTS  190
db2 connect to dbTest1
db2 update db cfg for dbTest2 using MAXAPPLS  70
db2 terminate
db2 connect to dbTest2
db2 update db cfg for dbTest2 using MAXAPPLS  100
db2 terminate

Note : This parameter is being used by pre-Version 9.5 data servers and clients. Any value specified for this parameter will be ignored by the database manager in DB2 Version 9.5 or later.