IBM Support

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

Subscribe

You can track all active APARs for this component.

 

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 number

    LI74932

  • Reported component name

    DB2 UDE ESE LIN

  • Reported component ID

    5765F4104

  • Reported release

    950

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2009-09-21

  • Closed date

    2010-09-14

  • Last modified date

    2010-09-14

  • 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

[{"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":"950","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
14 September 2010