Commit or Rollback
In order to obtain the correct result, SQL Optimizer executes the SQL statement against the database. After executing an UPDATE, INSERT, and DELETE statement, SQL Optimizer prompts you to commit changes that the execution made to the database:
Select Yes to commit the changes or No to roll back the changes.
How to select the Optimal SQL Statement?
SQL Optimizer provides two different measurements of performance; run time for all records and the run time the first record. Both measurements will provide an indication on the fastest running SQL statement–but with two different aims. It is best to understand the intention of the SQL statement and in what type of source code it will be embedded. Generally, if the SQL statement is used for reports, then select the SQL statement with the run time for all records. If the SQL statement is used for on-line query, then select the SQL statement with the best run time for the first record.
We recommend the use of the actual run time of the SQL or index-set alternative rather than its DB2 LUW cost to determine which alternative is the most suitable. The DB2 LUW cost is just an estimate of the alternative's performance in relation to the other alternatives. Actual performance may be much different.
After selecting the alternative with the desired results, copy and paste it into the source program (or use the displayed index DDL to create indexes as needed). We recommend that the alternative be verified and tested before pasting it into the source program.
Caution: Do not select the SQL or index-set alternative with the lowest cost value without retrieving the run time because experience has shown that alternative with the lowest cost is not necessary the SQL with the best performance.
Note: If the aim of the SQL statement is unknown, then we recommend the use of run time for all records as a performance indication.
View Batch Run Details
The Batch Run Details window displays a summary of the run time information for all SQL and index-set alternatives executed. The Batch Run Details window will appear after the batch run process is completed unless the Show details on next batch run checkbox in the Batch Run Details window is unchecked.
To review the Batch Run Details window
After the Batch Run is complete and the SQL Optimizer window is active, select View | Show Batch Run Details.
Batch Run Criteria Window
Quest SQL Optimizer for IBM® DB2® LUW maximizes SQL performance by automating the manual, time-intensive and uncertain process of ensuring that SQL statements are performing as fast as possible. SQL Optimizer analyzes, rewrites, and evaluates SQL statements within multiple database objects, files, or SQL statements captured by the DB2 Event Monitor. With SQL Optimizer, you can analyze and optimize all your problem SQL from multiple sources. SQL Optimizer also provides you a complete index optimization and plan change analysis solution, from index recommendations to simulated index impact analysis, through comparison of multiple SQL access plans.
SQL Optimizer provides you with the following main modules.
SQL Optimizer (including SQL Rewrite and Generate Indexes functions)