Adaptive Server has database privileges that either allow or restrict the access and authority of each user. Some of the modules in SQL Optimizer need specific privileges. When you logon to SQL Optimizer, it checks to see if the logon satisfies the privileges. If your user account does not have a specific privilege, you can still use all the other modules in the program.
To see if your logon satisfies the privilege requirements for each module
Select Database | View Connection Information.
Adaptive Server has database privileges that restrict the access and authority of each user. Although it is not required that the user logon have all of these privileges, certain functionality will not be available to the user without specific privileges.
When you logon to Quest SQL Optimizer for SAP ASE, it checks to see if the logon satisfies the privileges below.
To see if your logon satisfies the privilege requirements for each module
Determine which of the following database setup and user privileges are needed for your environment and follow the steps under the following sections.
SELECT Permission on database objects
sa_role
CREATE PROCEDURE
Adaptive Server Monitor Tables
mon_role
Adaptive Server Configuration Parameters
If the user is not the owner of the database objects, the user logon account must have the SELECT permission on the objects. If the user logon account does not have access to database objects in Adaptive Server, then these objects cannot be used or viewed in Quest SQL Optimizer for SAP ASE.
The sa_role privilege is needed for a few functions in Quest SQL Optimizer for SAP ASE. If the logon does not have this privilege, most of the functionality of Quest SQL Optimizer for SAP ASE is still available.
Assign 'sa_role' to the logins needing the following functionality:
Use the SQL Collector for Monitor Server module.
Abort Run Result function for SQL statements that return data.
Retrieve Trace On (dbcc) information
Grant sa_role to the logon using the following command in the SQL Worksheet or ISQL:
sp_role 'grant', sa_role, logon_name
The CREATE PROCEDURE privilege is required to optimize a SQL statement that runs inside a cursor. A procedure is created when the SQL for Cursor checkbox is selected in the SQL Optimizer window (in the SQL Optimizer module) or when the SQL Scanner finds a SQL statement that is executed within a cursor declaration. The stored procedure is created to retrieve the query plan and run time. The stored procedure is executed and then dropped.
If the user logon does not have sa_role which includes this privilege, then grant this privilege to the logon using the following command in the SQL Worksheet or ISQL:
grant create procedure to logon_name
The SQL Inspector and Performance Monitor require installation of the Adaptive Server monitoring table. Later versions of Adaptive Server create the monitoring tables are by default when you install Adaptive Server. So depending on the version of Adaptive Server you are running, you may find these tables already installed.
The tables are creating using the installmontables script which is included in the Adaptive Server install and is located in the $SYBASE/ASE-version/scripts directory (%SYBASE%/ASE-version/scripts for NT). This script requires that a server named "loopback" be included in sysservers.
If the monitoring tables are not installed yet in your Adaptive Server, follow these steps to for installing the tables.
Create a loopback server entry either using ISQL or SQL Optimizer.
In SQL Optimizer:
Logon to Quest SQL Optimizer for SAP ASE with sa.
In the bottom left corner of the window, set the database to Master.
In the SQL Worksheet module, execute: sp_addserver 'loopback', null, server_name
In ISQL
Logon to Adaptive Server with sa.
- Execute: sp_addserver 'loopback', null, serve_name go
isql -Usa -P<sa password> -Sserver_name
-n -i%SYBASE%/%SYBASE_ASE%/scripts/installmontables
The mon_role privilege is needed to run the SQL Inspector and Performance Monitor modules. The sa logon does not have this role by default, so it needs to be added to the sa logon and any other user logon. Assign 'mon_role' to the logins using the SQL Worksheet module or ISQL with this command.
sp_role 'grant', mon_role, logon_name
The following modules require specific settings for Adaptive Server configuration parameters. (You can copy, paste and execute the following script into the SQL Worksheet module).
/* parameters for SQL Inspector and Performance Monitor modules using monitoring tables */
/* requires Adaptive Server 12.5.0.3 and later */
sp_configure "enable monitoring",1
go
sp_configure "sql text pipe active",1
go
sp_configure "sql text pipe max messages",1024
go
sp_configure "plan text pipe active",1
go
sp_configure "plan text pipe max messages",5000
go
sp_configure "statement pipe active",1
go
sp_configure "statement pipe max messages",5000
go
sp_configure "errorlog pipe active",1
go
sp_configure "errorlog pipe max messages",1024
go
sp_configure "deadlock pipe active",1
go
sp_configure "deadlock pipe max messages",1024
go
sp_configure "wait event timing",1
go
sp_configure "process wait events",1
go
sp_configure "object lockwait timing",1
go
sp_configure "SQL batch capture",1
go
sp_configure "statement statistics active",1
go
sp_configure "per object statistics active",1
go
sp_configure "max SQL text monitored",4096
go
/* parameters for SQL Inspector module using the QP Metrics */
/* requires Adaptive Server 15.0 and later */
sp_configure 'enable metrics capture',1
go
/* parameters for Abstract Plan Manager module */
/* requires Adaptive Server 12.0 and later */
sp_configure "abstract plan cache",0
go
sp_configure "abstract plan replace",0
go
/* parameters for retrieving Estimated I/O Cost for query plans */
sp_configure "allow resource limits",1
go
/* parameters for SQL Collector for Monitor Server module*/
sp_configure "event buffers per engine",2000
go
sp_configure "max SQL text monitored",4096
go
sp_configure "select on syscomments.text",1
go
SQL Optimizer requires an authorization key to use the program. When it is first installed it has a trial key that is good for thirty days.
To enter another trial key or to enter the production key
Select Help | Register.
Enter the key.
When you enter a production key after purchasing the product, you must also enter the Site Message information.
Click OK.
When SQL Optimizer starts, a User Logon dialog field displays for you to connect to the Adaptive Server database.
| Item | Description |
| Login name | Enter the login name required to connect to the database. |
|
Password |
Enter the password associated with the login name. |
|
Server name |
Enter the Adaptive Server name as defined in the client configuration. |
The Server list contains the database servers taken from the SQL.INI file.
| Item | Description |
| Host name | Enter the name of the computer where the database resides. |
|
Port number |
Enter the port number that was assigned to Adaptive Server. |
After selecting and entering the connection information, click Connect.
The Loading Data Dictionary window may be shown while connecting to the database to indicate that information from the data dictionary is being retrieved and loaded in the memory of the computer. This information is used when the SQL statement is parsed for functions such as scanning, optimization, and index generation. Depending on the number of objects in your database, the loading of all the information from the data dictionary may take considerable time. Therefore you may want to use the option in the General Preferences with does not load the data dictionary when the connection to the database is made, but does load the specific information that is needed when SQL statements are parsed. This saves time when the connection is made and adds a little more time to the parsing process.
Note: The first time the program is launched, the Language dialog field displays a list of available interface and character set languages. The character set allows you to view data in the chosen language.
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Terms of Use Privacy Cookie Preference Center
