How to look for db2 connections leak?

During the execution of a scalability and performance test the db2 connections tipically increase, because more clients are performing requests to the database and are increasing the load. But after you finish the test, you would like to verify that database connections start decreasing as number because not they are not more needed and they should go close to the number of the start.

An exception to this reasoning is when the clients are connecting with a connection pool that keeps always a minimun number of connections always open to avoid  wasting time opening and closing them. Then in this case even if you completed the tests, do not get you surprised when you return back the morning later and see that the number of connections opened are the same like the night before. This is right when this number is the same like the minimum connections number defined on the connection pool.

Check with these few rows to run in a shell:

su - db2instance_user

while (true) do

timenow=$(date +'%Y%m%d-%H%M%S'); 
connections=$(db2 list applications for db yourdb show detail | grep "string to match" | wc -l);
echo $timenow, $connections
sleep 60
done


"string to match", can be db2jcc_application or the ip address of the server where is running the application that perform the requests.

If the number of connections displayed do not  decrease and remains alive without any reason there are some potential connection leak. Else investigate if on the client there is still actually some activity.


There is also a DB2 monitor view to check the connections their start time and idle wait time:

db2 "select varchar(application_name,20), application_handle as appl_id, CONNECTION_START_TIME, CLIENT_IDLE_WAIT_TIME/1000 from table(MON_GET_CONNECTION(NULL,-1)) order by CLIENT_IDLE_WAIT_TIME"

A high client idle time may indicate performance issues that need to be addressed on the client rather than the server.