IBM Support

IT28568: AN OUTER JOIN QUERY MIGHT BE WRONGFULLY RE-WRITTEN TO AN INNER JOIN QUERY WHEN MQT MATCHING IS ENFORCED FOR SHADOW TABLES.

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • Description:
    
    When the following registries are set and there's a matching
    shadow table for the base table used in the original query, the
    outer join can be wrongfully converted into an inner join.
    
    db2 "set CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION
    REPLICATION"
    db2 "SET CURRENT REFRESH AGE ANY"
    
    With registries and mqt enforced, the table used in the outer
    join could be replaced with its matching MQT. After replacing
    the table with its MQT, the outer join could wrongfully turn
    into inner join which could cause a wrong result.
    
    Workaround:
    
    Disable shadow tables when optimizing when optimizing the
    processing of dynamic SQL queries.
    
    db2 "set CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION
    NONE"
    

Local fix

  • Disable shadow tables when optimizing when optimizing the
    processing of dynamic SQL queries.
    
    db2 "set CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION
    NONE"
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * all                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to 11.1.4.7                                          *
    ****************************************************************
    

Problem conclusion

  • Upgrade to 11.1.4.7
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT28568

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    B10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2019-03-25

  • Closed date

    2022-04-18

  • Last modified date

    2022-04-20

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

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

    IT30838 IT30839

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • RB10 PSN

       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:
04 May 2022