Topic
  • 3 replies
  • Latest Post - ‏2012-05-07T07:01:10Z by MatthiasNicola
SystemAdmin
SystemAdmin
230 Posts

Pinned topic Unused XML index

‏2012-05-04T07:50:11Z |
Hi,

I am having trouble to identify unused XML indexes. How to accomplish this?

Tnx.
Updated on 2012-05-07T07:01:10Z at 2012-05-07T07:01:10Z by MatthiasNicola
  • MatthiasNicola
    MatthiasNicola
    321 Posts

    Re: Unused XML index

    ‏2012-05-04T09:10:59Z  
    Here is an idea.

    First, get the list of all XML indexes defined in the database:

    
    SELECT SUBSTR(indschema,1,20) AS indschema, SUBSTR(indname,1,30) AS indname, SUBSTR(tabschema,1,30) AS tabschema, SUBSTR(tabname,1,30) AS tabname, indextype AS type FROM syscat.indexes WHERE indextype = 
    'XVIL';
    


    Second, collect all your XML queries (and updates). If you don't have all the XML queries readily available, you can capture them with a dynamic SQL snapshot or with the MON_GET_PKG_CACHE_STMT monitoring function.

    Third, explain all the XML queries. That is, create the explain tables with the EXPLAIN.DDL script in the sqllib/misc directory of your DB2 installation. Then "set current explain mode explain" and run all the queries through the DB2 Command Line Processor.

    Finally, use the following query to check which XML indexes are part of any execution plans:

    
    select OBJECT_NAME, OBJECT_TYPE from explain_object where OBJECT_TYPE = 
    'XI';
    


    Note that OBJECT_TYPE = 'XI' only selects XML indexes, not relational indexes. Any XML indexes that do not appear in the result of this query are not used by any of the queries that you explained.

    You can achieve the same effect in various ways, but this gives you the general idea.

    Does that help?

    Matthias

    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
  • SystemAdmin
    SystemAdmin
    230 Posts

    Re: Unused XML index

    ‏2012-05-04T09:54:20Z  
    Here is an idea.

    First, get the list of all XML indexes defined in the database:

    <pre class="jive-pre"> SELECT SUBSTR(indschema,1,20) AS indschema, SUBSTR(indname,1,30) AS indname, SUBSTR(tabschema,1,30) AS tabschema, SUBSTR(tabname,1,30) AS tabname, indextype AS type FROM syscat.indexes WHERE indextype = 'XVIL'; </pre>

    Second, collect all your XML queries (and updates). If you don't have all the XML queries readily available, you can capture them with a dynamic SQL snapshot or with the MON_GET_PKG_CACHE_STMT monitoring function.

    Third, explain all the XML queries. That is, create the explain tables with the EXPLAIN.DDL script in the sqllib/misc directory of your DB2 installation. Then "set current explain mode explain" and run all the queries through the DB2 Command Line Processor.

    Finally, use the following query to check which XML indexes are part of any execution plans:

    <pre class="jive-pre"> select OBJECT_NAME, OBJECT_TYPE from explain_object where OBJECT_TYPE = 'XI'; </pre>

    Note that OBJECT_TYPE = 'XI' only selects XML indexes, not relational indexes. Any XML indexes that do not appear in the result of this query are not used by any of the queries that you explained.

    You can achieve the same effect in various ways, but this gives you the general idea.

    Does that help?

    Matthias

    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
    Hi Matthias,

    You helped me a lot. I was able to identify all xml indexes and capture all XML queries (DB2 ESE 9.5.5), but execution plans part is what i am looking for..

    Have to mention that i am one of regular readers of your posts, they are dba's life savers :)

    Thank you!

    Ivan
    Belgrade, Serbia
  • MatthiasNicola
    MatthiasNicola
    321 Posts

    Re: Unused XML index

    ‏2012-05-07T07:01:10Z  
    Hi Matthias,

    You helped me a lot. I was able to identify all xml indexes and capture all XML queries (DB2 ESE 9.5.5), but execution plans part is what i am looking for..

    Have to mention that i am one of regular readers of your posts, they are dba's life savers :)

    Thank you!

    Ivan
    Belgrade, Serbia
    Hi Ivan,

    I'm glad to hear that these posts are helpful. Thanks for the positive feedback!

    Matthias

    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/