IBM Support

IC88531: REROUTING A QUERY TO MQT MAY RETURN INCORRECT RESULT

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • DB2 compiler can automatically consider redirecting a portion of
    query's to MQT. When all the following conditions are satisfied,
    the rewritten query may return incorrect result.
    
    Notation:
    TS1, TS2: a single basetable/nickname or multiple
    basetables/nicknames inner joined together.
    colX, colY, colZ : a set of base table attributes or
    expressions.
    <columns> : arbitrary group of columns.
    <predicates> : arbitrary group of predicates.
    <aggregates> : arbitrary group of aggregations.
    
    Conditions:
    1. MQT query definition is either
    "SELECT TS1.colX, <aggregates> FROM TS1 where <predicates> GROUP
    BY TS1.colX".
    
    2. Query statement has a SELECT DISTINCT block whose output
    includes nothing from TS1, e.g.
    "SELECT DISTINCT TS2.colZ FROM TS1, TS2 where <predicates>" ,
    
    or Query references TS1 >=2 times and Query has a SELECT
    DISTINCT block whose output includes some but not all MQT
    Group-By columns, e.g.
    "SELECT <columns> FROM (SELECT DISTINCT TS1.colY FROM TS1), TS2
    where <predicates>".
    
    Here, colX is a strict superset of colY.
    
    3. TS2.colX does not contain unique key of all TS2 tables.
    
    4. TS2.colZ contains unique key of all TS2 tables.
    
    Symptom:
    Query's select distinct or group-by column(s) has same values in
    query result set. In db2exfmt generated plan file, the
    diagnostic message shows MQT is used.
    

Local fix

  • Set "CURRENT QUERY OPTIMIZATION" special register to 0, 1 or 3,
    or drop the MQT table in question.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All users                                                    *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 v9.7 FP8                                      *
    ****************************************************************
    

Problem conclusion

  • The problem was first fixed in DB2 v9.7 FP8
    

Temporary fix

  • See the APAR description
    

Comments

APAR Information

  • APAR number

    IC88531

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2012-11-26

  • Closed date

    2013-04-08

  • Last modified date

    2013-04-08

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

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

    IC90174

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSY

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC88531

Modified date: 08 April 2013