48 DB2 9 for z/OS: New Tools for Query Optimization
Look at the information available for other nodes on the graph. If you click the node at the
bottom left that represents the DEPT table, the information in the Node Descriptor panel
changes, as shown in Figure 2-17.
Figure 2-17 Table information
The middle pane of the Node Descriptor panel shows the name of the table (DEPT) and the
creator ID (PAOLOR4) and correlation name used in the SQL statement (D). The number of
qualifying rows is the number expected to qualify after applying local predicates. This is the
same as the total number of rows in the table (14) in our example because the SQL statement
has a no WHERE clause. Notice the scroll bar at the right of the middle pane. This shows that
there is more information that you can view by scrolling down.
Chapter 2. Sample query optimization 49
Now look at the top pane in the Node Descriptor panel. It shows a directory structure with the
table at the top. Below it are folders containing information about columns, indexes, table
spaces, and partitions. If you want to see the column definitions for the table, click the + box to
the left of the name Columns. This opens up the columns folder so that you can look at the
column definitions, as shown in Figure 2-18.
Figure 2-18 Viewing the column definitions for a table
You can see the top pane now shows a list of the column names for table DEPT. The middle
pane is still showing information about the table because the table is still selected in the top
pane of the Node Descriptor panel. To see information about a specific column, select it in the
top pane and the middle pane will show the column information.
50 DB2 9 for z/OS: New Tools for Query Optimization
In Figure 2-19, you have selected the DEPTNO column in the top pane. If you look at the
middle pane, you can see detailed information about the column. Its cardinality is 14 (there
are 14 different values), the second highest value is “I22” and the second lowest is “B01”, and
it cannot take Null values. All this information is read from the DB2 Catalog. Some of it is data
placed there by the Runstats utility. The scroll bar to the right of the middle panel indicates
that more information is available, including the time stamp of the most recent Runstats.
Figure 2-19 Viewing column detail
The middle pane is currently showing a subset of the information about column DEPTNO. It is
showing us a Cost Estimation view that shows the information the optimizer is interested in
when costing the access path. There are other views available. If you click the pull-down box
at the top right of the middle pane (where it says “Views:”), you can see the other views that
are available for a column, as shown in Figure 2-20 on page 51. You can see that, for a
column, the available views are Cost Estimation, Space, and All. The views available vary,
depending on the object selected in the access graph and in the top pane of the Node
Descriptor panel. If you select the All view of the column, you can see the column definition -
CHAR(3).
Chapter 2. Sample query optimization 51
Figure 2-20 Changing the view of a column
Lets look at one other feature in the Node Descriptor panel. On the top left of the middle pane
is a tick box labelled Show attribute explanation. If you click that to activate it, then the bottom
pane shows a brief description of any information that you select in the middle pane. In
Figure 2-21 on page 52, you have activated this feature and then selected the Cardinality
information in the middle pane. The bottom pane now shows a brief description of the column
cardinality.
52 DB2 9 for z/OS: New Tools for Query Optimization
Figure 2-21 Showing attribute explanations
Lets look at the indexes defined on the table DEPT. If, in the top pane, you click the - box to
the left of Columns, it closes the list of column names. You then click the + box by the Indexes
folder and you see that there is one index defined on the DEPT table called XDEPT1. Click
the + box by the index to show a folder called Indexkeys. Click the + box by Indexkeys to see
the columns that make up the index key.

Get IBM DB2 9 for z/OS: New Tools for Query Optimization now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.