What should I do if Sybase ASE MDA tables cannot be installed?

Sybase ASE introduced a set of Monitoring tables since 12.5.0.3, called the MDA tables of the latter of montables. You can use the MDA table to monitor and diagnose Sybase ASE. The monitoring table stores snapshot snapshots for statistics and summary of ASE status. We can query these Monitoring tables like other system tables (such as sysobjects, sysindexes, and syscolumns.

In version 12. X, the MDA table is not installed by default. You need to install it manually. It is installed by default when you create a database server in ase15.x.

The following describes how to install and configure MDA in detail.

(1) Check parameter: enable CIS enabled? If this parameter is not enabled, enable this parameter.

Sp_configure "enable CIS"
Go
Parameter Name default memory used config value run value unit type
-------------------------------------
Enable CIS 1 0 1 1 switch static
(1 row affected)

(2) check whether the sysservers system table contains the loopback record. If not, manually add a remote server

(Note: A loopback server is added by default when you create a database server in ase12.5.4 or later versions .)

Use master
Go
Sp_helpserver
Go
Name network_name class status ID cost
----------------------------------------
Syb_backup test_bs asenterprise timeouts, no net password encryption, writable, RPC Security Model A 1 null
Syb_ejb ejbserver asejb external Engine Auto Start 2 null
Syb_jsagent test_jsagent asenterprise no timeouts, No. Net password encryption, writable, RPC Security Model A 4 1000
Syb_jstask test asenterprise timeouts, No. Net password encryption, writable, RPC Security Model A 6 1000
Test test local 0 0
Test_xp rpcserver no timeouts, no net password encryption, writable, RPC Security Model A 3 1000
(Return status = 0)
Sp_addserver loopback, null, servername
Go

-- Test this configuration: -- (NB: this step is no longer required in 15.0 ESD#2 or later) set cis_rpc_handling on go -- -- Alternatively, run: -- sp_configure 'cis rpc handling', 1 -- ...and disconnect/reconnect your session exec loopback...sp_who -- note: 3 dots! go 

(3) install the MDA system table

Run iSQL-USA-P in a Unix shell.Yourpassword-SYourservername-I $ Sybase/$ sybase_ase/scripts/installmontables-o $ Sybase/$ sybase_ase/scripts/instmontables_log.txt

Run iSQL-USA-P on the Windows command line.Yourpassword-SYourservername-I % Sybase % \ % sybase_ase % \ scripts \ installmontables-o % Sybase % \ % sybase_ase % \ scripts \ instmontables_log.txt

(Note: you do not need to install the MDA table in ase15.x)

(4) Assign the mon_role role to a logon that requires monitoring permissions.

Use master
Go
Grant role mon_role to sa
Go

use master go grant role mon_role to sa go 

(5) Check the basic MDA configuration information of the test.

1> select * from Master... monstate
2> go
Lockwaitthreshold lockwaits daysrunning checkpoints numdeadlocks diagnosticdumps connections maxrecovery startdate counterscleared
-----------------------------------------------------
5 0 0 0 0 9 5 Apr 24 2010 PM Apr 24 2010 pm
(1 row affected)
1>

(6) enable all monitoring configuration parameters

Sp_configure "SQL text pipe active", 1
Go
Sp_configure "SQL text pipe Max messages", 2000
Go
Sp_configure "plan text pipe active", 1
Go
Sp_configure "plan text pipe Max messages", 1000
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", 1000
Go
Sp_configure "deadlock pipe active", 1
Go
Sp_configure "deadlock pipe Max messages", 1000
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", 256
Go

Parameter: max SQL text monitored takes effect only after the ASE server is restarted.

(7) after the ASE is restarted, You can query montables to learn about the monitoring information of the ASE. For example, view the SQL statement executed by the current session.

1> sp_autoformat "monprocesssqltext"
2> go
Spid kpid serveruserid batchid linenumber sequenceinline sqltext
-------------------------------------------------------
---------------------------------------------------
31 2228258 1 35 1 1 select spid = right (space (80) + isnull (convert (varchar (80), spid), 'null'), 4 ), kpid = right (space (80) + isnull
(Convert (varchar (80), kpid), 'null'), 7), serveruserid = right (space (80) + isnull (convert (varchar (80), serveruserid ), 'null'), 12), batchid = right (space (80) + isnull
31 2228258 1 35 1 2 (convert (varchar (80), batchid), 'null'), 7), linenumber = right (space (80) + isnull (convert (varchar (80), line
Number), 'null'), 10), sequenceinline = right (space (80) + isnull (convert (varchar (80), sequenceinline), 'null'), 14 ), sqltext = substring (convert (varchar (255), sqlte
31 2228258 1 35 1 3 XT), 1,252) from monprocesssqltext
(3 rows affected)
(Return status = 0)

Note: in some rare cases in ase12.5.3 and later versions, configuring the "per object statistics active" parameter may cause time slice errors. This bug has been fixed in ase15.x.

In addition, the MDA monitoring function of ASE has a significant impact on the overall performance of the system. It is said that more than 20% of the ASE power consumption is required. Therefore, it is not recommended to configure the MDA monitoring table in the production environment. If the monitoring table is enabled, you must disable the monitoring parameters in time when the monitoring function is not used. Run:

Sp_configure "enable monitoring", 1
Go

Address: http://www.haogongju.net/art/96917