IBM Support

IT35385: QUERY COULD FAIL WITH -901 WHEN MQT IS USED

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • The query with the following charateristic could fail;
    1) MQT is used for optimization
    2) MQT is joined with another table
    3) The original query has COUNT and GROUP BY
    4) The MQT has GROUP BY and COUNT_BIG function, instead of COUNT
    
    This happens while matching MQTs in query rewrite phase. Because
    the original query has
    COUNT and the MQT has COUNT_BIG, mqt matching specially handles
    the COUNT predicate.
    
    While handling such type of predicate matching, query rewrite
    could generate a wrong optimized query by putting columns in the
    wrong place.
    
    Workaround: Disable MQT matching.
    
    2020-09-25-14.48.03.990585+120 I31056122E646 LEVEL: Error
    PID : 17127 TID : 47314165032704 PROC : db2sysc 1
    INSTANCE: db2inst1 NODE : 001 DB : MYDB1
    APPHDL : 1-9674 APPID: X.X.X.X.58896.200925124641
    AUTHID : USER1 HOSTNAME:
    myhost1
    EDUID : 1140 EDUNAME: db2agent (MYDB2) 1
    FUNCTION: DB2 UDB, base sys utilities, sqleagnt_sigsegvh,
    probe:1
    MESSAGE : Error in agent servicing application with coor_node:
    DATA #1 : Hexdump, 2 bytes
    0x00002B082F80EF52 : 0100 ..
    
    The stack:
    ossDumpStackTraceInternal(unsigned long, OSSTrapFile&, int,
    siginfo*, void*, unsigned long, unsigned long) + 0x0356
    ossDumpStackTraceV98 + 0x002b
    OSSTrapFile::dumpEx(unsigned long, int, siginfo*, void*,
    unsigned long) + 0x00d7
    sqlo_trce + 0x03c7
    sqloEDUCodeTrapHandler + 0x03d1
    address: 0x00002AAAAACDE630 ; dladdress: 0x00002AAAAACCF000 ;
    offset in lib: 0x000000000000F630 ;
    sqlngMapAggFunc(sqlng_blk*, sqlri_opparm*, sqlnq_pid*) + 0x0024
    sqlng_build_S_X_op(sqlng_blk*, sqlri_opparm**, sqlnq_pid*) +
    0x36ac
    sqlng_process_BF_node(sqlng_blk*, sqlnq_pid*) + 0x016b
    sqlng_walk_BF_chain(sqlng_blk*) + 0x08c7
    sqlng_build_thread(sqlng_blk*, sqlng_thd_ctrl*, sqlnq_qun*) +
    0x11f8
    sqlng_build_TA_op(sqlng_blk*, sqlng_scan_blk*, sqlno_qtb*) +
    0x2123
    sqlng_process_f_iscan(sqlng_blk*, sqlno_plan_operator*) + 0x0faf
    sqlng_process_fetch_op(sqlng_blk*, sqlno_plan_operator*) +
    0x09a3
    sqlngProcessLolepop(sqlng_blk*, sqlno_plan_operator*) + 0x00bd
    sqlng_process_mate_op(sqlng_blk*, sqlno_plan_operator*) + 0x02d1
    sqlngProcessLolepop(sqlng_blk*, sqlno_plan_operator*) + 0x00bd
    sqlng_process_pipe_op(sqlng_blk*, sqlno_plan_operator*) + 0x02e2
    sqlngProcessLolepop(sqlng_blk*, sqlno_plan_operator*) + 0x00bd
    

Local fix

  • Disable MQT matching.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * DB2 Server                                                   *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * The query with the following charateristic could fail;       *
    * 1) MQT is used for optimization                              *
    * 2) MQT is joined with another table                          *
    * 3) The original query has COUNT and GROUP BY                 *
    * 4) The MQT has GROUP BY and COUNT_BIG function, instead of   *
    * COUNT                                                        *
    *                                                              *
    * This happens while matching MQTs in query rewrite phase.     *
    * Because the original query has COUNT and the MQT has         *
    * COUNT_BIG, mqt matching specially handles the COUNT          *
    * predicate.                                                   *
    *                                                              *
    * While handling such type of predicate matching, query        *
    * rewrite could generate a wrong optimized query by putting    *
    * columns in the wrong place.                                  *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to 11.1 Mod 4 Fix pack 7                             *
    ****************************************************************
    

Problem conclusion

  • Fixed in 11.1 Mod 4 Fix pack 7
    

Temporary fix

  • Disable MQT matching
    

Comments

APAR Information

  • APAR number

    IT35385

  • 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

    2020-12-30

  • Closed date

    2021-11-22

  • Last modified date

    2022-03-29

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

  • 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

  • RB10 PSN

       UP

  • RB10 PSY

       UP

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"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:
03 May 2022