LI74932: IMPROVEMENT ON PROCESSING FETCH FIRST N ROWS IN DPF ENVIRONMENT

DB2 Version 9.5 Fix Pack 6a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• 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.
  

Local fix

Problem summary

• IMPROVEMENT ON PROCESSING FETCH FIRST N ROWS IN DPF ENVIRONMENT
  

Problem conclusion

• Fixed in Db2 v95FP6.
  

Temporary fix

Comments

APAR Information

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

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

  IC63295 IC67537 IC74754

Fix information

• Fixed component name

  DB2 UDE ESE LIN

• Fixed component ID

  5765F4104

Applicable component levels

• R910 PSY

     UP

• R950 PSY

     UP