DB2 FOR LUW / DB2FORLUW / 970 - DB2

A query that includes host variables against a table with 
constraints can have a check added to the optimized SQL to 
validate the host variables are within the proper range of the 
constraints.  This provides an early out and prevents the query 
from running against the actual tables if the values would not 
return any rows. 
 
This operator does not filter any rows in the actual output. 
However, the filter factor estimate for this operator reduces 
the estimated cardinality of the results, causing the access 
plan to underestimate the actual cost. 
 
For instance, the following example shows the GENROW filter 
factor results in a estimate of 0.111111 rows.  Applied to the 
base table, that reduces the estimate from 40 rows down to 
4.44444 rows.  Since this operator does not actually filter any 
rows, so the filter factor should be 1 and keeping the estimate 
of 
40 rows. 
 
 
CREATE TABLE DB2INST1.TEST1 (COL1 INT, COL2 INT); 
ALTER TABLE DB2INST1.TEST1 ADD CONSTRAINT COL1_CHK CHECK (COL1 
BETWEEN 1 AND 4) ENFORCED ENABLE QUERY OPTIMIZATION; 
ALTER TABLE DB2INST1.TEST1 ADD CONSTRAINT COL2_CHK CHECK (COL2 
BETWEEN 0 AND 127) ENFORCED ENABLE QUERY OPTIMIZATION; 
 
 
Original Statement: 
------------------ 
select col2 
from db2inst1.test1 
where col1 = ? 
 
 
Optimized Statement: 
------------------- 
SELECT Q3.COL2 AS "COL2" 
FROM 
   (SELECT Q1.$C0 
   FROM (VALUES 0) AS Q1 
   WHERE (? < 127) AND (0 < ?)) AS Q2, DB2INST1.TEST1 AS Q3 
WHERE (Q3.COL1 = ?) 
 
 
               Rows 
              RETURN 
              (   1) 
               Cost 
                I/O 
                | 
              4.44444 
              NLJOIN 
              (   2) 
              15.646 
                 2 
          /-----+------\ 
     0.111111            40 
      TBSCAN           TBSCAN 
      (   3)           (   4) 
    0.00025113         15.6458 
         0                2 
        |                | 
         1              1000 
 TABFNC: SYSIBM    TABLE: DB2INST1 
      GENROW            TEST1 
        Q1               Q3

**************************************************************** 
* USERS AFFECTED:                                              * 
* DB2 UDB Version 9.7                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to Version 9.7 FixPack 7.                            * 
****************************************************************

DB2 Version 9.7 Fix Pack 7 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 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

not known / see Local fix

forerunner  : APAR is sysrouted TO one or more of the following: IC88354 
follow-up : 

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

28.03.2012
05.11.2012
05.11.2012

9.7.FP7