DB2 Visual Explain is an easy-to-use, graphical Explain tool that provides detailed information about the access plan chosen by a relational data base management system (DBMS) optimizer to access data. In relational DBMSs, a user querying the database need not specify how to access the data, only what data is desired. As part of processing the user's query, the DBMS's optimizer generates many alternative plans for accessing the data requested, estimates the execution cost of each alternative, and picks the cheapest plan to execute. In order to see what plan the optimizer chose, many systems provide an EXPLAIN statement.
The DB2 Visual Explain originated as a prototype in the Starburst extensible relational prototype developed at IBM's Almaden Research Center, conceived and designed by Guy Lohman and implemented by George Lapis and Rumi Hiraga (visiting from the IBM Tokyo Research Laboratory).
The IBM Database 2 (DB2) Visual Explain for OS/2 and AIX is the product version of this prototype. DB2 Visual Explain supports Version 2 of DB2 for OS/2 and DB2 for AIX/6000.
Explain output, which reveals the access plan chosen by the optimizer, is traditionally textual or tabular in nature. For most SQL statements, this output is difficult to interpret.
DB2 Visual Explain presents this output in an easy-to-understand graphical format. Relationships between database objects (for example, tables and indices) are instantly clear, as are various operations (such as database scans) that the optimizer has chosen in order to access data. With this information, SQL statements and databases can be tuned for better performance and efficiency.
DB2 Visual Explain provides a wealth of optimizer information to help users write efficient SQL statements. This information includes I/O and CPU cost estimates for each operation, bind-time and current catalog statistics, predicate information, and cardinality. For example, an administrator or developer can easily identify the most expensive operation for a given SQL statement and focus on tuning that operation.
DB2 Visual Explain allows users to model the impact of various changes in the database environment on SQL statements. For example, a user can determine the estimated time needed to execute a query in a production environment with 1,000,000 rows -- all without having to add data to his test environment of 100 rows.
The plan shows one node for each operator of the plan. Data moves bottom up, from the leaves at the bottom of the graph, representing tables or indexes, to the RETURN node, which returns data to the user. The ISCAN nodes represent index scans on the ENROLLMENT table using the FKSTUDID index, on the STUDENTS table using the PKSTUDID index, and on the TEACHERS table using the PKINSTR index. The SCAN node represents a tablespace scan of the COURSES table. A FETCH node represents accessing data pages using the row identifier that was obtained from the index, in order to get additional columns. Nested-loop joins are represented by NLJN nodes, whereas sorted-merge joins are represented by MGJN nodes. The MATE operator stands for MATerialize Expressions, i.e. compute the SELECT-list expressions.
Users can get more information on any operator shown in the plan by clicking on the operator of interest. The pop-up screen will show the operands of that operator and the properties that are output by that operator (representing the net effect of all operations done on the data up to and including that operator), including the cumulative cost of that portion of the plan up to and including that operator. The example shown is for the ISCAN on index PKSTUDID. Note that it is applying three predicates as starting and/or stopping keys in the index scan: an equality predicate on the first column of the index (that was derived from the original IN predicate on Y.STUDID) and two inequality predicates that were derived from the original BETWEEN predicate on the second column of the index. By selectively clicking on just those operators of interest, the user can see detailed information without drowning in too much information or losing the overall idea of the plan.
In addition, the user may view certain properties of a plan for all operators "at a glance", by clicking on "View" and then "At a glance". The properties that can be seen in this way include the total cost, the estimated number of disk I/Os, and the estimated cardinality.
DB2 Visual Explain runs on both OS/2 and AIX and supports Version 2 of both the DB2/2 and DB2/6000 database products.
DB2 Visual Explain will be made available in October 1994 to a selected set of customers. These customers will participate in a beta evaluation and test, providing feedback on function and quality. When these customers confirm that IBM has achieved the functional and quality objectives, IBM will announce planned availability and detailed information regarding pricing and ordering.
