Quest SQL Optimizer for IBM® DB2® LUWmaximizes 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)
The SQL Information pane shows types of information for the original SQL or the SQL associated with the currently selected alternative. Use the buttons across the top of the pane to toggle between the views.
Note: Different SQL Optimizer modules use the SQL Information pane. The buttons available in the pane depend on the module you are using.
Button |
Function |
Description |
---|---|---|
|
Show the corresponding virtual access plan. Notes:
| |
|
Virtual DB2 Optimized Text
|
If the SQL statement is valid, show the corresponding virtual DB2 optimized text. Note: No information displays if the SQL statement in invalid. |
|
Display the access plan. Notes:
Tip: Click | |
|
Display the SQL statement reconstructed by the DB2 LUW optimizer after it retrieves the access plan. Note: In the Scanner SQL Viewer, the optimized text for both the bound plan and the current plan display. | |
|
Information |
Show any of the following, depending on the specific function using this pane:
|
|
Scanner Temp Table (SQL Scanner only) |
Display the temporary table SQL statement assumed to create or modify the temporary table used on the scanned SQL statement if the SQL Scanner finds it in the source code. |
|
(SQL Scanner only) |
Display the date and time when the SQL statement was checked, its status and description, and the name of person who checked the SQL. |
Performance improvement can be obtained by running the SQL Rewrite function on an SQL statement. This function transforms the SQL syntax into equivalent statements with different access plans.
Use the following procedure to send the selected SQL statement to the SQL Optimizer window, where the SQL Rewrite function is automatically run on the statement.
To send a SQL statement to the SQL Rewrite function from another module
Select the single SQL statement you want to optimize.
Click . The selected SQL statement is copied to the SQL Optimizer window, and the rewrite process is automatically started.
When you copy a SQL statement from the Scanned SQL Viewer, the following can occur:
If the SQL statement uses temporary tables and the DDL for creating the table was found when scanning, you are prompted to create the temporary tables through the User-Defined Temp Table window.
If the DB2 LUW Special Registers are different from current settings, you are prompted to set the special registers through the Special Register Settings window.
If your current schema does not match the one previously used to retrieve the access plan of the SQL statement, a message alerts you to change the schema from the box at the left-corner of the main window.
Use this procedure to send the SQL statement selected in the current tool to a SQL Optimizer session, where the Generate Indexes function automatically runs on the statement. The Generate Indexes function creates virtual index sets that you can test on the SQL statement to determine whether the SQL's performance improves.
See Generate Index-Set Alternatives for more information about the Generate Indexes function.
To execute Generate Indexes on an SQL statement in another module
Select the SQL statement you want to optimize.
Click .
The selected SQL statement is copied to the SQL Optimizer window, where the Generate Indexes function automatically starts.
When copying a SQL statement from the Scanned SQL Viewer,
If the SQL statement uses temporary tables and the DDL for creating the table was found when scanning, you are prompted to create the temporary tables through the User-Defined Temp Table window.
If the DB2 LUW Special Registers are different from current settings, then you are prompted to set the special registers through the Special Register Settings window.
If your current schema does not match the one previously used to retrieve the access plan of the SQL statement, a message alerts you to change the schema from the box at the left-corner of the main window.
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Feedback Términos de uso Privacidad Cookie Preference Center