DB2 FOR LUW / DB2FORLUW / 970 - DB2

Queries containing joins based on referential integrity (RI) 
defined in the database, i.e., primary, unique and foreign keys 
(PK, UK and FK), are used by the DB2 Query ReWrite engine (QRW) 
to optimize the query graph (QGM) for optimal query performance. 
. 
This usually allows dropping of PK tables from joins used in the 
query. However, presence of certain constructs in the query may 
cause QRW to optimize the QGM in such a way that PK tables that 
are droppable given the RI may not be dropped, and consequently 
may adversely affect query performance. 
. 
For example, consider the following scenario: 
-- Table and Primary Key DDL 
CREATE TABLE SON (DAD_ID INTEGER NOT NULL 
                  CONSTRAINT PK_SON PRIMARY KEY, 
-- PK 
                  MOM_ID INTEGER NOT NULL, 
                  ID INTEGER NOT NULL WITH DEFAULT 0, 
                  AGE INTEGER NOT NULL WITH DEFAULT 0 
                 ); 
. 
CREATE TABLE MOM (ID INTEGER NOT NULL 
                  CONSTRAINT PK_MOM PRIMARY KEY 
-- PK 
                 ); 
. 
CREATE TABLE DAD (ID INTEGER NOT NULL 
                  CONSTRAINT PK_DAD PRIMARY KEY, 
-- PK 
                  ANCSTR_ID INTEGER NOT NULL 
                 ); 
. 
CREATE TABLE GRANDPA (ID INTEGER NOT NULL 
                      CONSTRAINT PK_GRANDPA PRIMARY KEY 
-- PK 
                     ); 
. 
CREATE TABLE CHILDREN_OF (ID INTEGER NOT NULL); 
. 
-- Foreign Key DDL 
ALTER TABLE SON 
ADD CONSTRAINT FK_SON_MOM FOREIGN KEY (MOM_ID) 
REFERENCES MOM (ID) 
ON DELETE CASCADE 
ON UPDATE NO ACTION 
ENFORCED 
ENABLE QUERY OPTIMIZATION; 
. 
ALTER TABLE SON 
ADD CONSTRAINT FK_SON_DAD FOREIGN KEY (DAD_ID) 
REFERENCES DAD (ID) 
ON DELETE CASCADE 
ON UPDATE NO ACTION 
ENFORCED 
ENABLE QUERY OPTIMIZATION; 
. 
ALTER TABLE DAD 
ADD CONSTRAINT FK_DAD_GRANDPA FOREIGN KEY (ANCSTR_ID) 
REFERENCES GRANDPA (ID) 
ON DELETE CASCADE 
ON UPDATE NO ACTION 
ENFORCED 
ENABLE QUERY OPTIMIZATION; 
. 
ALTER TABLE CHILDREN_OF 
ADD CONSTRAINT FK_CHILDRENOF_GP FOREIGN KEY (ID) 
REFERENCES GRANDPA (ID) 
ON DELETE CASCADE 
ON UPDATE NO ACTION 
ENFORCED 
ENABLE QUERY OPTIMIZATION; 
. 
-- Query 
SELECT SON.AGE 
FROM   SON, 
       MOM, 
       DAD, 
       GRANDPA 
WHERE  SON.ID         = 1 
   AND SON.MOM_ID     = MOM.ID 
   AND SON.DAD_ID     = DAD.ID 
   AND DAD.ANCSTR_ID  = GRANDPA.ID 
 AND GRANDPA.ID     IN ( SELECT CHILDREN_OF.ID 
                           FROM   CHILDREN_OF 
                         ); 
. 
Due to the presence of the IN subquery predicate, QRW alters the 
QGM in such a way that the droppable PK tables in the query are 
not dropped (this can be determined by looking at the optimized 
statement section of the formatted EXPLAIN output of the query). 
In this case, these are the GRANDPA and MOM tables. 
. 
The enhancement in this APAR will attempt to address such issues 
on a best-effort basis by giving extra special consideration to 
the RI when altering the QGM in QRW.

