IBM Support

PK66539: POOR PERFORMANCE FROM XPATH WITH PARENT AXIS

A fix is available

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • POOR PERFORMANCE FROM XPATH WITH PARENT AXIS
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED: DB2 9 z/OS users who use XMLQuery/XMLTable/  *
    *                 XMLExists with multiple parent-axis steps    *
    *                 and those who fetches XMLTable rows using    *
    *                 a cursor.                                    *
    ****************************************************************
    * PROBLEM DESCRIPTION: XMLQuery/XMLTable/XMLExists (especially *
    *                      XMLTable) with multiple parent-axis     *
    *                      steps performs poorly in terms of CPU   *
    *                      time and storage usage.                 *
    *                                                              *
    *                      Also, user may encounter ABEND04E       *
    *                      RC=00E70005 at DSNXMLSM:M098 when       *
    *                      fetching XMLTable rows using a cursor.  *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    The performance of XMLQuery/XMLTable/XMLExists (especially
    XMLTable) with multiple parent-axis steps needs to be enhanced
    to avoid multiple scans and extensive usage of storage.
    
    For example:
    
    The following query uses parent-axis in column definitions of
    an XMLTable.
    
    SELECT X.*
       FROM ADMF001.STATE_XML,
            XMLTABLE('/doc/state/college/students/student/classes
                      /class'
            PASSING STATE_COLLEGE
            COLUMNS
              "STATEID"   INTEGER path '../../../../../@stateid',
              "COLLEGEID" BIGINT  path '../../../../@id',
              "TEACHERID" INTEGER path '@teacherid',
              "STUDENTID" INTEGER path '../../@studentid',
              "COURSEID"  SMALLINT path '@courseid',
              "GRADE"     DECIMAL path '@grade',
              "SEMESTER"  DATE path '@semester'
            ) AS X;
    
    In this query, there are multiple parent-axis steps in some
    column definitions. The ".." in the XPath expressions denote
    the parent axis. The query execution is very time and space
    consuming because it requires to perform multiple get-parent
    scans to retrieve the ancestor node and needs to do this
    repeatedly for all the rows in XMLTable.
    
    
    ABEND04E DSNXMLSM:M098 problem occurs because DB2 tries to reuse
    storage which should have been freed during a CLOSE CURSOR but
    but was not.
    
    For example, user may declare a cursor for the following query
    that uses XMLTable
    
    EXEC SQL DECLARE C1 CURSOR FOR
    SELECT X.*
       FROM XMLTABLE('/a/b'
              PASSING XMLPARSE('<a><b>1</b><b>2</b></a>')
            COLUMNS C VARCHAR(5) PATH '.') X;
    
    EXEC SQL OPEN C1;
    EXEC SQL FETCH C1 INTO :result;
    EXEC SQL CLOSE C1;
    
    EXEC SQL OPEN C1;
    EXEC SQL FETCH C1 INTO :result;
    
    If user first opens the cursor, fetches a row using it and
    closes the cursor, and then opens it the second time and tries
    to fetch row(s) again, this abend would occur due to the re-use
    of storage which should have been freed during CLOSE CURSOR.
    

Problem conclusion

  • This is an enabling apar of its preconditioning apar PK56337.
    DB2 is modified to implement an ancestor node cache and support
    finding ancestor node in one scan.
    
    Also, the DSNXMLSM:M098 abend for the attempt to reuse uncleared
    storage is fixed as well.
    

Temporary fix

Comments

APAR Information

  • APAR number

    PK66539

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    910

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2008-05-22

  • Closed date

    2009-03-02

  • Last modified date

    2009-04-02

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

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

    UK44488

Modules/Macros

  • DSNDSXT  DSNDXMLS DSNNOSMS DSNNQOP  DSNXMLSM
    DSNXMLSV DSNXQRWA DSNXQXPS DSNXRXML DSNXXLIB DSNX5TOP DSNX5XEE
    DSNX5XPD
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • R910 PSY UK44488

       UP09/03/17 P F903

Fix is available

  • Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG19M","label":"APARs - z\/OS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.1","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
02 April 2009