IT22118: WINDOWS EVENT HANDLE LEAK WITH PARALLELIZED QUERY PLANS (INTRA_PARALLEL YES) - MAY CAUSE OSERR 1450

Db2 Version 11.1 Mod2 Fix Pack2 iFix001 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod2 Fix Pack2 iFix002 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 3 Fix Pack 3 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod3 Fix Pack3 iFix001 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod3 Fix Pack3 iFix002 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod4 Fix Pack4 iFix001 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• 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
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * ALL                                                          *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to Db2 11.1 Mod 2 Fix Pack 2 iFix001 or higher       *
  ****************************************************************
  

Problem conclusion

• First fixed in Db2 11.1 Mod 2 Fix Pack 2 iFix001
  

Temporary fix

• See local fix.
  

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  IT22168

Fix information

• Fixed component name

  DB2 FOR LUW

• Fixed component ID

  DB2FORLUW

Applicable component levels