DB2 FOR LUW / DB2FORLUW / 970 - 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.

IMPROVEMENT ON PROCESSING FETCH FIRST N ROWS IN DPF ENVIRONMENT

DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
 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
 

Fixed in DB2 v97FP3

keiner bekannt / siehe Local-Fix

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

21.09.2009
29.09.2010
29.09.2010

9.7.FP3