I am having trouble to identify unused XML indexes. How to accomplish this?
This topic has been locked.
3 replies Latest Post - 2012-05-07T07:01:10Z by MatthiasNicola
Pinned topic Unused XML index
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2012-05-07T07:01:10Z at 2012-05-07T07:01:10Z by MatthiasNicola
MatthiasNicola 120000E28R302 PostsACCEPTED ANSWER
Re: Unused XML index2012-05-04T09:10:59Z in response to SystemAdminHere 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?
SystemAdmin 110000D4XK230 PostsACCEPTED ANSWER
Re: Unused XML index2012-05-04T09:54:20Z in response to MatthiasNicolaHi 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 :)
MatthiasNicola 120000E28R302 Posts