Viewing SQL or XQuery statement details and statistics - IBM DB2 9.7 for Linux, UNIX, and Windows

Use Visual Explain to view details and statistics for SQL or XQuery statements.

To start Visual Explain:

From the Control Center, right-click a database name and select either Show Explained Statements History or Explain Query.
From the Command Editor, execute an explainable statement on the Interactive page or the Script page.
From the Query Patroller, click Show Access Plan from either the Managed Queries Properties notebook or from the Historical Queries Properties notebook.

For more information on the windows described below, refer to the online help.

Table 1. Viewing SQL or XQuery statement text...
Tasks	Procedure
To view the text for an SQL or XQuery statement:	Use the Query Text window. To open this window, open either the Explainable Statements or the Explained Statements History window, or the Access Plan Graph window. Select Statement > Show Query Text.
To view the text for an explained SQL or XQuery statement that was rewritten by the optimizer:	Use the Optimized Query Text window. To open this window, open the Access Plan Graph window. Select Statement > Show Optimized Query Text.
To find a specific character string in the text of the window that you are using:	Use the Find window. To open this window, open either the Query Text or the Optimized Query Text window and click the Find push button.

Table 2. Viewing SQL or XQuery statement details...
Tasks	Procedure
To view the list of built-in functions and user-defined functions that are associated with the SQL or XQuery statement whose access plan is shown in the graph:	Use the Functions window. To open this window, open the Access Plan Graph window. Select Statement > Show Statistics > Functions.
To view the list of indexes that are defined on the table that is shown in the Table Statistics window:	Use the Indexes window. To open this window, open the Table Statistics window and click the Indexes push button.
To view details for an access plan graph that is selected in the Access Plan Graph window:	Use the Operator details window. To open this window, open the Access Plan Graph window. To see statistics on an operator in the graph, do one of the following: Highlight the operator node and select Node > Show Details. Double-click the operator node. Right-click the operator node and select Show Details from the pop-up menu.
To view the configuration parameters and bind options that affect the optimization process:	Use the Optimization Parameters window. Current values are shown, as well as values from the time of the explain. To open this window, open the Access Plan Graph window. Select Statement > Show Optimization Parameters.
To view a list of the column groups that are associated with the SQL or XQuery statement whose access plan is shown in the graph:	Use the Column Groups window. These columns belong to the table that is shown in the Table Statistics window. To open this window, open the Table Spaces window. Select one or more entries, and click OK.
To view a list of the referenced columns that are associated with the SQL or XQuery statement whose access plan is shown in the graph:	Use the Referenced Columns window. These columns belong to the table that is shown in the Table Statistics window. To open this window, open the Table Statistics window and click the Referenced Columns push button.
To view statistics for a column group that is referenced in a selected table:	Use the Referenced Column Groups window. To open this window, open the Referenced Columns window. Select one or more entries, and click OK.
To view the list of table spaces that are associated with the SQL or XQuery statement whose access plan is shown in the graph:	Use the Table Spaces window. To open this window, open the Access Plan Graph window. Select Statement > Show Statistics > Table Spaces.

Table 3. Viewing SQL or XQuery statement statistics...
Tasks	Procedure
To view column distribution values for the column that is shown in the Referenced Column Statistics window:	Use the Column Distribution Statistics window. To open this window, open the Referenced Column Statistics window and click the Column Distribution push button.
To view statistics for a built-in or user defined function that is associated with the explained SQL or XQuery statement:	Use the Function Statistics window. To open this window, open the Functions window. Select one or more entries, and click OK.
To view statistics for an index node selected in the Access Plan Graph or an index entry selected in the Indexes window:	Use the Index Statistics window. To open this window, open the Access Plan Graph window. Do one of the following: To see statistics on an Index node in the graph, do one of the following: Highlight the index node and select Node > Show Statistics. Double-click the index node. Right-click the index node and select Show Statistics from the pop-up menu. To see statistics on other indexes defined for a Table node in the graph, do one of the following: Highlight the table node and select Node > Show Statistics. Double-click the table node. Right-click the table node and select Show Statistics from the pop-up menu. The Table Statistics window opens. Click the Indexes push button to open the Indexes window. Select one or more entries in the Indexes window and click OK. An Index Statistics window opens for each entry that you select.
To view the estimated number of page fetches for each hypothetical number of buffer pages as an ordered set of pairs:	Use the Page Fetch Pairs window. The numbers model the number of I/Os required to read the data pages into buffer pools of various sizes. To open this window, open the Index Statistics window and click the Page Fetch Pairs push button.
To view statistics for a column that is referenced in a selected table:	Use the Referenced Column Statistics window. To open this window, open the Referenced Columns window. Select one or more entries, and click OK.
To view statistics for a table function node selected in the access plan graph:	Use the Table Function Statistics window. To open this window, open the Access Plan Graph window. To see statistics on a Table function node in the graph, do one of the following: Highlight the table function node and select Node > Show Statistics. Double-click the table function node. Right-click the table function node and select Show Statistics from the pop-up menu. Note: If the node is GENROW, only explained statistics are displayed; otherwise, both explained and current statistics are displayed.
To view statistics for the table node selected in the Access Plan Graph:	Use the Table Statistics window. To open this window, open the Access Plan Graph window. To see statistics on a Table node in the graph, do one of the following: Highlight the table node and select Node > Show Statistics. Double-click the table node. Right-click the table node and select Show Statistics from the pop-up menu.
To view statistics for a table space that is associated with an explained SQL or XQuery statement:	Use the Table Space Statistics window. To open this window, open the Table Spaces window. Select one or more entries, and click OK.

Note on CARD row under the Statistics column: In a partitioned database environment, the value in the Current column is computed based on all nodes, while the value in the Explained column is computed based on a particular node.