DB2 FOR LUW / DB2FORLUW / 980 - DB2

In DPF environment, DB2 optimizer always generates an access 
plan for the FETCH FIRST N ROWS clause by sending data to a 
coordinator node and fetch the first N rows of the data. 
 
This can cause performance degradation and in the access plan of 
the query using FETCH FIRST N ROWS, we can see redundant TQ. 
 
 
 
 
 
                      Rows 
                     RETURN 
                     (   1) 
                      Cost 
                       I/O 
                       | 
                      50000 
                     DELETE 
                     (   2) 
                     768333 
                     134645 
                  /----+----\ 
               50000      9.3116e+06 
              FETCH   DP-TABLE: TABLE1 
              (   3) 
              390 
               84645 
           /----+-----\ 
        50000       9.3116e+06 
       DTQ      DP-TABLE: TABLE1 
 
 
       (   4) 
       12152.9 
        34645 
         | 
        1e+06 
       DTQ 
       (   5) 
       12088.6 
        34645 
         | 
       804913 
       TBSCAN 
       (   6) 
       11979.4 
        34645 
         | 
     9.3116e+06 
 DP-TABLE: TABLE1 
 
 
 
 
 
 
 
 
This work detects cases where data are from a single node and 
being applied the FETCH FIRST N ROWS clause. DB2 optimizer will, 
instead of moving the data to a coordinator node, locally fetch 
the first N rows from the node the date reside. 
 
Example: 
 
DELETE FROM (SELECT * FROM TABLE1 T WHERE DBPARTITIONNUM(T.C1) = 
1 FETCH FIRST 10 ROWS ONLY); 
 
DB2 optimizer will fetch the first 10 rows from table TABLE1 at 
database partition number 1 and send the 10 rows to the DELETE 
operation locally in the same database partition.

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* IMPROVEMENT ON PROCESSING FETCH FIRST N ROWS IN DPF          * 
* ENVIRONMENT                                                  * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 V98FP4                                        * 
****************************************************************

DB2 Version 9.8 Fix Pack 4 for AIX and Linux
 DB2 Version 9.8 Fix Pack 5 for AIX and Linux
 

Fixed in DB2 V98 FP4

keiner bekannt / siehe Local-Fix

Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:

01.04.2010
05.08.2011
05.08.2011

9.8.FP4