IBM Support

IT18322: DB2 crash or long Compilation time that might error out with SQL101N

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • DB2 engine crashes or a query takes a long time to compile or
    may error out with
    SQL0101N due to running out of statement heap during compile
    state.
    
    The problem could occur for queries containing common sub
    expressions (CSE) that have predicates applied against these
    CSEs. This problem can also be observed for queries containing 2
    and more correlated sub-queries that share a common correlated
    source.
    
    
    If you hit this problem, the stack differs depending on the
    symptoms you observe.
    
    If you experience long compilation time or observe SQL101N
    error, the call stack at the thread compiling the query is:
    
      0x00007F04A1EB2CE6
    _Z25ossDumpStackTraceInternalmR11OSSTrapFileiP7siginfoPvmm +
    0x0356
      0x00007F04A1EB292B ossDumpStackTraceV98 + 0x002b
      0x00007F04A1EAEAD7 _ZN11OSSTrapFile6dumpExEmiP7siginfoPvm +
    0x00d7
      0x00007F04A940F9F7 sqlo_trce + 0x03c7
      0x00007F04A956B17B sqloDumpDiagInfoHandler + 0x020b
      0x00007F04B06B9850 address: 0x00007F04B06B9850 ;
    dladdress:0x00007F04B06AA000 ; offset in lib: 0x000000000000F850
    ;
    
    0x00007F04A953A448_ZN13SQLO_MEM_POOL27MemTreePreallocateAndInser
    tEP17SqloChunkSubgroupmmPj + 0x0288
      0x00007F04A951BED6 sqloGetMemoryBlockExtended + 0x0ff6
      0x00007F04A8BBC89D _ZN12sqlnq_memMgr17newEntry_newBlockERi +
    0x010d
      0x00007F04A89237DC _Z14sqlnq_stk_pushPhPP9sqlnq_stkP3loc +
    0x011c
      0x00007F04A8C17018
    _ZN9sqlnq_prd21next_prdpder_preorderERPS_RP9sqlnq_stkS0_P3loc +
    0x00f8
      0x00007F04A874AA63 address: 0x00007F04A874AA63 ; dladdress:
    0x00007F04A23FB000 ; offset in lib: 0x000000000634FA63 ;
      0x00007F04A87488E3 _Z18sqlno_ntup_ff_scanP13sqlno_globals +
    0x1993
      0x00007F04A86A329C _Z9sqlno_exeP9sqlnq_qur + 0x0d8c
      0x00007F04A83A8A08
    _Z10sqlnn_cmplP8sqeAgentP11sqlrrstrings17sqlnn_compileModesP14sq
    lrr_cmpl_enviiPP9sqlnq_qur + 0x4d78
      0x00007F04A83A3C81
    _Z10sqlnn_cmplP8sqeAgentP11sqlrrstrings17sqlnn_compileModesP14sq
    lrr_cmpl_env + 0x0021
      0x00007F04A9DF91DB
    _Z17sqlra_compile_varP8sqlrr_cbP14sqlra_cmpl_envPhitiiiiiP14SQLP
    _LOCK_INFOP16sqlra_cached_varPiS5_Pb + 0x10eb
      ...
    
    If you observe DB2 crash, the crashed stack could contain these
    frames:
    
    0x00007FFFEBBD6D65
    _Z25ossDumpStackTraceInternalmR11OSSTrapFileiP7siginfoPvmm +
    0x0385
            (/var/ibmdb2/nypit227/sqllib/lib64/libdb2osse.so.1)
    0x00007FFFEBBD696C ossDumpStackTraceV98 + 0x002c
            (/var/ibmdb2/nypit227/sqllib/lib64/libdb2osse.so.1)
    0x00007FFFEBBD2A0D _ZN11OSSTrapFile6dumpExEmiP7siginfoPvm +
    0x00fd
            (/var/ibmdb2/nypit227/sqllib/lib64/libdb2osse.so.1)
    0x00007FFFF223F4B4 sqlo_trce + 0x0404
            (/var/ibmdb2/nypit227/sqllib/lib64/libdb2e.so.1)
    0x00007FFFF22C8E5C sqloEDUCodeTrapHandler + 0x02ac
            (/var/ibmdb2/nypit227/sqllib/lib64/libdb2e.so.1)
    0x00007FFFF79CD7E0 address: 0x00007FFFF79CD7E0 ; dladdress:
    0x00007FFFF79BE000 ; offset in lib: 0x000000000000F7E0 ;
            (/lib64/libpthread.so.0)
    0x00007FFFF1E2C0B3
    _ZN9sqlnq_prd21replace_in_prdpderparEPS_P13sqlnq_memPool20sqlnq_
    pder_rule_type + 0x00f3
            (/var/ibmdb2/nypit227/sqllib/lib64/libdb2e.so.1)
    0x00007FFFEFD8B27D
    _ZN9sqlnq_prd16reassign_prdpderEP13sqlnq_memPool + 0x0c8d
            (/var/ibmdb2/nypit227/sqllib/lib64/libdb2e.so.1)
    0x00007FFFEFD8B976 _ZN9sqlnq_prd4dropEP13sqlnq_memPoolPPS_ +
    0x0156
            (/var/ibmdb2/nypit227/sqllib/lib64/libdb2e.so.1)
    ......
    

Local fix

  • db2set -im DB2COMPOPT=NO_DERPRD_FF
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to Db2 11.1 Mod 4 Fixpack 4 or higher                *
    ****************************************************************
    

Problem conclusion

  • First fixed in Db2 11.1 Mod 4 Fixpack 4
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT18322

  • 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

    2016-12-08

  • Closed date

    2018-11-27

  • Last modified date

    2018-11-27

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

    IT18067

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

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