IBM Support

LI73583: VISUAL EXPLAIN WILL CRASH WHEN A TABLE WITH A XML INDEX WITH DETAILED STATISTICS IS EXPLAINED.

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • Visual Explain will crash when attempting to display the query
    graph that contains a table with a XML index that has
    PAGE_FETCH_PAIRS statistics.  The access plan does not need to
    use the XML index for the crash to occur.
    
    PAGE_FETCH_PAIRS is one of the extended index statistics,
    along with CLUSTERFACTOR that are gathered when the DETAILED
    option of the INDEX clause is used in the runstats command.
    

Local fix

  • Since the issue only occurs when extended statistics are
    gathered, the issue can be circumvented by not gathering
    extended statistics for the XML indexes.  Below is an
    example of using the runstats command to collect
    statistics in a way that avoids the Visual Explain crash:
    
    
    Consider a table s.t with two non-XML indexes s.idx1 and s.idx2,
    and two XML indexes s.xml_idx1 and s.xml_idx2.
    
    1)  Gather the table and basic index statistics for all indexes.
    
    runstats on table s.t and indexes all
    
    
    2) Gather the table, extended index statistics for non-XML
    indexes and basic index statistics for XML indexes.
    
    runstats on table s.t and detailed indexes s.idx1, s.idx2
    runstats on table s.t for indexes s.xml_idx1, s.xml_idx2
    
    The two runstats commands should run consecutively so that the
    statistics are gathered near the same time.
    

Problem summary

  • Users Affected:  Visual Explain Users.
    
    Problem Description: Visual Explain will crash when attempting
    to display the query graph that contains a table with a XML
    index that has PAGE_FETCH_PAIRS statistics.  The access plan
    does not need to use the XML index for the crash to occur.
    
    Problem Summary: Visual Explain will crash when attempting to
    display the query graph that contains a table with a XML index
    that has PAGE_FETCH_PAIRS statistics.  The access plan does not
    need to use the XML index for the crash to occur.
    

Problem conclusion

  • Problem was first fixed in Version 9.5 Fix Pack 3
    
    The Fix should be applied to the server.
    

Temporary fix

  • Since the issue only occurs when extended statistics are
    gathered, the issue can be circumvented by not gathering
    extended statistics for the XML indexes.  Below is an
    example of using the runstats command to collect
    statistics in a way that avoids the Visual Explain crash:
    
    
    Consider a table s.t with two non-XML indexes s.idx1 and s.idx2,
    and two XML indexes s.xml_idx1 and s.xml_idx2.
    
    1)  Gather the table and basic index statistics for all indexes.
    
    runstats on table s.t and indexes all
    
    
    2) Gather the table, extended index statistics for non-XML
    indexes and basic index statistics for XML indexes.
    
    runstats on table s.t and detailed indexes s.idx1, s.idx2
    runstats on table s.t for indexes s.xml_idx1, s.xml_idx2
    
    The two runstats commands should run consecutively so that the
    statistics are gathered near the same time.
    

Comments

APAR Information

  • APAR number

    LI73583

  • Reported component name

    DB2 UDE ESE LIN

  • Reported component ID

    5765F4104

  • Reported release

    950

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2008-07-11

  • Closed date

    2009-04-16

  • Last modified date

    2009-04-16

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

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

    LI73584

Fix information

  • Fixed component name

    DB2 UDE ESE LIN

  • Fixed component ID

    5765F4104

Applicable component levels

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

Document Information

Modified date:
15 October 2021