Click the button on the right to find out the average query time for the XML query used in the previous page.

This page also shows you how to obtain the access plan of the XML query executed in the previous page. From the access plan, you can identify any inefficient access and make some tuning accordingly.

Explain Snapshot

Run the following steps on the DB2 CLP window to view the explain snapshot :

  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. 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.out. Replace DBNAME with the database you are working on. The Explain Plan can be viewed in the output file explain_xmlquery.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 53055.1.

Operation in access plan
The access plan shows the XSCAN operator scanning the XML document and a TBSCAN scanning the relational data and then joining the result set of two operations.

XSCAN operator

This operator is used to navigate XML fragments to evaluate XPath expressions and to extract document fragments if needed. This operator processes node references passed by a nested-loop join operator (NLJOIN). It is not represented with a direct input in the access plan.