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

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.1 and Fix Pack 11                   *
  ****************************************************************
  

Problem conclusion

• Problem was first fixed in DB2 Version 9.1 and Fix Pack 11
  

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:

  IC77634 IC77636

Fix information

• Fixed component name

  DB2 UDB ESE HP-

• Fixed component ID

  5765F4103

Applicable component levels

• R910 PSN

     UP