IBM Support

IC85196: CREATING A UNIQUE GLOBAL INDEX ON A TABLE WITH DETACHED PARTITIO N AND DEPENDANT MQT MIGHT LEAD TO INCORRECT RESULT AFTER REFRESH

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • After detaching a partition from a range partitioned table with
    a dependent materialized query table (MQT), a unique global
    index might get created.
    If a unique global index gets created and the detached partition
    has any duplicates, refreshing the MQT might lead to incorrect
    results.
    
    The following commands illustrate how refreshing the MQT might
    lead to incorrect results:
    
    create table t1(rk int not null, uk int not null) partition by
    range(rk) (starting 0 ending 10001 every 1000);
    insert into t1 values(1,1),(2,1);
    
    create table t2 (uk int not null);
    insert into t2 values(1);
    
    create table m1 as (select t1.uk, count(*) as count from t1, t2
    where t1.uk=t2.uk group by t1.uk) data initially deferred
    refresh immediate;
    refresh table m1;
    
    select * from m1;
    
    UK          COUNT
    ----------- -----------
              1           2
    
      1 record(s) selected.
    
    alter table t1 detach partition part0 into part0;
    create unique index t1_uk on t1(uk) not partitioned;
    refresh table m1;
    
    select * from m1;
    
    UK          COUNT
    ----------- -----------
              1           1
    
      1 record(s) selected.
    
    To identify the MQTs that are affected by this problem, perform
    the following steps:
    
    1) Record the current query optimization level and set it to 3
    by issuing the following commands:
    
    select current query optimization from sysibm.sysdummy1;
    set current query optimization 3;
    
    2) Verify if querying the MQT and the MQT query definition
    return different result set by issuing the following commands:
    
    (mqt_query_definition) except all select * from mqt
    select * from mqt except all (mqt_query_definition)
    
    Using the previous example, the resultant queries are as
    follows:
    
    (select t1.uk, count(*) as count from t1, t2 where t1.uk=t2.uk
    group by t1.uk) except all select * from m1
    
    UK          COUNT
    ----------- -----------
    
      0 record(s) selected.
    
    
    select * from m1 except all (select t1.uk, count(*) as count
    from t1, t2 where t1.uk=t2.uk group by t1.uk)
    
    UK          COUNT
    ----------- -----------
              1           1
    
      1 record(s) selected.
    
    If either of the EXCEPT ALL queries return any record, then that
    MQT is
    impacted and is returning incorrect result.
    

Local fix

  • Run "REFRESH TABLE <mqt_name> NOT INCREMENTAL" to do a full
    refresh on the MQT
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All users running DB2 Version 9.7 GA through Fix Pack 6 for  *
    * Linux, Unix and Windows and creating global unique index     *
    * after detaching a partition.                                 *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Update to DB2 Version 9.7 Fix Pack 7 or follow Local Fix.    *
    ****************************************************************
    

Problem conclusion

  • Problem is first fixed in DB2 Version 9.7 Fix Pack 7.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC85196

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    YesSpecatt / Pervasive

  • Submitted date

    2012-07-09

  • Closed date

    2012-10-18

  • Last modified date

    2012-12-07

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

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

    IC86029 IC86325 IC86330

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.7","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
07 December 2012