IBM Support

IC82326: ADJUST FILTER FACTOR FOR QUERIES WITH HOST VARIABLES AGAINST TABLES WITH CONSTRAINTS

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as fixed if next.

Error description

  • 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
    

Local fix

Problem summary

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

Problem conclusion

Temporary fix

Comments

APAR Information

  • APAR number

    IC82326

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED FIN

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2012-03-28

  • Closed date

    2012-11-05

  • Last modified date

    2012-11-05

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

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

    IC88354

Fix information

Applicable component levels

  • R970 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC82326

Modified date: 05 November 2012