Parameters Window
Data type and value of variables are entered using the Parameters window. If variables are embedded in the original SQL statement, the Parameters window will be displayed each time you launch the Run Result, Run for First Record, Run for All Records, and Batch Run functions. The values entered in the Parameters window have a direct result on the run time and run result retrieved.
The parameter values that you have previously entered can be saved by selecting the Enable SQL parameter history Options setting. If the same parameter is used in a SQL statement again, the value and the data type from the last time it was used is automatically entered for you.
To help identify the data type and value
-
Click Browse to expand the Parameters window. The bottom pane appears displaying object and data information corresponding to the selected objects from the SQL statement. Select the object to browsed using the Objects and Columns lists. If a column is selected (asterisk * selects all columns from the selected object), the corresponding data type is shown.
-
Click Load Data and highlight the cell with the variable value from the data grid. Then click Use Data & Data type or double-click the cell to copy the compatible data type and value to the parameter selected at the top of the window.
-
Click Use Data type to copy a compatible data type to the parameter selected at the top of the window.
-
After the data type and value of all the parameters have been selected, click OK.
Note: If the Parameters window appears when you do not have variables in the SQL statement, this may be caused by incorrect spelling of column or table names, pointing to the wrong schema, or the tables or columns do not exist in the database. If the tables or columns have been created since you opened SQL Optimizer, you may need to Synchronize the Data Dictionary.
Filter Database Objects
When you are selecting database objects from the list of all objects, you can filter the list in order to more quickly locate specify objects.
To filter database objects
-
Click
to open the Add Database Objects window.
-
In the Schema box, select the schema name where the objects reside.
-
In the Object type box, select the database object type.
-
In the Filter box, enter the filtering criteria using the % wildcard to replaces multiple characters. The filter is case sensitive, so you must match the upper or lowercase characters of the database object name.
-
If you would like to exclude the database objects that meet the filter criteria instead of including them, check the Exclude filter box.
-
Select the database objects from the list.
Access Plan
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)
Access Plan
The access plan is a combination of steps the DB2 LUW database optimizer chooses to execute a SQL statement. Each node represents how the database optimizer will physically retrieve rows of data from the database or how the data is prepared. By examining the access plan, you can see exactly how the database executes your SQL statement.
Right-click Menu
The Access Plan pane contains a right-click menu that allows you to perform the following functions:
|
Function |
Description |
|---|---|
|
|
Sends the access plan in its current view to the printer, to display on the screen (print preview), or to a file. |
|
Copy |
Copies the access plan to the clipboard. |
|
View Plan |
Changes how the access plan is displayed. |
|
Animated Plan Steps |
Highlights one-by-one the access plan steps. |
|
Plan Options |
Opens the Access Plan Options window so you can select the specific detailed information that is displayed in the access plan. You can also choose to display specific information in individual columns. |
|
Get Help on plan_step |
Displays the help text for the currently selected step in the access plan. |
|
Help on Access Plan |
Opens online help for the access plan. |