Latest versionsfixlist
11.1.0.7 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Have problems? - contact us.
Register for free anmeldung-x26
Contact form kontakt-x26

DB2 - Problem description

Problem IC73581 Status: Closed

NESTED-LOOP JOIN WITH EARLYOUT FOR GROUPBY CLAUSES, YIELDS INCORRECT
RESULTS WHEN JOIN COLUMNS ARE OF DIFFERENT DATA TYPES

product:
DB2 FOR LUW / DB2FORLUW / 970 - DB2
Problem description:
When a query that meets all the following conditions is 
executed, fewer results may be returned: 
- Several tables are joined using columns with different data 
types 
- Nested-loop join is selected as the join method 
- The output from the nested-loop join method is included into 
the Group by clause and the Early Out flag is marked as GROUPBY 
 
The following scenarios indicate the problem: 
 
1. Joining two tables with different data types. To demonstrate 
this problem, create two tables for the test.  Create the tables 
such that the columns being joined (t1.i1 and t2.i1) have 
different data types. 
create table t1 (i1 bigint not null, c2 char(250)) 
create index i1 on t1 (i1) 
create table t2 (i1 smallint not null, i2 bigint) 
create index i2 on t2 (i1,i2) 
2. Run the query with different data types (bigint and smallint) 
in predicate and the result goes directly into the Group by 
clause. 
select t2.i1, max(t2.i2) from t1, t2 where t1.i1=t2.i1 group by 
t2.i1 
3. When the Nested-loop join is selected as the join method. 
 
Access Plan: 
----------- 
        Total Cost:             22.8806 
        Query Degree:           1 
              Rows 
             RETURN 
             (   1) 
              Cost 
               I/O 
               | 
               99 
             GRPBY 
             (   2) 
             22.8709 
                3 
               | 
               99 
             ^NLJOIN 
             (   3) 
             22.8655 
                3 
         /-----+------\ 
       99             12.1111 
     IXSCAN           IXSCAN 
     (   4)           (   5) 
    0.0455361         7.57676 
        0                1 
       |                | 
       99              1199 
 INDEX: DB2INST1   INDEX: DB2INST1 
       I1               I2 
       Q2               Q1 
        3) NLJOIN: (Nested Loop Join) 
                Arguments: 
                --------- 
                EARLYOUT: (Early Out flag) 
                        GROUPBY 
                Predicates: 
                ---------- 
                3) Predicate used in Join 
                        Predicate Text: 
                        -------------- 
                        (Q2."I1" = Q1."I1")
Problem Summary:
**************************************************************** 
* USERS AFFECTED:                                              * 
* All users.                                                   * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* When a query that meets all the following conditions is      * 
* executed, fewer results may be returned:                     * 
* - Several tables are joined using columns with different     * 
* data types                                                   * 
* - Nested-loop join is selected as the join method            * 
* - The output from the nested-loop join method is included    * 
* into the Group by clause and the Early Out flag is marked as * 
* GROUPBY                                                      * 
*                                                              * 
* The following scenarios indicate the problem:                * 
*                                                              * 
* 1. Joining two tables with different data types. To          * 
* demonstrate this problem, create two tables for the test.    * 
* Create the tables such that the columns being joined (t1.i1  * 
* and t2.i1) have different data types.                        * 
* create table t1 (i1 bigint not null, c2 char(250))           * 
* create index i1 on t1 (i1)                                   * 
* create table t2 (i1 smallint not null, i2 bigint)            * 
* create index i2 on t2 (i1,i2)                                * 
* 2. Run the query with different data types (bigint and       * 
* smallint) in predicate and the result goes directly into the * 
* Group by clause.                                             * 
* select t2.i1, max(t2.i2) from t1, t2 where t1.i1=t2.i1 group * 
* by t2.i1                                                     * 
* 3. When the Nested-loop join is selected as the join method. * 
*                                                              * 
* Access Plan:                                                 * 
* -----------                                                  * 
* Total Cost:             22.8806                              * 
* Query Degree:           1                                    * 
* Rows                                                         * 
* RETURN                                                       * 
* (   1)                                                       * 
* Cost                                                         * 
* I/O                                                          * 
* |                                                            * 
* 99                                                           * 
* GRPBY                                                        * 
* (   2)                                                       * 
* 22.8709                                                      * 
* 3                                                            * 
* |                                                            * 
* 99                                                           * 
* ^NLJOIN                                                      * 
* (   3)                                                       * 
* 22.8655                                                      * 
* 3                                                            * 
* /-----+------\                                               * 
* 99             12.1111                                       * 
* IXSCAN           IXSCAN                                      * 
* (   4)           (   5)                                      * 
* 0.0455361         7.57676                                    * 
* 0                1                                           * 
* |                |                                           * 
* 99              1199                                         * 
* INDEX: DB2INST1   INDEX: DB2INST1                            * 
* I1               I2                                          * 
* Q2               Q1                                          * 
* 3) NLJOIN: (Nested Loop Join)                                * 
* Arguments:                                                   * 
* ---------                                                    * 
* EARLYOUT: (Early Out flag)                                   * 
* GROUPBY                                                      * 
* Predicates:                                                  * 
* ----------                                                   * 
* 3) Predicate used in Join                                    * 
* Predicate Text:                                              * 
* --------------                                               * 
* (Q2."I1" = Q1."I1")                                          * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 9.7.0.4.                                      * 
****************************************************************
Local Fix:
Execute the following and then recycle the instance: 
db2set DB2_REDUCED_OPTIMIZATION=NO_NLJN_EO_FOR_GB
available fix packs:
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 6 for Linux, UNIX, and Windows
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

Solution
The problem was fixed in DB2 9.7.0.4.
Workaround
not known / see Local fix
Timestamps
Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :
29.12.2010
20.04.2011
20.04.2011
Problem solved at the following versions (IBM BugInfos)
9.7.0.4
Problem solved according to the fixlist(s) of the following version(s)
9.7.0.4 FixList
This site uses cookies to make it easier for us to provide you with our services. By using our site you agree to the use of cookies.