IBM Support

SQL1226N due to many db2agent processes attached to the instance

Question & Answer


Question

If many db2agent processes remain attached to an instance, they may use up all of the agents (MAXAGENTS) and cause new connections to trigger a SQL1226N error, and ADM7009E can be seen in the db2diag.log file.

Cause

If applications do not detach from instance, they may use up all agents (MAXAGENTS) and new connections will trigger a SQL1226N error. Also, the following ADM7009E error message may be seen in db2diag.log file:

2005-02-21-09.37.22.327442 Instance:test Node:000

PID:5352(db2tcpcm 0) TID:1 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 output of ps -ef or db2ptree (Solaris) shows db2agent processes are attached to the instance as in the following example:


test 4012 0.0 0.512302479384 ? S Feb 17 5:10 db2agent (instance) 0
test 2689 0.0 0.511719267592 ? S 12:14:06 0:00 db2agent (instance) 0

25398 db2tcpcm 0
80 db2agent (instance) 0
118 db2agent (instance) 0
784 db2agent (instance) 0
3093 db2agent (instance) 0
4343 db2agent (SAMPLE) 0
4441 db2agent (instance) 0
4465 db2agent (instance) 0
4770 db2agent (instance) 0
7753 db2agent (instance) 0

Answer

For DB2® Universal Database™ (DB2 UDB) Version 8.2 (Version 8.1 FixPak 7 or later), you can use the db2pd tool with the -agents option to determine the application handle ("ApplHandl"), the process ID of the client process ("ClientPid"), the userid running the agent ("UserId") and the name of the client process ("ClientNm"). Here is some example output:

$ db2pd -agents

Database Partition 0 -- Active -- Up 0 days 00:00:32

Agents:
Current agents: 1
Idle agents: 0
Active agents: 1
Coordinator agents: 1

Address AppHandl [nod-index] AgentPid Priority Type State ClientPid Userid ClientNm Rowsread Rowswrtn LkTmOt DBName
0x0780000000125A20 7 [000-00007] 2576432 0 Coord Active 1288 TESTER db2bp.ex 0 0 0 n/a

You can then force the ApplHandl off to free up agents. For example:
db2 "force application (7)"

For earlier versions of DB2 UDB, you will need to get the db2spec tool (use option: db2spec - GPOOL) from DB2 UDB support in order to determine the name of the application that remains attached to instance.

[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Performance - General\/Tuning","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF027","label":"Solaris"}],"Version":"8;7","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018

UID

swg21202489