DB2 on windows has an event handle leak with parallelized plans,
if INTRA_PARALLEL YES is set.
The problem can be seen when parallelized plans are executed,
i.e. many subagents are spawned. On windows the event handles
can be monitored with the task manager or the "handle" utility
from Microsoft sysinternals. If you use the handle tool against
db2sysc you will see a growing number of "Event Handles" over
time:
G:\cds>handle -s -p 6584
Nthandle v4.1 - Handle viewer
Copyright (C) 1997-2016 Mark Russinovich
Sysinternals - www.sysinternals.com
Handle type summary:
ALPC Port : 5
Desktop : 1
Directory : 3
EtwRegistration : 56
Event : 1025591 <<<<
File : 368
IoCompletion : 2
IRTimer : 2
Key : 44
Mutant : 260
Process : 3
Section : 12
Semaphore : 215
Thread : 455
Token : 2
TpWorkerFactory : 1
WaitCompletionPacket: 3
WindowStation : 2
Total handles: 1027025
This might go on undetected for a while till Microsoft Kernel
limit of event handles is reached. At that point you will most
likely see OSERR 1450 logged in db2diag.log.
Once that happens DB2 will show certain misbehavior, like hangs,
SQL1034C errors and/or entries in db2diag.log like the
following:
- SQLO_NORES during read operations
2017-01-15-16.13.51.394000+060 I10890739F686 LEVEL:
Severe
PID : 8896 TID : 5896 PROC :
db2syscs.exe
INSTANCE: db2inst1 NODE : 000 DB :
SAMPLE
APPHDL : 0-24450 APPID: ::1.53330.170115150048
AUTHID : SAPSR3 HOSTNAME: localhost
EDUID : 5896 EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, buffer pool services, sqlbReadPage,
probe:1140
MESSAGE : ZRC=0x870F00F2=-2029059854=SQLO_NORES
"no resources to create process or thread"
DATA #1 : <preformatted>
Failed to read page from disk on attempt number 1.
Retrying operation. Only subsequent failures will be logged.
- 1450 error logged by sqloInitIPCWaitPost()
2017-01-15-16.13.54.113000+060 E10946809F621 LEVEL: Error
(OS)
PID : 8896 TID : 9260 PROC :
db2syscs.exe
INSTANCE: db2inst1 NODE : 000 DB :
SAMPLE
APPHDL : 0-24668 APPID: ::1.53720.170115151322
AUTHID : SAPSR3 HOSTNAME: localhost
EDUID : 9260 EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, oper system services, sqloInitIPCWaitPost,
probe:20
MESSAGE : ZRC=0x830005AA=-2097150550
CALLED : OS, -, CreateEvent
OSERR : 1450 "Insufficient system resources exist to complete
the requested service."
- 1450 error logged by db2agentX threads (parallel sort):
2017-01-15-16.13.52.878000+060 I10926483F924 LEVEL:
Severe
PID : 8896 TID : 9468 PROC :
db2syscs.exe
INSTANCE: db2inst1 NODE : 000 DB :
SAMPLE
APPHDL : 0-24652 APPID: ::1.53710.170115151301
AUTHID : SAPSR3 HOSTNAME: localhost
EDUID : 9468 EDUNAME: db2agnts (SAMPLE) 0
FUNCTION: DB2 UDB, relation data serv, sqlrr_dump_ffdc,
probe:250
MESSAGE : ZRC=0x830005AA=-2097150550
DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -901 sqlerrml: 4
sqlerrmc: 1450
sqlerrp : SQLRI14A
sqlerrd : (1) 0x830005AA (2) 0x000005AA (3) 0x00000000
(4) 0x00000000 (5) 0xFFFFFD09 (6)
0x00000000 |
