IBM Support

IT29458: POSSIBLE PERFORMANCE PROBLEMS FOR INSERT STATEMENTS WITH COMMON-TABLE EXPRESSIONS

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • If an Insert statement references common table expressions,
    either user specified or internally generated by the Db2
    compiler, and the common table expressions reference correlated
    expressions, a TEMP might be introduced in the execution plan.
    This in turn might prevent index access and other optimizations.
    
    Example statement and execution plan:
    
    create table it1(c1 int, c2 int) organize by row;
    create table it2(c1 int, c2 int) organize by row;
    create table itgt(c1 int, c2 int) organize by row;
    create index xit1 on it1 (c2);
    
    create view iv1 (c1, c2) as
    select c1, c2 from it1 where not exists (select * from it2 where
    it1.c1 = it2.c1);
    
    insert into itgt(c1) select (select c1 from iv1) from iv1 where
    c2 < 3;
    
    
                               Rows
                              RETURN
                              (   1)
                               Cost
                                I/O
                                |
                            0.00231481
                              INSERT
                              (   2)
                              97.8006
                                 6
                           /----+----\
                     0.00231481        144
                       NLJOIN    TABLE: URSU
                       (   3)         ITGT
                       90.4978         Q7
                          5
                     /---+---\
                    1      0.00231481
                 TBSCAN      TBSCAN
                 (   4)      (   9)
                 41.7894     41.7895
                    2           2
                   |           |
               0.00694444  0.00694444
                 TEMP        TEMP
                 (   5)      (   5)
                 40.5222     40.5222
                    2           2
                   |
               0.00694444
                 HSJOINx
                 (   6)
                 40.0641
                    2
             /-----+------\
           144              144
         TBSCAN           TBSCAN
         (   7)           (   8)
         18.7763          18.7763
            1                1
           |                |
           144              144
    TABLE: URSU      TABLE: URSU
           IT2              IT1
           Q2               Q1
    

Local fix

  • To allow replication of common table expressions for Insert
    statements, apply the following guideline:
    
    insert into itgt(c1)
    select (select c1 from iv1)
    from iv1 where c2 < 3
    /* <OPTGUIDELINES>
       <REGISTRY>
            <OPTION NAME='DB2_EXTENDED_OPTIMIZATION'
    VALUE='BREAK_CORR_CSE ON'/>
       </REGISTRY>
    </OPTGUIDELINES> */
    ;
    
    
    Execution plan after the guideline.
    
                                          Rows
                                         RETURN
                                         (   1)
                                          Cost
                                           I/O
                                           |
                                        0.0208333
                                         INSERT
                                         (   2)
                                         81.2561
                                            5
                                      /----+----\
                                 0.0208333        144
                                  NLJOIN    TABLE: URSU
                                  (   3)         ITGT
                                  73.9529         Q10
                                     4
                     /--------------+---------------\
                    1                              0.0208333
                 HSJOINx                            HSJOINx
                 (   4)                             (   7)
                 39.7646                            34.1704
                    2                                  2
             /-----+------\                    /------+-------\
           144              144              144                48
         TBSCAN           TBSCAN           TBSCAN             FETCH
         (   5)           (   6)           (   8)             (   9)
         18.7763          18.7763          18.7763
    13.9712
            1                1                1                  1
           |                |                |
    /---+----\
           144              144              144          48
    144
    TABLE: URSU      TABLE: URSU      TABLE: URSU      IXSCAN
    TABLE: URSU
           IT2              IT1              IT2        (  10)
    IT1
           Q5               Q6               Q1         4.90264
    Q2
                                                           0
                                                          |
                                                          144
                                                    INDEX: URSU
                                                         XIT1
                                                          Q2
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * all                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to 11.1.4.7                                          *
    ****************************************************************
    

Problem conclusion

  • Upgrade to 11.1.4.7
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT29458

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    B10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2019-06-16

  • Closed date

    2022-04-16

  • Last modified date

    2022-04-16

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

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

    IT39621

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • RB10 PSN

       UP

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.1","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
03 May 2022