IBM Support

IT00248: QUERIES WITH XMLTABLE FUNCTIONS MIGHT RETURN INCORRECT RESULTS WHEN MORE THAN ONE EQUAL PREDICATE IS USED IN WHERE CLAUSE

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • If more than one XMLTABLE columns are selected and there is a
    predicate on the XMLTABLE in the where clause, then incorrect
    results might be returned.
    
    In following query columns NAME and AREA in the same XMLTABLE
    (T2 in this case) are filtered by equal predicates in
    "where" clause and some records might be omitted in the final
    results:
    
    select T2.NAME, T2.AREA from MOVE,
      XMLTABLE('$MOVEINFO/listing/locations/building' Columns
        "DEPT" VARCHAR(15) PATH '@dept')
      as T1,
      XMLTABLE('$MOVEINFO/listing/items/item' Columns
        "NAME" VARCHAR(20) PATH 'name',
        "AREA" VARCHAR(10) PATH 'area')
      as T2
      where T2.NAME = 'laser printer' and T2.AREA = 'common'
    
    As a best practice, it is better to not put the XMLTABLE
    predicate in "where" clause, as the original query does. Use the
    XPATH predicate instead. After pushing down the predicate to
    XPATH, the predication can be done when doing navigation, which
    generally improves performance.
    

Local fix

  • A) Pushing down predicates to XPATH is a recommended solution.
    sample predicates from one of where clause:
      where T2.NAME = 'laser printer'
    can be pushed down to XPath instead:
      XMLTable('$MOVEINFO/listing/items/item[name="laser printer"]'
    Same for 'AREA'.
    
    B) Disabling Multiple Extraction Points (MEP) using db2set
    variable:
    db2set DB2_COMPILER_XML_OVERRIDES=1
    db2stop force && db2start
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All users                                                    *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 version 10.1.0.4.                             *
    ****************************************************************
    

Problem conclusion

  • The problem is first fixed in DB2 version 10.1.0.4.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT00248

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2014-03-12

  • Closed date

    2014-05-08

  • Last modified date

    2014-05-26

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • RA10 PSN

       UP

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

Document Information

Modified date:
25 September 2021