IBM Support

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

Subscribe

You can track all active APARs for this component.

 

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 number

    IT22118

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    B10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2017-08-23

  • Closed date

    2017-10-11

  • Last modified date

    2017-10-11

  • 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

[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.1","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
29 June 2020