In the SQL Outline view, you can view EXPLAIN data that
shows you statistics about the access plans for the SQL statements
in your Java™ application. You
can spot the underperforming statements that, when revised, can improve
the performance of the application.
Before you begin
EXPLAIN is a monitoring tool in Visual Explain that produces
information about the estimated cost of executing an SQL SELECT, INSERT,
UPDATE, or DELETE statement.
Ensure the following project and
application requirements are met:
- At least one Java project
in your workspace must have support for pureQuery enabled. Your application
must have either of these characteristics:
- The application must contain at least one of the following objects:
- A call to one of the methods in the pureQuery Data interface
- An interface that declares at least one annotated method that
uses the pureQuery API
- A pureQueryXML file that contains captured SQL statements
- The application must use the Java Persistence
API or Hibernate, an object-relational mapping library for Java.
- Your project must be associated with one of the following databases:
- DB2® for Linux®, UNIX®,
and Windows®
- DB2 for z/OS®
- Informix® Dynamic Server
Note: You cannot run EXPLAIN for SQL
statement if the SQL statement contains variables that are declared
in your application. The statement can contain data server related
variables such as DB2 host variables.
About this task
Running EXPLAIN for SQL statements can be time consuming.
Instead of manually selecting SQL statements and populating their
EXPLAIN content, you can run background EXPLAIN.
Procedure
To find underperforming SQL statements with EXPLAIN:
- Use background EXPLAIN.
- Enable background EXPLAIN, the process that creates
EXPLAIN data on SQL statements in the SQL Outline view, and set options
to modify the behavior of this process.
- Select .
- In the Preferences window, select . To see explanations
of the controls that are on the page, click
in the lower-left corner of the window.
- In the SQL Outline view, click the arrow next to the
toggle button (
) and select EXPLAIN Columns.
The SQL Outline view gathers and displays EXPLAIN data
for the SQL statements that it lists.
- Manually select SQL statements and populate their EXPLAIN
content.
- In the SQL Outline view, click the arrow next to the
toggle button (
) and then select EXPLAIN
Columns.
- Select the statement or statements for which you want
to see EXPLAIN data. Use the Ctrl or Shift key to select
multiple statements.
- Right-click the statement or statements and then select Retrieve
EXPLAIN Data.
The SQL Outline view gathers and displays EXPLAIN data
for the selected SQL statements.
What to do next
To sort the results, click
to view the results in a table.
Then, click the head of the column by which you want to sort.
If
the EXPLAIN data make it clear that you should modify an SQL statement
so that it takes less time to run, you can tune the query. This feature
is available only for the following databases:
- DB2 for Linux, UNIX,
and Windows
- DB2 for z/OS