Users Affected : All 
Problem Description : 
DB2 Query Rewrite may not take complete advantage of referential 
integrity to optimize a query. 
Problem Summary : 
Queries containing joins based on referential integrity (RI) 
defined in the database, i.e., primary, unique and foreign keys 
(PK, UK and FK), are used by the DB2 Query ReWrite engine (QRW) 
to optimize the query graph (QGM) for optimal query performance. 
This usually allows dropping of PK tables from joins used in the 
query. However, presence of certain constructs in the query may 
cause QRW to optimize the QGM in such a way that PK tables that 
are droppable given the RI may not be dropped, and consequently 
may adversely affect query performance. 
For example, consider the following scenario: 
-- Table and Primary Key DDL 
CREATE TABLE SON (DAD_ID INTEGER NOT NULL 
                  CONSTRAINT PK_SON PRIMARY KEY, 
-- PK 
                  MOM_ID INTEGER NOT NULL, 
                  ID INTEGER NOT NULL WITH DEFAULT 0, 
                  AGE INTEGER NOT NULL WITH DEFAULT 0 
                 ); 
CREATE TABLE MOM (ID INTEGER NOT NULL 
                  CONSTRAINT PK_MOM PRIMARY KEY 
-- PK 
                 ); 
 
CREATE TABLE DAD (ID INTEGER NOT NULL 
                  CONSTRAINT PK_DAD PRIMARY KEY, 
-- PK 
                  ANCSTR_ID INTEGER NOT NULL 
                 ); 
CREATE TABLE GRANDPA (ID INTEGER NOT NULL 
                      CONSTRAINT PK_GRANDPA PRIMARY KEY 
-- PK 
                     ); 
CREATE TABLE CHILDREN_OF (ID INTEGER NOT NULL); 
. 
-- Foreign Key DDL 
ALTER TABLE SON 
ADD CONSTRAINT FK_SON_MOM FOREIGN KEY (MOM_ID) 
REFERENCES MOM (ID) 
ON DELETE CASCADE 
ON UPDATE NO ACTION 
ENFORCED 
ENABLE QUERY OPTIMIZATION; 
 
ALTER TABLE SON 
ADD CONSTRAINT FK_SON_DAD FOREIGN KEY (DAD_ID) 
REFERENCES DAD (ID) 
ON DELETE CASCADE 
ON UPDATE NO ACTION 
ENFORCED 
ENABLE QUERY OPTIMIZATION; 
ALTER TABLE DAD 
ADD CONSTRAINT FK_DAD_GRANDPA FOREIGN KEY (ANCSTR_ID) 
REFERENCES GRANDPA (ID) 
ON DELETE CASCADE 
ON UPDATE NO ACTION 
ENFORCED 
ENABLE QUERY OPTIMIZATION; 
ALTER TABLE CHILDREN_OF 
ADD CONSTRAINT FK_CHILDRENOF_GP FOREIGN KEY (ID) 
REFERENCES GRANDPA (ID) 
ON DELETE CASCADE 
ON UPDATE NO ACTION 
ENFORCED 
ENABLE QUERY OPTIMIZATION; 
-- Query 
SELECT SON.AGE 
FROM   SON, 
       MOM, 
       DAD, 
       GRANDPA 
WHERE  SON.ID         = 1 
   AND SON.MOM_ID     = MOM.ID 
   AND SON.DAD_ID     = DAD.ID 
   AND DAD.ANCSTR_ID  = GRANDPA.ID 
 AND GRANDPA.ID     IN ( SELECT CHILDREN_OF.ID 
                           FROM   CHILDREN_OF 
                         ); 
Due to the presence of the IN subquery predicate, QRW alters the 
QGM in such a way that the droppable PK tables in the query are 
not dropped (this can be determined by looking at the optimized 
statement section of the formatted EXPLAIN output of the query). 
In this case, these are the GRANDPA and MOM tables. 
The enhancement in this APAR will attempt to address such issues 
on a best-effort basis by giving extra special consideration to 
the RI when altering the QGM in QRW.

No general workaround exists, however, potential workarounds may 
be available on a query by query basis.

DB2 Version 9.7 Fix Pack 1 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

Problem was first fixed in V9.7 FP1.

No general workaround exists, however, potential workarounds may 
be available on a query by query basis.

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

21.08.2009
15.12.2009
15.12.2009

9.7.FP1