I faced a problem yesterday. The number of connections to our database was very high. At a point of time, the users were not able to access the database. When I tried to login as db2inst1 to our DB Server(OS-RHEL 6.5, DB2 10.5), there was an error "su:cannot set userid Resource temporarily unavailable".
After sometime, this was automatically resolved.
Is this due to the ulimit setting on db2inst1? It was 1024. I increased it later.
The entry in db2diag.log was as follows.
2015-03-30-22.01.45.834611+330 I17881674E502 LEVEL: Severe
PID : 78131 TID : 139775726053120 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000
EDUID : 22 EDUNAME: db2tcpcm 0
FUNCTION: DB2 UDB, base sys utilities, sqeAgentServices::RequestAgentForAppl, probe:2081
RETCODE : ZRC=0xFFFFFB37=-1225
SQL1225N The request failed because an operating system process,
thread, or swap space limit was reached.
db2inst1.nfy entry is as follows
ADM7519W DB2 could not allocate an agent. The SQLCODE is "-1225".
2015-03-30-22.02.50.973312 Instance:db2inst1 Node:000
PID:78131(db2tcpcm 0) TID:327149312 Appid:none
common communication sqlcctcpconnmgr_child Probe:125
ADM7009E An error was encountered in the "TCPIP" protocol support. A possible
cause is that the maximum number of agents has been exceeded.
The database configuration parameters are as follows
Priority of agents (AGENTPRI) = SYSTEM
Agent pool size (NUM_POOLAGENTS) = AUTOMATIC(100)
Initial number of agents in pool (NUM_INITAGENTS) = 0
Max number of coordinating agents (MAX_COORDAGENTS) = AUTOMATIC(200)
Max number of client connections (MAX_CONNECTIONS) = AUTOMATIC(MAX_COORDAGENTS)
Is there any maximum values for MAX_COORDAGENTS or MAX_CONNECTIONS if they are set to AUTOMATIC.
Please help. I am still not able to find out whether it's an OS related problem or DB2 Agent problem.
Probably this is a configuration problem for your operating system.
You did not quantify "very high" number of connections, you also did not mention whether the application layer uses any connection-pooling technique - these are also relevant factors, which may have their own levers for controlling this kind of issue.
As root, check /etc/security/limits.conf for the value of "nproc" for hard and soft (in case the symptom happened because the per-process limit on threads was reached).
The soft limit might need to be increased, and/or new lines added for the your sysadm_group groupname - involve your sysadmin first.
In my case I needed to add these lines in the past (db2iadm1 is the group that is sysadm_group for the relevant db2 instance), but the actual numbers can vary with your physical resources (#cores, #ram , shmem etc).
@db2iadm1 soft nproc 16384
@db2iadm1 hard nproc 17408
And for another issue I had to (temporarily) use :
* hard core unlimited