IBM Support

IC75014: DB2 MAY NOT APPLY LOCAL PREDICATE WITH BIGINT, INTEGER, SMALLINT IN AN OPTIMAL WAY MAY RETURN INCORRECT RESULT ON LENGTH FUNCTION

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • DB2 may not apply local predicates with BIGINT, INTEGER,
    SMALLINT on varying-length char strings in an optimal way or
    may return incorrect results with LENGTH function on
    varying-length char strings
    
    There are two problems:
    
    (1) DB2 may not push down local predicates containing an
    expression of the form <function>(<expr>) where
    
    <function> is BIGINT, INTEGER or SMALLINT and
    <expr> is an expression of the varying-length character strings
    (such as VARCHAR, VARGRAPHIC, CLOB, etc.)
    
    to lower operations. This may result in performance degradation
    when upgrading DB2 from earlier releases to Version 9.7.
    
    (2) DB2 may return incorrect results when running the SQL
    statement containing predicates containing an expression of the
    form
    LENGTH(<expr>) where
    
    <expr> is an expression of the varying-length character strings
    of type VARCHAR, VARGRAPHIC, CLOB, NCLOB.
    
    and the predicates are applied above Group By operation.
    
    For example:
    
    db2 => create table t1(c1 varchar(10), c2 int, c3 int);
    db2 => insert into t1 values ('C1', 1, -1), ('C1 ', 2, -2), ('C1
    ', -1, 1);
    db2 => select * from t1;
    
    C1         C2          C3
    ---------- ----------- -----------
    C1                   1          -1
    C1                   2          -2
    C1                  -1           1
    
      3 record(s) selected.
    
    -- Correct result
    db2 => select c1,sum(c2),sum(c3) from t1 group by c1 having
    length(c1) = 2;
    
    C1         2           3
    ---------- ----------- -----------
    C1                   2          -2
    
      1 record(s) selected.
    
    -- Incorrect result because LENGTH(c1) is applied before Group
    By
    db2 => select * from (select c1,sum(c2),sum(c3) from t1 group by
    c1 ) as q where length(c1)=2;
    
    C1         2           3
    ---------- ----------- -----------
    C1                   1          -1
    
      1 record(s) selected.
    
    
    This problem occurs in DB2 Version 9.7 GA and Fix Packs 1,2, and
    3
    

Local fix

  • Upgraded to DB2 Version 9.7 Fix Pack 4
    

Problem summary

  • Users Effected:
    All user using V9.7
    
    Problem Description:
    DB2 may not apply local predicates with BIGINT, INTEGER,
    SMALLINT on varying-length char strings in an optimal way or
    may return incorrect results with LENGTH function on
    varying-length char strings
    
    There are two problems:
    
    (1) DB2 may not push down local predicates containing an
    expression of the form <function>(<expr>) where
    
    <function> is BIGINT, INTEGER or SMALLINT and
    <expr> is an expression of the varying-length character strings
    (such as VARCHAR, VARGRAPHIC, CLOB, etc.)
    
    to lower operations. This may result in performance degradation
    when upgrading DB2 from earlier releases to Version 9.7.
    
    (2) DB2 may return incorrect results when running the SQL
    statement containing predicates containing an expression of the
    form
    LENGTH(<expr>) where
    
    <expr> is an expression of the varying-length character strings
    of type VARCHAR, VARGRAPHIC, CLOB, NCLOB.
    
    and the predicates are applied above Group By operation.
    
    For example:
    
    db2 => create table t1(c1 varchar(10), c2 int, c3 int);
    db2 => insert into t1 values ('C1', 1, -1), ('C1 ', 2, -2), ('C1
    ', -1, 1);
    db2 => select * from t1;
    
    C1         C2          C3
    ---------- ----------- -----------
    C1                   1          -1
    C1                   2          -2
    C1                  -1           1
    
      3 record(s) selected.
    
    -- Correct result
    db2 => select c1,sum(c2),sum(c3) from t1 group by c1 having
    length(c1) = 2;
    
    C1         2           3
    ---------- ----------- -----------
    C1                   2          -2
    
      1 record(s) selected.
    
    -- Incorrect result because LENGTH(c1) is applied before Group
    By
    db2 => select * from (select c1,sum(c2),sum(c3) from t1 group by
    c1 ) as q where length(c1)=2;
    
    C1         2           3
    ---------- ----------- -----------
    C1                   1          -1
    
      1 record(s) selected.
    
    
    This problem occurs in DB2 Version 9.7 GA and Fix Packs 1,2, and
    3
    
    Problem Summary:
    DB2 MAY NOT APPLY LOCAL PREDICATE WITH BIGINT, INTEGER, SMALLINT
    IN AN OPTIMAL WAY MAY RETURN INCORRECT RESULT ON LENGTH FUNCTION
    

Problem conclusion

  • defect  wsdbu00823817
    CSD     First fixed in DB2 UDB Version 9.7, FixPak 4
    module  engn_sqnr
    

Temporary fix

  • Upgrade to DB2 Version 9.7, FixPak 4
    

Comments

APAR Information

  • APAR number

    IC75014

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2011-03-15

  • Closed date

    2011-05-02

  • Last modified date

    2011-05-02

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

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

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSY

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.7","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
02 May 2011