银行
应用程序在连接时候会报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同行回答
可能是数据库编目有问题，重新编目下试试～
Verify that the output from the LIST DATABASE DIRECTORY command (specifically the "directory entry type" field) is correct. If it is not, correct the information by performing UNCATALOG DATABASE and CATALOG DATABASE commands.
A local database should appear similar to the following in the LIST DATABASE DIRECTORY output:
Database 1 entry:
Database alias = SAMPLE
Database name = SAMPLE
Local database directory = /home/db2inst1/db2inst1
Database release level = a.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =收起
Verify that the output from the LIST DATABASE DIRECTORY command (specifically the "directory entry type" field) is correct. If it is not, correct the information by performing UNCATALOG DATABASE and CATALOG DATABASE commands.
A local database should appear similar to the following in the LIST DATABASE DIRECTORY output:
Database 1 entry:
Database alias = SAMPLE
Database name = SAMPLE
Local database directory = /home/db2inst1/db2inst1
Database release level = a.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =收起
浏览628
猜测LOAD出错导致大量agent占用，导致其他EDU无法得到agent。
这个是第一个错嘛？之前还有没有？db2版本号是啥？
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收起
这个是第一个错嘛？之前还有没有？db2版本号是啥？
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收起
浏览594