SQL optimization is a four-phase process:
Tip: The DB2 LUW Cost is only an estimate of the resources it takes to execute a SQL statement. It is essential to run Batch Execute on the alternatives to determine which statement actually performs the best.
Click to open a SQL Optimizer session.
On the SQL tab, enter the SQL statement you want to optimize.
Click . This step launches the SQL Rewrite process that automatically transforms the syntax of the SQL statement.
After the rewrite, the Rewrite Details dialog shows the total number of semantically equivalent SQL statements, the number of alternative statements with access plans different from your original statement, and a warning message if the number of SQL transformations reaches any of the optimization quotas set in the Options.
The SQL Optimizer window shows several tabs that provide information about the original SQL statement and its SQL alternatives. The SQL tab displays the SQL text and the access plan of the currently selected SQL alternative (or the original SQL statement). At the bottom of the tab is the Run Time pane. This pane lists the original SQL statement, the SQL alternatives, and the run-time statistics for all of these after they are executed. At this point, since you have not yet run Batch Execute, the pane shows displays only DB2 LUW cost value for the original SQL statement and each SQL alternative.
In the Run Time pane, select an SQL alternative.
Go to the Access Plan tab to view the alternative's access plan and statistics for the objects accessed by the SQL statement.
To see how the syntax of an alternative SQL statement differs from that of your original SQL, do the following:
Go to the Compare tab.
Your original SQL statement is displayed in one pane of the page and the alternative statement in the other pane. Blue highlighted shows differences in the SQL syntax.
Click , , or to customize what is displayed on the page.
Go to the Plan Statistics tab to compare the cost estimates between the original SQL access plan and the plan for each alternative.
In the SQL Optimizer window, click to generate virtual index sets. These index sets include those that the SQL Optimizer's Index Expert component recommends and those that DB2 recommends.
Note: If you are connected toDB2 LUW 8 or later, Index Expert generates its own virtual index sets and optionally includes indexes that DB2 recommends. If you are using DB2 LUW 7, only DB2-recommended index sets are retrieved.
The resulting virtual index-sets display as alternatives in the in the Run Time pane. Index-set alternatives recommended by Index Expert are labeled Setx; those recommended by DB2 are labeled DB2 LUW.
To prepare to execute the original SQL, SQL alternatives, and index-set alternatives, click .
In the Batch Run Criteria window, select the Selected SQL/Index Set tab.
Select which alternatives to execute. The blue checkmark in the left column indicates that the alternative is selected. By default, all alternatives are selected.
To unselect a statement, right-click the alternative and select the appropriate option.
Select the defaults on the remaining tabs in the Batch Criteria window, and click OK.
Note: If you need to make edits to any criteria, see Retrieve the Run Time for a Group of Alternatives for more information.
The Batch Run window opens enabling you to view the results as each statement executes.
When all the selected SQL statements have finished executing, the Batch Run Details window appears. Click OK.
In the Run Time pane on the SQL tab, review the columns that contain various types of execution time.
Once you have identified the most-efficient alternative SQL statement, you can any of the following:
Copy and paste it back in your application.
Save the alternative SQL statement in a text file either individually or with multiple SQL statements in the Optimized SQL report.
Save your SQL optimization results for later review. Select SQL | Saved Optimized SQL.
The SQL Formatter formats SQL statements, verifies syntax, and color-codes variables, invalid field or table names, optimization forces and comments. The use of indenting and highlighting gives SQL statements a standard of formatting that is easy to read.
After entering a SQL statement in the left pane of the window, click .
The formatted SQL statement displays in the right pane of the window. Comments, bind variables, optimizer forces, invalid column or table names, and variables are highlighted in different colors. If there is a syntax error, an error message from DB2 LUW is displayed.
To open the SQL Inspector window
To add an Inspector
After adding an Inspector in the SQL Inspector window, SQL statements and statistics from the monitoring tables are retrieved by executing the Inspect function. The Inspector must first be selected before the inspection can begin. Only one Inspector can be marked at a time.
To start the inspecting process
Select an Inspector job.
If the start time of the Inspector has not been reached, the SQL Inspector waits until it is time to begin. During and at the end of the inspecting, information is updated on the SQL Inspector window. Inspect terminates automatically once the end time is reached, except for ad-hoc inspecting. The ad-hoc inspecting process has no ending time so it must be terminated manually.
To abort the inspecting
Note: If you have already executed the Inspect function for an Inspector, re-executing the Inspect function will erase all existing information.
When your SQL statement uses a temporary table, you must create a temporary table before you use the SQL statement in several modules. When you exit from the program or connect to another session, all the temporary tables you create are dropped.
Click User-Defined Temp Table .
On the Creation tab, enter the statements for creating the temp table. You may include DECLARE GLOBAL TEMPORARY TABLE, INSERT, UPDATE, and DELETE statements.