IBM Support

IC79119: DB2 MIGHT ABEND WHILE MATCHING OPTIMIZER MQT FOR QUERY WITH MORE THAN 30 JOINS

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • Under rare scenarios, DB2 might abend for a query which
    satisfies the following conditions:
    
    - The query has more than 30 inner joins
    - There are one or more optimizer mqts defined on the tables.
    
    <Stack Trace>
    -----FUNC-ADDR---- ------FUNCTION + OFFSET------
    sqlnq_mch::find_qun_mappings
    sqlnr_subsume_derived_match
    sqlnr_subsume_match
    sqlnr_matchmaker
    sqlnr_compute_matchinfo
    sqlnr_optprep
    sqlnr_optprep_action
    sqlnr_comp
    sqlnr_seq
    sqlnr_rcc
    sqlnr_exe
    sqlnn_cmpl
    </StackTrace>
    
    The following messages will be written to the db2diag.log
    
    20xx-xx-xx-xx.xx.xx.xxxxxx-xxx xxxxxxxxxxx         LEVEL: Event
    PID     : xxxxx                TID  : xxxxxxxxxxxx PROC :
    db2sysc 0
    INSTANCE: xxxxxxxx             NODE : 000          DB   : xxxxx
    APPHDL  : 0-13                 APPID: *LOCAL.DB2.xxxxxxxxxxxx
    AUTHID  : xxxxx
    EDUID   : 66                   EDUNAME: db2stmm (xxxxx) 0
    FUNCTION: DB2 UDB, config/install, sqlfLogUpdateCfgParam,
    probe:20
    CHANGE  : STMM CFG DB xxxxx: "Pckcachesz" From: "12161"
    <automatic>  To: "12321" <automatic>
    
    20xx-xx-xx-xx.xx.xx.xxxxxx-xxx xxxxxxxxxxx         LEVEL: Error
    PID     : xxxxx                TID  : xxxxxxxxxxxx PROC :
    db2sysc 0
    INSTANCE: xxxxxxxx             NODE : 000          DB   : xxxxx
    APPHDL  : 0-6242               APPID:
    xx.xxx.xxx.xx.xxxx.xxxxxxxxxxxx
    AUTHID  : xxxxxxxx
    EDUID   : 233                  EDUNAME: db2agent (xxxxx) 0
    FUNCTION: DB2 UDB, access plan manager, sqlra_intd_call,
    probe:200
    MESSAGE : Out sqlz_value
    DATA #1 : Hexdump, 24 bytes
    0x0000002F56FF8DA8 : 1301 4000 0108 0000 A006 0000 0000 0000
    ..@.............
    0x0000002F56FF8DB8 : 4000 0000 0000 0000
    @.......
    
    20xx-xx-xx-xx.xx.xx.xxxxxx-xxx xxxxxxxxxxx         LEVEL: Error
    PID     : xxxxx                TID  : xxxxxxxxxxxx PROC :
    db2sysc 0
    INSTANCE: xxxxxxxx             NODE : 000          DB   : xxxxx
    APPHDL  : 0-6242               APPID:
    xx.xxx.xxx.xx.xxxx.xxxxxxxxxxxx
    AUTHID  : xxxxxxxx
    EDUID   : 233                  EDUNAME: db2agent (xxxxx) 0
    FUNCTION: DB2 UDB, access plan manager, sqlra_intd_call,
    probe:200
    MESSAGE : Out sqlz_value
    DATA #1 : Hexdump, 24 bytes
    0x0000002F56FF8CA8 : 1301 4000 0108 0000 A005 0000 0000 0000
    ..@.............
    0x0000002F56FF8CB8 : 4000 0000 0000 0000
    @.......
    
    20xx-xx-xx-xx.xx.xx.xxxxxx-xxx xxxxxxxxxxx         LEVEL: Error
    PID     : xxxxx                TID  : xxxxxxxxxxxx PROC :
    db2sysc 0
    INSTANCE: xxxxxxxx             NODE : 000          DB   : xxxxx
    APPHDL  : 0-6242               APPID:
    xx.xxx.xxx.xx.xxxx.xxxxxxxxxxxx
    AUTHID  : xxxxxxxx
    EDUID   : 233                  EDUNAME: db2agent (xxxxx) 0
    FUNCTION: DB2 UDB, access plan manager, sqlra_intd_call,
    probe:200
    MESSAGE : Out sqlz_value
    DATA #1 : Hexdump, 24 bytes
    0x0000002F56FF8BA8 : 1301 4000 0108 B804 A004 0000 0000 0000
    ..@.............
    0x0000002F56FF8BB8 : 4000 0000 0000 0000
    @.......
    
    20xx-xx-xx-xx.xx.xx.xxxxxx-xxx xxxxxxxxxxx         LEVEL: Error
    PID     : xxxxx                TID  : xxxxxxxxxxxx PROC :
    db2sysc 0
    INSTANCE: xxxxxxxx             NODE : 000          DB   : xxxxx
    APPHDL  : 0-6426               APPID:
    xxx.xx.xxx.xx.xxxxx.xxxxxxxxxxx
    AUTHID  : xxxxxx
    EDUID   : 140                  EDUNAME: db2agent (xxxxx) 0
    FUNCTION: DB2 UDB, base sys utilities, sqleagnt_sigsegvh,
    probe:1
    MESSAGE : Error in agent servicing application with coor_node:
    DATA #1 : Hexdump, 2 bytes
    0x000000307580F592 : 0000
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * all                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See problem description                                      *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Please upgrade to DB2 950 fix pack 9                         *
    ****************************************************************
    

Problem conclusion

  • Problem has been first fixed in DB2 950 fix pack 9
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC79119

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    950

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2011-10-07

  • Closed date

    2012-03-22

  • Last modified date

    2012-03-22

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

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

    IC79121

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R950 PSN

       UP

  • R950 PSY

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.5

Reference #: IC79119

Modified date: 22 March 2012