银行
应用程序在连接时候会报SQL1223N的错误
最近应用程序在连接时候会报SQL1223N的错误。
db2 ? SQL1223N的解释已经了解。
[size=+0]通过db2pd -agent 抓的数据如下，在短短2分钟内，agents数目的使用波动如此之大，好困惑。
[size=+0]Database Partition 17 -- Active -- Up 112 days 07:11:34 -- Date 2012-12-28 02:41:00
[size=+0]Agents:
Current agents: 142
Idle agents: 1
Active coord agents: 137
Active agents total: 141
Pooled coord agents: 0
Pooled agents total: 0
[size=+0]
Database Partition 17 -- Active -- Up 112 days 07:12:15 -- Date 2012-12-28 02:41:41
Agents:
Current agents: 575
Idle agents: 1
Active coord agents: 571
Active agents total: 574
Pooled coord agents: 0
Pooled agents total: 0
Database Partition 17 -- Active -- Up 112 days 07:12:56 -- Date 2012-12-28 02:42:22
Agents:
Current agents: 489
Idle agents: 1
Active coord agents: 485
Active agents total: 488
Pooled coord agents: 0
Pooled agents total: 0
Database Partition 17 -- Active -- Up 112 days 07:14:17 -- Date 2012-12-28 02:43:43
Agents:
Current agents: 27
Idle agents: 1
Active coord agents: 23
Active agents total: 26
Pooled coord agents: 0
Pooled agents total: 0
db2diag.log里面
2012-12-28-02.38.40.429663+480 I30975463A531 LEVEL: Error
PID : 3777250 TID : 1 PROC : db2agent (DBNAME) 3
INSTANCE: instance NODE : 003 DB : DBNAME
APPHDL : 3-1563 APPID: *N1.instance.121227183818
FUNCTION: DB2 UDB, database utilities, DIAG_ERROR, probe:0
DATA #1 : String, 175 bytes
LOADID: 3777250.2012-12-28-02.38.34.708708.3 (42;7288)
Coord: error encountered during catalog agent processing. , -27902, 0, Detected in file:sqlusMPPCoordinator.C, Line:1324
2012-12-28-02.38.40.435066+480 I30975995A510 LEVEL: Error
PID : 3777250 TID : 1 PROC : db2agent (DBNAME) 3
INSTANCE: instance NODE : 003 DB : DBNAME
APPHDL : 3-1563 APPID: *N1.instance.121227183818
FUNCTION: DB2 UDB, database utilities, DIAG_ERROR, probe:0
DATA #1 : String, 154 bytes
LOADID: 3777250.2012-12-28-02.38.34.708708.3 (42;7288)
Error during processing of catalog sub-agent. , -27902, 0, Detected in file:sqluCatalog.C, Line:691
2012-12-28-02.38.40.451328+480 I30976506A472 LEVEL: Error
PID : 3777250 TID : 1 PROC : db2agent (DBNAME) 3
INSTANCE: instance NODE : 003 DB : DBNAME
APPHDL : 3-1563 APPID: *N1.instance.121227183818
FUNCTION: DB2 UDB, database utilities, DIAG_ERROR, probe:0
DATA #1 : String, 116 bytes
LOADID: 3777250.2012-12-28-02.38.34.708708.3 (42;7288)
sqluv_rollback , 0, 2, Detected in file:sqluvutl.C, Line:1172
2012-12-28-02.38.40.453022+480 I30976979A399 LEVEL: Severe
PID : 3777250 TID : 1 PROC : db2agent (DBNAME) 3
INSTANCE: instance NODE : 003 DB : DBNAME
APPHDL : 3-1563 APPID: *N1.instance.121227183818
FUNCTION: DB2 UDB, database utilities, sqlu_register_table_load, probe:10
MESSAGE : Load Error: Failed getting Load catalog info.
2012-12-28-02.41.10.510550+480 I30977379A389 LEVEL: Error
PID : 468080 TID : 1 PROC : db2ipccm 17
INSTANCE: instance NODE : 017
FUNCTION: DB2 UDB, base sys utilities, sqleGetAgentFromPool, probe:97
MESSAGE : Error getting an agent. Return code =
DATA #1 : Hexdump, 4 bytes
0x0FFFFFFFFFFF81C0 : FFFF FB39 ...9
2012-12-28-02.41.10.915385+480 I30977769A283 LEVEL: Error
PID : 468080 TID : 1 PROC : db2ipccm 17
INSTANCE: instance NODE : 017
FUNCTION: DB2 UDB, base sys utilities, sqleGetAgent, probe:70
MESSAGE : Agent not allocated, sqlcode = -1223
2012-12-28-02.41.11.856607+480 I30978053A605 LEVEL: Severe
PID : 4243910 TID : 1 PROC : db2agent 17
INSTANCE: instance NODE : 017
APPHDL : 17-295 APPID: *N17.instance.121228132220
FUNCTION: DB2 UDB, SQO Memory Management, sqlodshr, probe:1020
MESSAGE : Set address is NULL.
DATA #1 : Pointer, 8 bytes
0x00000001115a11c8
DATA #2 : Memory set handle, PD_TYPE_OSS_MEM_SET_HDL, 32 bytes
0x00000001115A11C8 : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x00000001115A11D8 : 0000 0000 0000 0000 0000 0000 0000 0000 ................
2012-12-28-02.41.11.931287+480 I30978659A389 LEVEL: Error
PID : 468080 TID : 1 PROC : db2ipccm 17
INSTANCE: instance NODE : 017
FUNCTION: DB2 UDB, base sys utilities, sqleGetAgentFromPool, probe:97
MESSAGE : Error getting an agent. Return code =
DATA #1 : Hexdump, 4 bytes
0x0FFFFFFFFFFF81C0 : FFFF FB39 ...9
2012-12-28-02.41.12.244894+480 I30979049A283 LEVEL: Error
PID : 468080 TID : 1 PROC : db2ipccm 17
INSTANCE: instance NODE : 017
FUNCTION: DB2 UDB, base sys utilities, sqleGetAgent, probe:70
MESSAGE : Agent not allocated, sqlcode = -1223
********************补充 db2pd -agents抓的内容*********************
Database Partition 17 -- Active -- Up 112 days 07:11:34 -- Date 2012-12-28 02:41:00
Agents:
Current agents: 575
Idle agents: 1
Active coord agents: 571
Active agents total: 574
Pooled coord agents: 0
Pooled agents total: 0
Address AppHandl [nod-index] AgentPid Priority Type State ClientPid Userid ClientNm Rowsread Rowswrtn LkTmOt DBName
0x07800000001251E0 0 [000-00000] 697768 0 Panic Idle n/a n/a n/a 0 0 0 n/a
0x0780000000F35500 1115052 [017-00940] 0 0 Coord Inst-Active n/a n/a n/a 0 0 0 n/a
0x0780000000F34AC0 1115424 [017-01312] 2359350 0 Coord Inst-Active 3765630 user ****** 0 0 0 n/a
0x0780000000F34080 1115780 [017-01668] 1397758 0 Coord Inst-Active 4400476 user ****** 0 0 0
0x0780000000F233C0 1114312 [017-00200] 2720664 0 Coord Inst-Active 1499504 user ****** 0 0 0 n/a
0x0780000000F22980 1115159 [017-01047] 2466678 0 Coord Inst-Active 3765630 user ****** 0 0 0 n/a
0x0780000000F21F40 1114469 [017-00357] 2725206 0 Coord Inst-Active 4400476 user ****** 0 0 0 n/a
0x0780000000F21500 1115221 [017-01109] 251208 0 Coord Inst-Active 1499504 user ****** 0 0 0 n/a
0x0780000000F20AC0 1114820 [017-00708] 2512372 0 Coord Inst-Active 2847088 user ****** 0 0 0 n/a
这个node上，Current agents: 575个，而Active coord agents: 571，这样subagents是3个，1个Panic。
有点奇怪，按理说这个节点上一个Coord，最少会有对应一个subagents,而现在几乎全部是Coord 类型的agent。
db2 ? SQL1223N的解释已经了解。
[size=+0]通过db2pd -agent 抓的数据如下，在短短2分钟内，agents数目的使用波动如此之大，好困惑。
[size=+0]Database Partition 17 -- Active -- Up 112 days 07:11:34 -- Date 2012-12-28 02:41:00
[size=+0]Agents:
Current agents: 142
Idle agents: 1
Active coord agents: 137
Active agents total: 141
Pooled coord agents: 0
Pooled agents total: 0
[size=+0]
Database Partition 17 -- Active -- Up 112 days 07:12:15 -- Date 2012-12-28 02:41:41
Agents:
Current agents: 575
Idle agents: 1
Active coord agents: 571
Active agents total: 574
Pooled coord agents: 0
Pooled agents total: 0
Database Partition 17 -- Active -- Up 112 days 07:12:56 -- Date 2012-12-28 02:42:22
Agents:
Current agents: 489
Idle agents: 1
Active coord agents: 485
Active agents total: 488
Pooled coord agents: 0
Pooled agents total: 0
Database Partition 17 -- Active -- Up 112 days 07:14:17 -- Date 2012-12-28 02:43:43
Agents:
Current agents: 27
Idle agents: 1
Active coord agents: 23
Active agents total: 26
Pooled coord agents: 0
Pooled agents total: 0
db2diag.log里面
2012-12-28-02.38.40.429663+480 I30975463A531 LEVEL: Error
PID : 3777250 TID : 1 PROC : db2agent (DBNAME) 3
INSTANCE: instance NODE : 003 DB : DBNAME
APPHDL : 3-1563 APPID: *N1.instance.121227183818
FUNCTION: DB2 UDB, database utilities, DIAG_ERROR, probe:0
DATA #1 : String, 175 bytes
LOADID: 3777250.2012-12-28-02.38.34.708708.3 (42;7288)
Coord: error encountered during catalog agent processing. , -27902, 0, Detected in file:sqlusMPPCoordinator.C, Line:1324
2012-12-28-02.38.40.435066+480 I30975995A510 LEVEL: Error
PID : 3777250 TID : 1 PROC : db2agent (DBNAME) 3
INSTANCE: instance NODE : 003 DB : DBNAME
APPHDL : 3-1563 APPID: *N1.instance.121227183818
FUNCTION: DB2 UDB, database utilities, DIAG_ERROR, probe:0
DATA #1 : String, 154 bytes
LOADID: 3777250.2012-12-28-02.38.34.708708.3 (42;7288)
Error during processing of catalog sub-agent. , -27902, 0, Detected in file:sqluCatalog.C, Line:691
2012-12-28-02.38.40.451328+480 I30976506A472 LEVEL: Error
PID : 3777250 TID : 1 PROC : db2agent (DBNAME) 3
INSTANCE: instance NODE : 003 DB : DBNAME
APPHDL : 3-1563 APPID: *N1.instance.121227183818
FUNCTION: DB2 UDB, database utilities, DIAG_ERROR, probe:0
DATA #1 : String, 116 bytes
LOADID: 3777250.2012-12-28-02.38.34.708708.3 (42;7288)
sqluv_rollback , 0, 2, Detected in file:sqluvutl.C, Line:1172
2012-12-28-02.38.40.453022+480 I30976979A399 LEVEL: Severe
PID : 3777250 TID : 1 PROC : db2agent (DBNAME) 3
INSTANCE: instance NODE : 003 DB : DBNAME
APPHDL : 3-1563 APPID: *N1.instance.121227183818
FUNCTION: DB2 UDB, database utilities, sqlu_register_table_load, probe:10
MESSAGE : Load Error: Failed getting Load catalog info.
2012-12-28-02.41.10.510550+480 I30977379A389 LEVEL: Error
PID : 468080 TID : 1 PROC : db2ipccm 17
INSTANCE: instance NODE : 017
FUNCTION: DB2 UDB, base sys utilities, sqleGetAgentFromPool, probe:97
MESSAGE : Error getting an agent. Return code =
DATA #1 : Hexdump, 4 bytes
0x0FFFFFFFFFFF81C0 : FFFF FB39 ...9
2012-12-28-02.41.10.915385+480 I30977769A283 LEVEL: Error
PID : 468080 TID : 1 PROC : db2ipccm 17
INSTANCE: instance NODE : 017
FUNCTION: DB2 UDB, base sys utilities, sqleGetAgent, probe:70
MESSAGE : Agent not allocated, sqlcode = -1223
2012-12-28-02.41.11.856607+480 I30978053A605 LEVEL: Severe
PID : 4243910 TID : 1 PROC : db2agent 17
INSTANCE: instance NODE : 017
APPHDL : 17-295 APPID: *N17.instance.121228132220
FUNCTION: DB2 UDB, SQO Memory Management, sqlodshr, probe:1020
MESSAGE : Set address is NULL.
DATA #1 : Pointer, 8 bytes
0x00000001115a11c8
DATA #2 : Memory set handle, PD_TYPE_OSS_MEM_SET_HDL, 32 bytes
0x00000001115A11C8 : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x00000001115A11D8 : 0000 0000 0000 0000 0000 0000 0000 0000 ................
2012-12-28-02.41.11.931287+480 I30978659A389 LEVEL: Error
PID : 468080 TID : 1 PROC : db2ipccm 17
INSTANCE: instance NODE : 017
FUNCTION: DB2 UDB, base sys utilities, sqleGetAgentFromPool, probe:97
MESSAGE : Error getting an agent. Return code =
DATA #1 : Hexdump, 4 bytes
0x0FFFFFFFFFFF81C0 : FFFF FB39 ...9
2012-12-28-02.41.12.244894+480 I30979049A283 LEVEL: Error
PID : 468080 TID : 1 PROC : db2ipccm 17
INSTANCE: instance NODE : 017
FUNCTION: DB2 UDB, base sys utilities, sqleGetAgent, probe:70
MESSAGE : Agent not allocated, sqlcode = -1223
********************补充 db2pd -agents抓的内容*********************
Database Partition 17 -- Active -- Up 112 days 07:11:34 -- Date 2012-12-28 02:41:00
Agents:
Current agents: 575
Idle agents: 1
Active coord agents: 571
Active agents total: 574
Pooled coord agents: 0
Pooled agents total: 0
Address AppHandl [nod-index] AgentPid Priority Type State ClientPid Userid ClientNm Rowsread Rowswrtn LkTmOt DBName
0x07800000001251E0 0 [000-00000] 697768 0 Panic Idle n/a n/a n/a 0 0 0 n/a
0x0780000000F35500 1115052 [017-00940] 0 0 Coord Inst-Active n/a n/a n/a 0 0 0 n/a
0x0780000000F34AC0 1115424 [017-01312] 2359350 0 Coord Inst-Active 3765630 user ****** 0 0 0 n/a
0x0780000000F34080 1115780 [017-01668] 1397758 0 Coord Inst-Active 4400476 user ****** 0 0 0
0x0780000000F233C0 1114312 [017-00200] 2720664 0 Coord Inst-Active 1499504 user ****** 0 0 0 n/a
0x0780000000F22980 1115159 [017-01047] 2466678 0 Coord Inst-Active 3765630 user ****** 0 0 0 n/a
0x0780000000F21F40 1114469 [017-00357] 2725206 0 Coord Inst-Active 4400476 user ****** 0 0 0 n/a
0x0780000000F21500 1115221 [017-01109] 251208 0 Coord Inst-Active 1499504 user ****** 0 0 0 n/a
0x0780000000F20AC0 1114820 [017-00708] 2512372 0 Coord Inst-Active 2847088 user ****** 0 0 0 n/a
这个node上，Current agents: 575个，而Active coord agents: 571，这样subagents是3个，1个Panic。
有点奇怪，按理说这个节点上一个Coord，最少会有对应一个subagents,而现在几乎全部是Coord 类型的agent。
18同行回答
回复 1# jeffbeck
1.实例级别：
###db2 get snapshot for dbm |grep -i agent
High water mark for agents registered = 626
High water mark for agents waiting for a token = 0
Agents registered = 154
Agents waiting for a token = 0
Idle agents = 1
Agents assigned from pool = 7
Agents created from empty pool = 47258876
Agents stolen from another application = 0
High water mark for coordinating agents = 625
Max agents overflow = 97
Gateway connection pool agents stolen = 0
2.db 级别：
#######db2 get snapshot for db on zmccdw|grep -i agent
Agents associated with applications = 114
Maximum agents associated with applications= 325 --这个值是小于600.
Maximum coordinating agents = 211
Agents currently waiting on locks = 0
3.通过上面的实例快照和，db快照可以看到...db层的agents最大是325，而dbm层最大的agnets已经超过600了。
怀疑是load带来这些额外的agent,因为db2diag.log里面只有load相关日志.
验证如下:db2 list applications 是db级别，并不显示load这些applicarions
db2pd -utilities是dbm级别的，会显示load，，我想load本身会带来相应的agnets。收起
1.实例级别：
###db2 get snapshot for dbm |grep -i agent
High water mark for agents registered = 626
High water mark for agents waiting for a token = 0
Agents registered = 154
Agents waiting for a token = 0
Idle agents = 1
Agents assigned from pool = 7
Agents created from empty pool = 47258876
Agents stolen from another application = 0
High water mark for coordinating agents = 625
Max agents overflow = 97
Gateway connection pool agents stolen = 0
2.db 级别：
#######db2 get snapshot for db on zmccdw|grep -i agent
Agents associated with applications = 114
Maximum agents associated with applications= 325 --这个值是小于600.
Maximum coordinating agents = 211
Agents currently waiting on locks = 0
3.通过上面的实例快照和，db快照可以看到...db层的agents最大是325，而dbm层最大的agnets已经超过600了。
怀疑是load带来这些额外的agent,因为db2diag.log里面只有load相关日志.
验证如下:db2 list applications 是db级别，并不显示load这些applicarions
db2pd -utilities是dbm级别的，会显示load，，我想load本身会带来相应的agnets。收起
浏览1285
向大家学习收起
浏览1557
关注收起
浏览1300
回复 13# 田强
数据库版本是：DB2 8.1 ,fixpack 16, DPF 32 nodes, db2diag level是2
我截取了部分db2diag.log的信息，
2012-12-28-02.38.40.429663+480 I30975463A531 LEVEL: Error
PID : 3777250 TID : 1 PROC : db2agent (DBNAME) 3
INSTANCE: instance NODE : 003 DB : DBNAME
APPHDL : 3-1563 APPID: *N1.instance.121227183818
FUNCTION: DB2 UDB, database utilities, DIAG_ERROR, probe:0
DATA #1 : String, 175 bytes
LOADID: 3777250.2012-12-28-02.38.34.708708.3 (42;7288)
Coord: error encountered during catalog agent processing. , -27902, 0, Detected in file:sqlusMPPCoordinator.C, Line:1324
这个错误出现了很多次，前面出错后并未立即出现SQL1223N的错误收起
数据库版本是：DB2 8.1 ,fixpack 16, DPF 32 nodes, db2diag level是2
我截取了部分db2diag.log的信息，
2012-12-28-02.38.40.429663+480 I30975463A531 LEVEL: Error
PID : 3777250 TID : 1 PROC : db2agent (DBNAME) 3
INSTANCE: instance NODE : 003 DB : DBNAME
APPHDL : 3-1563 APPID: *N1.instance.121227183818
FUNCTION: DB2 UDB, database utilities, DIAG_ERROR, probe:0
DATA #1 : String, 175 bytes
LOADID: 3777250.2012-12-28-02.38.34.708708.3 (42;7288)
Coord: error encountered during catalog agent processing. , -27902, 0, Detected in file:sqlusMPPCoordinator.C, Line:1324
这个错误出现了很多次，前面出错后并未立即出现SQL1223N的错误收起
浏览1305
回复 4# wolaos123
问题前dbm snapshot：
High water mark for coordinating agents = 625
Max agents overflow = 244
问题后dbm snapshot：
High water mark for coordinating agents = 625
Max agents overflow = 286
发现有42个 agents overflow
[size=+0]
问题前db snapshot：
Maximum agents associated with applications= 285
Maximum coordinating agents = 192
问题后db snapshot：
Maximum agents associated with applications= 285
Maximum coordinating agents = 192
数据库层面没有agent变化，切也小于600
[size=+0]
实例参数： MAXAGENTS = MAX_COORDAGENTS =600收起
问题前dbm snapshot：
High water mark for coordinating agents = 625
Max agents overflow = 244
问题后dbm snapshot：
High water mark for coordinating agents = 625
Max agents overflow = 286
发现有42个 agents overflow
[size=+0]
问题前db snapshot：
Maximum agents associated with applications= 285
Maximum coordinating agents = 192
问题后db snapshot：
Maximum agents associated with applications= 285
Maximum coordinating agents = 192
数据库层面没有agent变化，切也小于600
[size=+0]
实例参数： MAXAGENTS = MAX_COORDAGENTS =600收起
浏览1402