IBM Support

IC77085: QUERY USING A VIEW BASED ON REPLICATED MQT IN CONTEXT OF EXISTENTIAL SUBQUERY MAY RETURN INCORRECT RESULTS

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • A query might produce incorrect results under the following
    scenario:
    
    1. The environment is a multi-partition DPF setup.
    2. There is a view which references one or more replicated MQTs
       directly.
    3. The view is used in the context of an existential subquery.
    4. (Optional) if replicated table is maintained by user, there
       does not exist a unique index
    
    Example:
    
    create table t1(c1 int, c2 int);
    
    create table t2(c1 int, c2 int);
    
    create table repl_mqt as (select * from t2) data initially
    deferred refresh deferred distribute by replication;
    
    create view v1 as (select * from repl_mqt);
    
    select * from v1 where v1.c2 in (select c2 from t1);
    

Local fix

  • The view should reference the base table instead of the
    replicated MQT directly.
    

Problem summary

  • APAR fix is available in V9.7 Fixpack 5.
    

Problem conclusion

  • APAR fix is available in V9.7 Fixpack 5.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC77085

  • 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

    2011-06-21

  • Closed date

    2011-12-12

  • Last modified date

    2011-12-12

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

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

    IC80101

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 #: IC77085

Modified date: 12 December 2011