IBM Support

IT29346: POSSIBLE PERFORMANCE REGRESSION FOR UPDATE STATEMENTS WHEN MIGRATING TO DB2 V11.1.4

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as fixed if next.

Error description

  • If an Update statement references complex views with correlated
    predicates, the performance might be regressed.
    
    Example statement:
    
    create view v1 (c1, c2) as (
    select tt1.c1, tt2.c2 from tt1 left outer join tt2 on tt1.c3 =
    tt2.c3);
    
    update ttgt set c1 = 1
    where c2 <= (select v2.c2
    from (select  (select c2 from v1 where c1 = t3.c1) as sc1,
                   (select c2 from v1 where c1 = t3.c1) as sc2  from
    t3) as
                   v2(c1, c2));
    
    
    In v11.1.4, correlated predicates are not pushed down into
    complex views. This might prevent index access to be used.
    
    To allow index access, set the following registry variable at
    the statement level:
    
      /* <OPTGUIDELINES>
         <REGISTRY>
                 <OPTION NAME='DB2_EXTENDED_OPTIMIZATION'
    VALUE='CSE_CORR_PPD ON'/>
         </REGISTRY>
        </OPTGUIDELINES> */
    
    
    Execution plan for the above statement with the registry
    variable set:
    
    
                                       Rows
                                      RETURN
                                      (   1)
                                       Cost
                                        I/O
                                        |
                                      76.6667
                                      UPDATE
                                      (   2)
                                      2192.32
                                      81.6667
                                    /---+----\
                                76.6667        115
                                NLJOIN   TABLE: URSU
                                (   3)        TTGT
                                1633.8         Q1
                                   5
                        /---------+---------\
                       1                    76.6667
                    NLJOIN                  TBSCAN
                    (   4)                  (  10)
                    1614.98                 18.8258
                       4                       1
             /--------+--------\              |
           115                    1           115
         TBSCAN                HSJOIN<  TABLE: URSU
         (   5)                (   6)        TTGT
         16.7788               27.4837        Q9
            1                     2
           |              /------+-------\
           115          115                 1
    TABLE: URSU      TBSCAN             FETCH
           TT3        (   7)             (   8)
           Q2         16.7788            9.88324
                         1                  1
                        |              /---+----\
                        115           1           119
                  TABLE: URSU      IXSCAN   TABLE: URSU
                        TT2        (   9)         TT1
                        Q5         2.52544        Q3
                                      0
                                     |
                                     119
                               INDEX: URSU
                                    XTT1
                                     Q3
    
    Execution plan for the above statement with the registry
    variable unset:
    
                               Rows
                              RETURN
                              (   1)
                               Cost
                                I/O
                                |
                              76.6667
                              UPDATE
                              (   2)
                              4364.81
                              80.6667
                            /---+----\
                        76.6667        115
                        NLJOIN   TABLE: URSU
                        (   3)        TTGT
                        3806.29        Q1
                           4
                   /------+-------\
                  1               76.6667
               NLJOIN             TBSCAN
               (   4)             (  10)
               3787.47            18.8258
                  3                  1
             /---+----\             |
           115           1          115
         TBSCAN       FILTER  TABLE: URSU
         (   5)       (   6)       TTGT
         16.7788      46.435        Q8
            1            2
           |            |
           115         547.4
    TABLE: URSU      HSJOIN<
           TT3        (   7)
           Q2         36.6913
                         2
                  /-----+------\
                115              119
              TBSCAN           TBSCAN
              (   8)           (   9)
              16.7788          17.0543
                 1                1
                |                |
                115              119
          TABLE: URSU      TABLE: URSU
                TT2              TT1
                Q4               Q3
    

Local fix

  • NA
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * 25                                                           *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to 11.1.4.6                                          *
    ****************************************************************
    

Problem conclusion

Temporary fix

Comments

APAR Information

  • APAR number

    IT29346

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    B10

  • Status

    CLOSED FIN

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2019-06-05

  • Closed date

    2021-03-22

  • Last modified date

    2021-03-22

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

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

Fix information

Applicable component levels

[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.1"}]

Document Information

Modified date:
23 March 2021