IC90174: 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                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade the database server to V10.1 Fix Pack 3              *
    ****************************************************************
    

Problem conclusion

  • This problem was first fixed in DB2 V10.1 Fix Pack 3
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC90174

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-02-13

  • Closed date

    2013-10-21

  • Last modified date

    2013-10-21

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

    IC88531

  • 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

  • RA10 PSN

       UP



Rate this page:

(0 users)Average rating

Document information


More support for:

DB2 for Linux, UNIX and Windows

Software version:

10.1

Reference #:

IC90174

Modified date:

2013-10-21

Translate my page

Machine Translation

Content navigation