Chatta subito con l'assistenza
Chat con il supporto

SQL Optimizer for SAP ASE 3.8 - Release Notes

Adaptive Server Configuration Instructions

Adaptive Server Configuration

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

  1. Select Database | View Connection Information.
  2. 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

SELECT permission on database objects

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.

sa_role privilege

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

Create privileges

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

Adaptive Server monitoring tables

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.

  1. Create a loopback server entry either using ISQL or SQL Optimizer.

    In SQL Optimizer:

  1. Logon to Quest SQL Optimizer for SAP ASE with sa.

  2. In the bottom left corner of the window, set the database to Master.

  3. In the SQL Worksheet module, execute: sp_addserver 'loopback', null, server_name

In ISQL

  1. Logon to Adaptive Server with sa.

  2. Execute: sp_addserver 'loopback', null, serve_name go
  1. Execute the installmontables script located in the $SYBASE/ASE-version/scripts directory (%SYBASE%/ASE-version/scripts for NT).

isql -Usa -P<sa password> -Sserver_name
-n -i%SYBASE%/%SYBASE_ASE%/scripts/installmontables

mon_role privilege

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

Configuration Parameters

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

/* parameter to access syscomments system table to retrieve DDL for database objects. */
/*Access to the system catalog table, syscomments, is needed to view SQL text for procedures, triggers, views, default and rules objects. If you do not have access to the syscomments system table, a message <SQL Text unavailable> will be presented in the Text tab of the Database Explorer window, and you will be unable to scan database object in the SQL Scanner module. */

sp_configure "select on syscomments.text",1

go

Related Documents

The document was helpful.

Seleziona valutazione

I easily found the information I needed.

Seleziona valutazione