This article applies to all versions of DPA and Ignite 4.3 and later.
DPA creates a login with SA_ROLE and MON_ROLE privileges or uses an existing login with those privileges. DPA uses that login to monitor a Sybase instance and perform query plans for the SQL statements that it captures. DPA uses this login to view four MDA tables (monProcess, monProcessProcedures,monProcessSQLText, and monProcessLookup) and records information for each active session to a database repository. The query plans are performed on the monitored database, then stored for historical access in the repository.
DPA configures the Sybase instance with the following:
|Minimum Version |
|create login for DB user in "master" |
create monitoring user in "master"
sp_configure enable cis, 1
|Versions 12 to 15.0||sp_addserver loopback,null,@@servername|
|15.0 and below use loopback||create existing table monProcess|
|15.0 and below use loopback||create existing table monProcessProcedures (12.5.3+ has LineNumber column)|
|15.0 and below use loopback||create existing table monProcessLookup|
|15.0 and below use loopback||create existing table monProcessSQLText (12.5.3+ has ServerUserID column)|
|15.0 and below use loopback||create existing table monWaitEventInfo|
|grant select on monProcess to mon_role |
grant select on monProcessProcedures to mon_role
grant select on monProcessLookup to mon_role
grant select on monProcessSQLText to mon_role
grant select on monWaitEventInfo to mon_role
sp_configure enable monitoring,1
sp_configure wait event timing,1
sp_configure SQL batch capture,1
sp_configure enable cis,1
set cis_rpc_handling on
|if < 4000||sp_configure max SQL text monitored,4000|
The Sybase instance must be restarted if max SQL text monitored was not initially 4000.
DPA requires the SA_ROLE permission during the wizard setup for monitoring and for collections of historical query plans . If you do not need the historical query plan feature, you can remove the SA_ROLE once monitoring is set up. In some versions of Sybase, you may need to explicitly grant select access on the monProcess, monSysStatement, monTableColumns and monProcessSQLText tables.
To turn off historical query plan collections so DPA will not log errors: