IBM Support

IC77636: OPTIMIZER CHOOSES TABLE SCANS INSTEAD OF INDEX ON DPF WITH DB2_INLIST_TO_NLJN

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • When registry variable DB2_INLIST_TO_NLJN is enabled, in DPF
    environment, DB2 optimizer can choose a Table Scan on a
    query of the following nature even though there is an index that
    could be used.
    
    
    Query:
    SELECT *
       FROM "TABLE1" T_00, (
          SELECT *
          FROM (VALUES CAST (? AS VARCHAR(72)), CAST (? AS
    VARCHAR(72)), CAST (? AS
                  VARCHAR(72)), CAST (? AS VARCHAR(72)), CAST (? AS
    VARCHAR(72)),
                  CAST (? AS VARCHAR(72)), CAST (? AS VARCHAR(72)),
    CAST (? AS
                  VARCHAR(72)), CAST (? AS VARCHAR(72)), CAST (? AS
    VARCHAR(72)),
                  CAST (? AS VARCHAR(72)), CAST (? AS VARCHAR(72)),
    CAST (? AS
                  VARCHAR(72)), CAST (? AS VARCHAR(72)), CAST (? AS
    VARCHAR(72)),
                  CAST (? AS VARCHAR(72)), CAST (? AS VARCHAR(72)),
    CAST (? AS
                  VARCHAR(72)), CAST (? AS VARCHAR(72)), CAST (? AS
    VARCHAR(72))) AS
                  T_01_TMP ("C_01")
          GROUP BY "C_01") AS T_01
       WHERE T_00."C_01" = T_01."C_01"
            WITH UR
    
     Plan chosen:
    
    
                      38.8155
                      NLJOIN
                      (   3)
                      208.919
                        177
                 /------+------\
             2.22222           17.467
             TBSCAN            TBSCAN
             (   4)            (   8)
            0.0401435          99.371
                0                85
               |                 |
             2.22222            6849
             SORT              TEMP
             (   5)            (   9)
            0.0396246          94.6416
                0                85
               |                 |
             2.22222            6849
             DTQ               DTQ
             (   6)            (  10)
            0.0386532          92.6006
                0                85
               |                 |
               20               6849
             TBSCAN            TBSCAN
             (   7)            (  11)
           0.000171107         90.6664
                0                85
               |                 |
               20               6849
        TABFNC: SYSIBM     TABLE:
             GENROW         SCHEMA1.TABLE1
               Q1                Q4
    
    
    The desired plan should make use of existing Index on TABLE1:
    
    
    
                         36.3512
                         NLJOIN
                         (   3)
                         302.628
                           40
                 /---------+---------\
               20                    1.81756
             TBSCAN                  FETCH
             (   4)                  (   7)
           0.00939683                15.1393
                0                       2
               |                  /----+----\
               20             1.81756        6849
             SORT             IXSCAN    TABLE: SCHEMA1
             (   5)           (   8)     TABLE1
           0.00777578         7.57544         Q4
                0                1
               |                |
               20              6849
             TBSCAN       INDEX: SCHEMA1
             (   6)        TABLE1_IX
           0.000171107          Q4
                0
               |
               20
        TABFNC: SYSIBM
             GENROW
               Q1
    

Local fix

  • Use Optimization guidelines:
    
    
    <OPTGUIDELINES>
    <IXSCAN TABLE='"T_00"' INDEX='"TABLE1_IX"' />
    </OPTGUIDELINES>
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * This APAR is a improvement on the Fix for APAR IY94892 POOR  *
    * PERFORMING QUERY ACCESS PLAN CHOSEN FOR INLIST-TO-JOIN       *
    * TRANSFORMATION IN DPF ENVIRONMENT                            *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 Version 9.7 and Fix Pack 5                    *
    ****************************************************************
    

Problem conclusion

  • Problem was first fixed in DB2 Version 9.7 and Fix Pack 5
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC77636

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2011-07-20

  • Closed date

    2011-12-10

  • Last modified date

    2011-12-10

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

    IV03465

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

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

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

Document Information

Modified date:
10 December 2011