Add SQL Wizard: SQL Information Page
View Add SQL Wizard - SQL Information Page
The SQL Information page of the Add SQL wizard is where to enter the SQL text and view various information about the SQL statement, such as the query and abstract plans.
Left pane
Enter the SQL text.
Right Pane
At the top of the right pane are buttons that display the query plan, the abstract plan, Trace On information, the SQL classification and connection information, and the DDL for creating any temporary tables used by the SQL statement.
Check SQL button
Checks the SQL syntax using the current database connection and retrieves the query plan, SQL type classification and other information relating to the SQL statement. To get the query plan most appropriate for your SQL statement, make sure that you have made the corresponding selections from the Settings tab before retrieving the checking the SQL.
Add SQL Wizard: Settings Page
The Settings page of the Add SQL wizard is used to set various parameters that affect the retrieval of the query plan for the SQL statement.
SQL Settings section
SQL for Cursor
Adaptive Server uses a difference 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 the SQL for Cursor checkbox. This enables cursor simulation when retrieving the query plan.
Use Default Plan
This option uses the BINARY data type when executing the Show Plan function for all variables in the SQL statement. This is useful when you want to quickly investigate the query plan of the original SQL statement without having to select the data type for each variable.
Abstract Plan section
Dump abstract plan
Specify whether to retrieve the abstract plan for the SQL statement whenever the query plan is retrieved. The abstract plan is not saved on the database until you deliberately save it. The default group names in Adaptive Server are: ap_stdout and ap_stdin. These groups are usually used by the Database Administrator to enable server-wide abstract plan capturing and retrieving.
Group name
Specify the abstract plan group name where the abstract plan for this SQL statement is saved.
Abstract Plan Manager button
Opens the Abstract Plan Manager window to view, create, and modify abstract plan group.
Database Settings section
Set Ansinull On
Specify the option with regards to comparison of NULL values.
Set Quoted_identifier On
Specify whether to allows the use of delimited identifier (" ") for table names.
Set Statistics Simulate On SAP ASE 15 or later)
Specify whether to load simulated statistics into the database. Simulated statistics can be generated using optdiag command and can be used to optimize SQL statements using the simulated statistics rather than the actual statistics.
dbcc traceon (3604, 302, 310) (sa_role privilege only)
Specify whether to retrieve the trace on information which displays the reasons why the Adaptive Server optimizer chooses to resolve the SQL statement in a particular way. This option is applicable only if you have sa_role privileges.
Refresh SQL Query Plan
If you feel the query plan stored with the SQL statement in the SQL Repository does not represent the current indication of performance due to database changes, you can retrieve the current query plan from the database.
To refresh the query plan in the SQL Repository
-
Select the SQL.
-
Select SQL | Refresh Plan or right-click the tree view and select Refresh Plan to open the Refresh Plan window.
-
The original query plan displays on the left pane while the newly retrieved query plan is displayed on the right pane for comparison. You can view and analyze the newly retrieved query plan, abstract plan, and SQL classification before saving the new query plan with the SQL statement.
-
Click Save to replace the original query plan with the newly retrieved one.
Modify SQL
You can only modify the SQL name and description of the SQL statement stored in the SQL Repository.
To modify SQL name or description
-
From the SQL Repository window, select the SQL you want to modify from the left pane.
-
Right-click and select Modify.
-
Modify the name and/or description. Click OK to save changes to the SQL Repository. The Last modified field will automatically be updated once the information is saved.
Note: SQL text cannot be modified. You must delete the current SQL and recreating a new one to change the SQL text.