The right pane allows you to find out the average query time for the XML Query with index used in the previous page. You can also see the execution time of the same query with and without index in a graph.

If you are interested in looking at the explain plan and see how the index is being used, follow the steps below.

Explain Snapshot

Run the following steps on the DB2 CLP window to view the explain snapshot. If you have already created the explain tables, skip to step #5.

  1. On Windows change to directory DB2_install_directory\sqllib\misc
  2. On UNIX/Linux change to directory DB2 instance home directory/sqllib/misc
  3. Connect to the database you are working on.
  4. Run db2 -tvf EXPLAIN.DDL. This creates the explain tables.
  5. Run db2 SET CURRENT EXPLAIN MODE=YES
  6. Go the Ad Hoc SQL tab, Select Options. Note the Default Schema used for this workshop.
  7. If you have not completed the previous pages, copy the file TE_install_directory\tutorials\workshops\pureXML\HealthCare\SQL\PerformanceXQuery.sql to the current working directory.
  8. Edit the working copy of PerformanceXQuery.sql and replace '~~~SCHEMA~~~' with the Default Schema.
  9. Run db2 -tvf PerformanceXQuery.sql
  10. Run db2 SET CURRENT EXPLAIN MODE=NO
  11. Run db2exfmt -d DBNAME -1 -o explain_xmlquery_ind.out. Replace DBNAME with the database you are working on. The Explain Plan can be viewed in the output file explain_xmlquery_ind.out.


Click here to view the sample output of the Explain Access Plan : Sample Explain Access Plan

Total cost captured in access plan

Review the explain output and locate the Total Cost right above the access graph. The sample plan above shows a total cost of 37.86.

Operation in access plan

The access plan shows the XISCAN operator scanning the XML document with reference to the INDEX and a TBSCAN scanning the relational data and then joining the result set of two operations.

XISCAN operator

This operation is performed for a single query predicate. Its evaluation narrows down the qualifying return set of rows IDs and XML node IDs by range scanning any associated index over XML data before accessing the base table. The use of an index can improve the performance of a query because the compiler determines whether and how to use index information to complete the query. This typically results in:



Best Practice