IBM Support

IC80537: XQUERY WHICH CONTAINS XPATH EXTRACTION IN A FOR ... LET STATEMENT HAS A BAD PERFORMANCE

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • The performance of an XQUERY which contains path extraction in a
    'for loop' statement, such as 'for ... let' and 'for ...',
    has a bad performance in V97 compared to V91.  While the
    performance is bad in general, some queries may show worse
    performance than the others, depending on the amount of data to
    be extracted. The problem is caused by a bad plan being chosen
    by the optimizer.
    
    An example of such query is:
    -----
    xquery declare namespace xxxx="http://xxxx"; let $base :=
    db2-fn:xmlcolumn("xxxx")/ap:LimitData for $name in
    fn:distinct-values( $base[./xxxx:ScheduleDate <
    xs:string(fn:current-date())]/xxxx:ChargeList/xxxx:Charge/xxxx:U
    serSectionName ), $count in fn:count( $base[./xxxx:ScheduleDate
    < xs:string(fn:current-date()) and
    ./xxxx:ChargeList/xxxx:Charge/xxxx:UserSectionName=$name] )
    order by $name return <xxxx:LDOutline> <xxxx:Name>{ $name
    }</xxxx:Name> <xxxx:Count>{ $count } </xxxx:Count>
    </xxxx:LDOutline>@
    -----
    
    Another example of such query is :
    -----
    xquery declare namespace xxxx="http://xxxx"; let $base :=
    db2-fn:xmlcolumn("xxxx")/xxxx:LimitData for $name in
    fn:distinct-values( $base[./xxxx:ScheduleDate <
    xs:string(fn:current-date())]/xxxx:ChargeList/xxxx:Charge/xxxx:U
    serSectionName )
    order by $name return <xxxx:LDOutline> <xxxx:Name>{ $name }
    </xxxx:Name> <xxxx:Count> { fn:count( $base[./xxxx:ScheduleDate
    < xs:string(fn:current-date()) and ./xxxx:ChargeList/xxxx:
    Charge/xxxx:UserSectionName=$name] ) }
    </xxxx:Count> </xxxx:LDOutline> ;
    -----
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * DB2 UDB Version 9.7.                                         *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error description field for more information.            *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to Version 9.7 FixPack 6.                            *
    ****************************************************************
    

Problem conclusion

  • Problem was first fixed in DB2 UDB Version 9.7 FixPack 6.
    Upgrade to Version 9.7 FixPack 6.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC80537

  • 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-12-21

  • Closed date

    2012-10-20

  • Last modified date

    2012-10-20

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

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

    IC80587

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC80537

Modified date: 20 October 2012