Evangelizing Mainframe
Print Email

The Good Old DB2 Visual Explain

The DB2 Visual Explain is being deprecated, and pretty soon it will no longer be an option. While it’s being replaced by better offerings, it remains one of the tools to assist in improving DB2 access performance.

Visual Explain is an easy-to-use graphical tool that provides detailed information about the access plan chosen by a relational database management system (RDBMS) optimizer to access data. In RDBMS, 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 RDBMS’s optimizer:

1. Generates many alternative plans for accessing the data requested
2. Estimates the execution cost of each alternative, and
3. Picks the cheapest plan to execute

In order to see which plan the optimizer chooses, many systems provide an Explain statement. Some of the highlights are:

Graphical presentation of the Explain output. The Explain output reveals the access plan chosen by the optimizer. It 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 (tables and indices, for example) 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.

Detailed optimizer information. 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.

What-if modelling capability. Explain allows users to model the impact of various changes in the database environment on SQL statements. Therefore, a user can determine the estimated time needed to execute a query in a production environment with 1 million rows—all without having to add data to his test environment of 100 rows, for instance.

In addition to these highlights, one can improve a query’s access to data by performing one or more of following tuning activities:

• Tune your table design and reorganizing table data.
• Create appropriate indexes.
• Use the RUNSTATS command to provide the optimizer with current statistics.
• Choose appropriate configuration parameters.
• Choose appropriate bind options.
• Design queries to retrieve only required data.
• Work with an access plan.
• Create explain snapshots.
• Use an access plan graph to improve an access plan.

Raghavendra Kulkarni is a mainframe technical specialist at the India-based IT-services company MindTree. He has more than nine years of experience in mainframe-related technologies and products. Currently engaged as a mainframe consultant, he is also a project manager and a pre-sales consultant. He can be reached at Raghavendra_Kulkarni2@mindtree.com.

Posted: 11/7/2011 2:51:04 PM by Raghavendra Kulkarni

Print Email

Join Now!