IBM Support

IT32664: XML INDEXES NOT USED IN DB2 11.5 EXPLAIN PLAN

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • Used in 10.5:
    
    ******************** EXPLAIN INSTANCE ********************
    
    DB2_VERSION:       10.05.A
    FORMATTED ON DB:   SAMPLE
    SOURCE_NAME:       SQLC2K26
    SOURCE_SCHEMA:     NULLID
    SOURCE_VERSION:
    EXPLAIN_TIME:      2020-04-21-15.51.15.269964
    EXPLAIN_REQUESTER: DB2INST1
    
    Original Statement:
    ------------------
    SELECT
      T1.ID,
      T1.DATA,
      T1.ID
    FROM
      db2inst1.TABLE1 T1
    WHERE
      (T1.ID = 'test')
    FOR FETCH ONLY
      OPTIMIZE FOR 1 ROW
    
    Optimized Statement:
    -------------------
    SELECT
      $INTERNAL_XMLTOSQL$(Q1.$C0) AS "ID",
      $INTERNAL_XMLTOSQL$(Q1.$C1) AS "DATA",
      $INTERNAL_XMLTOSQL$(Q1.$C0) AS "ID"
    FROM
      $INTERNAL_FOR$ ((TABLE ($INTERNAL_XPATH$
    ('($INTERNAL_XMLTOXML_NIEO$(Q2.XMLDATA))/estimate/{ID/./(.[((.
    >= $INTERNAL_SQLTOXML$(RTRIM(:L0))) and (. <
    $INTERNAL_FUNC$()))])(:-->$C0:),EventInfo/AssignmentEvent/(Inspe
    ctionDateTime)(:-->$C1:)}'))) AS Q1),
      db2inst1.TABLE2 AS Q2
    WHERE
      ($INTERNAL_XMLTOSQL$(Q1.$C0) = :L0)
    
    Access Plan:
    -----------
            Total Cost:             7.7278e+06
            Query Degree:           1
    
                     Rows
                    RETURN
                    (   1)
                     Cost
                      I/O
                      |
                  1.11147e-05
                    NLJOIN
                    (   2)
                  7.7278e+06
                  1.13528e+06
                   /--+--\
               167249  6.64561e-11
               FETCH     XSCAN
               (   3)    (   7)
               132621    45.4123
               131788       6
             /---+----\
         167249     1.19344e+06
         RIDSCN   TABLE: db2inst1
         (   4)    TABLE2
         1050.71        Q2
           123
           |
         167249
         SORT
         (   5)
         1050.71
           123
           |
         167249
         XISCAN
         (   6)
         941.878
           123
           |
       1.19344e+06
    XMLIN: db2inst1
    TABLE2_D4
           Q2
    
    
    But not in 11.5:
    
    ******************** EXPLAIN INSTANCE ********************
    
    DB2_VERSION:       11.05.0
    FORMATTED ON DB:   SAMPLE
    SOURCE_NAME:       SQLC2O29
    SOURCE_SCHEMA:     NULLID
    SOURCE_VERSION:
    EXPLAIN_TIME:      2020-04-21-15.45.24.238406
    EXPLAIN_REQUESTER: DB2INST1
    
    Original Statement:
    ------------------
    SELECT
      T1.ID,
      T1.DATA,
      T1.ID
    FROM
      db2inst1.TABLE1 T1
    WHERE
      (T1.ID = 'test')
    FOR FETCH ONLY
      OPTIMIZE FOR 1 ROW
    
    Optimized Statement:
    -------------------
    SELECT
      $INTERNAL_XMLTOSQL$(Q1.$C1) AS "ID",
      $INTERNAL_XMLTOSQL$(Q1.$C0) AS "DATA",
      $INTERNAL_XMLTOSQL$(Q1.$C1) AS "ID"
    FROM
      $INTERNAL_FOR$ ((TABLE ($INTERNAL_XPATH$
    ('($INTERNAL_XMLTOXML_NIEO$(Q2.XMLDATA))/estimate/{EventInfo/Ass
    ignmentEvent/(InspectionDateTime)(:-->$C0:),(ID)(:-->$C1:)}')))
    AS Q1),
      db2inst1.TABLE2 AS Q2
    WHERE
      ($INTERNAL_XMLTOSQL$(Q1.$C1) = :L0)
    
    Access Plan:
    -----------
            Total Cost:             3.24676e+07
            Query Degree:           1
    
    
              Rows
             RETURN
             (   1)
              Cost
               I/O
               |
             15204.1
             NLJOIN
             (   2)
           3.24676e+07
           4.6992e+06
             /-+--\
         844914  0.0179949
         TBSCAN   XSCAN
         (   3)   (   4)
         474844   37.8651
         474628      5
           |
         844914
    TABLE: db2inst1
      TABLE2
           Q2
    

Local fix

  • N/A
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * Db2 customer                                                 *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to 11.5.6                                            *
    ****************************************************************
    

Problem conclusion

  • Upgrade to 11.5.6
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT32664

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    B50

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2020-04-27

  • Closed date

    2021-03-25

  • Last modified date

    2021-06-11

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

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

    IT39352

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • RB50 PSN

       UP

  • RB50 PSY

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

Document Information

Modified date:
17 December 2021