Configuring the Sybase Monitoring and Diagnostic (MDA) tables
To set the size of tempdb for MDA
it seems to me that we say MDA when we mean the Sybase audit system
For best results, ensure the temporary database (tempdb) has more than 100MB of free space.
- Connect to the master database as the
sauser. - Check the size of
tempdb. - Allocate an appropriate amount of disk space to
tempdb. - Allocate disk space on the new device to
tempdb.
For example, execute the following command:
sp_helpdb
go
name db_size owner dbid created status
-------------- ------------- ----- ------ ------------------
--------------------------------------------------------------------
master 13.0 MB sa 1 Dec 07, 2007
mixed log and data
model 4.0 MB sa 3 Dec 07, 2007
mixed log and data
sybmgmtdb 75.0 MB sa 4 Dec 07, 2007
select into/bulkcopy/pllsort, trunc log on chkpt, mixed log and data
sybsystemdb 3.0 MB sa 31513 Dec 07, 2007
mixed log and data
sybsystemprocs 120.0 MB sa 31514 Dec 07, 2007
trunc log on chkpt, mixed log and data
tempdb 4.0 MB sa 2 Nov 11, 2008
select into/bulkcopy/pllsort, trunc log on chkpt, mixed log and data
text_db 5.5 MB sa 5 Dec 07, 2007
trunc log on chkpt, mixed log and data
For example, to allocate 500 MB, which is 256000 pages, execute the following command:
disk init name = "tempdb_data01",
physname = "/export/home/sybase/data/tempdb_data01.dat",
size = 256000
go
For example, execute the following command:
alter database tempdb on tempdb_data01 = 500
go
Extending database by 256000 pages (500.0 megabytes) on disk tempdb_data01
To configure the login trigger for session policies
Login triggers execute a specified stored procedure every time a user logs in.
- Drop any existing FortiDB_audit table.
- Create a table to store login information in.
- Create a procedure for the login trigger.
- Create the login trigger.
- Grant permission to execute login_proc to public.
For example, to drop the table FortiDB_audit, use the following command:
drop table master.dbo.FortiDB_audit
go
For example, to create the table FortiDB_audit in the master database, use the following command:
create table master.dbo.FortiDB_audit
(
spid smallint,
kpid int,
suid int,
loginname varchar(30),
dbusername varchar(30),
dbid smallint,
dbname varchar(30),
program_name varchar(30) null,
hostprocess varchar(30) null,
ipaddr varchar(64) null ,
loggedindatetime datetime
)
go
For example, to create the procedure login_proc, use the following script:
use master
go
drop procedure login_proc
go
create procedure login_proc
as
begin
insert into master.dbo.FortiDB_audit
select
S.spid,
S.kpid,
S.suid,
suser_name(),
user_name(),
S.dbid,
db_name(),
S.program_name,
S.hostprocess,
S.ipaddr,
S.loggedindatetime
from master.dbo.sysprocesses S
where S.spid = @@spid
end
go
For example, use the following command:
sp_logintrigger 'master.dbo.login_proc'
go
Global login trigger updated.
If sp_logintrigger is not installed, recreate the master database procedures.
For example, for UNIX, execute the following script:
isql -Usa -P<password> -i$SYBASE/ASE-15_0/scripts/installmaster
For Windows, execute the following script:
isql -Usa -P<password> -i$SYBASE/ASE-15_0/scripts/installmstr
If you need to drop the global trigger, execute:
sp_logintrigger 'drop'
go
For example:
grant execute on dbo.login_proc to public
go
To set the MDA parameters
- Configure MDA parameters.
- Restart the database.
- To configure the monitoring table to collect data, use the following command:
For example, for Linux, use the following commands (for Windows, enter "go" for each execution):
sp_configure "enable cis", 1
sp_addserver loopback, null, @@servername (not required for 15.0.2 or later)
set cis_rpc_handling on (not required for 15.0.2 or later)
exec loopback...sp_who (note: 3 dots)
sp_configure "errorlog pipe active", 1
sp_configure "deadlock pipe active", 1
sp_configure "wait event timing", 1
sp_configure "process wait events", 1
sp_configure "object lockwait timing", 1
go
For the monSysStatement table:
sp_configure "statement statistics active",1
sp_configure "statement pipe max messages",30000
sp_configure "per object statistics active",1
sp_configure "statement pipe active" ,1
go
For the monSysSQLText table:
sp_configure "max SQL text monitored" , 8192
sp_configure "SQL batch capture", 1
sp_configure "sql text pipe max messages", 30000
sp_configure "sql text pipe active", 1
go
Additional parameter values to set:
sp_configure "max memory" , 256000
sp_configure "event buffers per engine", 2000
sp_configure "plan text pipe max messages", 100
sp_configure "errorlog pipe max messages", 30000
sp_configure "deadlock pipe max messages", 100
go
sp_configure "enable monitoring" , 1
go
To connect to the Sybase database and clear the MDA buffer
Clear the MDA buffer only after the FortiDB database user has made an initial connection to the database.
- Connect to the Sybase database that you have configured for monitoring by FortiDB.
- To clear the MDA buffer, use the following commands:
See Adding (or modifying) a target connection.
select top 1 * from dbo.monSysSQLText
go
select top 1 * from dbo.monSysStatement
go