Convert Parameter Markers
Your SQL statement may have several question marks "?" as parameter markers.
To enable unique referencing of parameter markers
Click 
This will assign a unique number to all parameter markers within the SQL statement, for example:
Original SQL
SELECT *
FROM employee
WHERE emp_id = ?
OR emp_salary < ?
SELECT *
FROM employee
WHERE emp_id = ?1
OR emp_salary < ?2
SQL Information and Functions
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)
SQL Information Pane
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. |
(if available)to display detailed information for each row of the access plan.
Send SQL to SQL Optimizer
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.