SQL Optimizer for SAP ASE 3.9 - Installation Guide

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.

Related Topic

Add SQL to SQL Repository