SQL for Cursor Checkbox
Adaptive Server uses a different query plan for a SQL statement that is embedded in a cursor declaration from the query plan when the SQL statement is not embedded in a cursor. This needs to be taken into account when retrieving the query plan or run time and also when generating SQL alternatives.
Therefore, if the original SQL statement comes from or will be embedded in a cursor declaration then you need to select SQL for Cursor in the SQL Editor pane of SQL Optimizer window. This enables cursor simulation when retrieving the query plan and run time information.
This checkbox is automatically selected in the SQL Editor pane when you use the Send to SQL Optimizer function from the SQL Scanner if the SQL was extracted from within a cursor declaration.
In Adaptive Server 15 or later, the Declare Cursor Setting window is also available to select specific cursor settings.
View Declare Cursor Settings Window
Cursor Arguments
The cursor arguments should match the settings used for the SQL statement in your application code.
| Cursor Type | Description |
|
SEMI-SENSITIVE |
Specify that the worktable which holds the result set is populated only as the rows are fetched. Therefore changes to the data that occur while the cursor is opened may be visible in the result set. |
|
INSENSITIVE |
Specify that the data is copied to a worktable when the cursor is open which makes the data insensitive to changes in the data that may occur while the cursor is opened. |
|
SCROLL |
Specify that the cursor is scrollable meaning that you can position the cursor anywhere in the cursor result set for as long as the cursor is open. All scrollable cursors are read only. |
|
NO SCROLL |
Specify that the rows are retrieved one row at a time. All update cursors are non-scrollable. |
Optimize Using Abstract Plan Only
This function is only available if you are connected to Adaptive Server 15 or later.
The Optimize using abstract plan function optimizes the original SQL with the objective of producing an optimal abstract plan. Therefore the transformed SQL statements are not shown. After optimization, the alternative abstract plans are shown with the original SQL statement in the left pane of the SQL Optimizer window. All abstract plans are compatible with the original SQL statement.
To optimize using only the abstract plan
-
In the SQL Optimizer window, enter the SQL statement in the SQL Editor pane of the SQL Optimizer.
-
Click
.
The time it takes to optimize is dependent on the complexity of the original SQL statement and the quota values set in the Preferences window.
To stop the optimization process
Click 
It may take a few seconds to terminate all processes.
Note: After the optimization, the Abstract Plan page remains blank since the abstract plan displays with the original SQL statement.
Insert User-Defined SQL
Once you have entered the original SQL statement in the SQL Optimizer module, you can add your own alternative SQL statement. You can do this either before or after you have optimized the original SQL statement. With this feature, you can benchmark test your own SQL alternatives with the alternatives created by the SQL Optimizer. Or, you can simply test your own alternatives against the original SQL statement.
To insert your own SQL alternative
-
Select the original SQL statement or the alternative SQL statement most like the one you want to insert.
-
Click
.
-
Create your SQL statement.
The query plan for your SQL statement is checked to see if it matches any of the query plans for the SQL alternatives or the original SQL. If it does, you will be prompted to decide whether to insert your alternative.
Note: The User-Defined SQL statements are not checked to see if they are semantically equivalent to the original SQL. When you include a User-Defined SQL in a Batch Run, be sure to check the Remarks column of Run Time pane to see if the record count for the User-Defined SQL matches the record count for the original SQL.
Open Optimized SQL
After you have saved the optimized SQL statements to a file, you can load them back through the SQL Optimizer window:
To load a saved optimized SQL statements and alternatives
-
Select SQL | Open Optimized SQL.
-
Select the file you want to load and click Open. This loads the saved SQL statements to the SQL Optimizer window. The Open Optimized SQL Details window displays the following:
Summary tab
Optimization Information
Displays the original connection and optimization settings information.
Last Saved Query plan Information
Displays the saved and current connection information and whether there are any changes in SQL structure and query plans.
User-Defined Temp Table Tab
If the SQL statement uses a temporary table, the User-Defined Temp Table tab displays in this window. It displays the DDL used to create the temporary table.
Changes Tab
If there are any changes to the SQL structure or the access plans, the Changes tab displays inI this window. It displays the SQL text along with the saved and current query plan.
If there are any changes in either SQL structure or query plans it is advisable that you refresh the query plans so that the reloaded image is a truth reflection of the current environment before any further testing is done. Click Refresh Plan from the Open Optimized SQL Details window. If there are changes in the query plan the corresponding SQL statements run time and statistics information are deleted. All invalid SQL statements are removed, except for the original SQL statement. You have an option to eliminate SQL statements with duplicate query plans.
After refresh, the Refresh Plan Details window can be displayed. This window displays the number of query plans refreshed, the total eliminated, and invalid plans.
The Open Optimized SQL Details and Refresh Plan Details windows can be reviewed at a later stage.
To view the details
Select View | Show Open Optimized SQL Details and View | Refresh Plan Details.