DB2 FOR LUW / DB2FORLUW / A10 - 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:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 V10.1 Fix Pack 2                              * 
****************************************************************

DB2 Version 10.1 Fix Pack 2 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 3a for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows
 

not known / see Local fix

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

17.11.2012
18.12.2012
18.12.2012