IZ16816: MQT FAILS TO BE MATCHED WHEN 2 OR MORE EQUALITY PREDICATES EXIST WITH THE SAME LITERAL

APAR status

• Closed as program error.

Error description

• MQT fails to be matched when 2 or more equality predicates exist
  with the same literal
  
  an example is
  
  -- create base tables
  CREATE TABLE T1  (
            "ID1" INTEGER ,
            "X1"  INTEGER ,
            "C1"  INTEGER );
            ;
  
  CREATE TABLE T2  (
            "ID2" INTEGER ,
            "C2"  INTEGER );
  
  CREATE TABLE T3  (
            "ID3" INTEGER ,
            "X3"  INTEGER ,
            "C3"  INTEGER );
  
  CREATE TABLE T4  (
            "X4"  INTEGER ,
            "C4"  INTEGER );
  
  CREATE TABLE T5  (
            "ID5" INTEGER );
  
  -- create a view
  CREATE VIEW V1 AS (
  SELECT COUNT(*) AS COUNT,
         T3.ID3 AS VID3
  FROM T3, T4
  WHERE T3.C3 = T4.C4
        AND T4.X4 = 100
        AND T3.X3 = 100
  GROUP BY T3.ID3 );
  
  -- create MQT
  CREATE TABLE MQT AS ( SELECT * FROM V1) DATA INITIALLY DEFERRED
  REFRESH IMMEDIATE;
  REFRESH TABLE MQT;
  
  -- Query is not routed to MQT
  SELECT  1
    FROM T5, V1
    WHERE T5.ID5 = V1.VID3
          AND T5.ID5 IN
    (
     SELECT T5.ID5
     FROM T2
     INNER JOIN T5
          ON T5.ID5 = T2.ID2
     INNER JOIN V1
          ON V1.VID3 = T2.ID2
     LEFT OUTER JOIN T1 AS T1
          ON T1.ID1 = T2.ID2
          AND T1.X1 = 100
    WHERE
          ( T1.C1 = 200 OR T1.C1 = 300 )
          AND T2.C2 = 400
    GROUP BY T5.ID5
    )
  ;
  
  Two characteristics to get MQT not be matched in this case
  (1) MQT has >=2 local equality preds whose literal are same,
  called literal_x (100 in the example).
  (2) Query has a CSE (Common Sub-Expression) that can be matched
  with MQT.
  One CSE consumer is a row preserving side of OUTER JOIN, and
  NULL PRODUCING side of this OJ has a local equality pred whose
  constant is literal_x too.
  
  The other constructs are also needed to force a particular path
  of QRW rule sequences to trigger the problem.
  

Local fix

• n/a
  

Problem summary

• Users affected : users with MQT tables.
  Problem summary: in some cases an MQT may not be used leading to
  suboptimal performance
  Problem conclusion : This apar fix will ensure that the mqt is
  used.
  

Problem conclusion

• This problem was first fixed in v8.2 fixpak 17
  

Temporary fix

Comments

APAR Information

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

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

  IZ16820

Fix information

• Fixed component name

  DB2 UDB ESE SOL

• Fixed component ID

  5765F4102

Applicable component levels

• R820 PSY

     UP