Providing monitoring information as tables enables you to use Transact-SQL to monitor Adaptive Server. For example, to identify the Transact-SQL statements that are currently consuming CPU, enter:
select s.SPID, s.CpuTime, t.LineNumber, t.SQLText from monProcessStatement s, monProcessSQLText t where s.SPID=t.SPID order by s.CpuTime, s.SPID, t.LineNumber desc
You can use this same query to find the SQL statements that are using the most physical I/O by substituting CpuTime for PhysicalReads.
The information in each monitoring table can be sorted, selected, joined, inserted into another table, and treated much the same as the information in a regular Adaptive Server table.
The tables are read-only because they are in-memory tables that are generated as they are queried.
Access to these tables is restricted to users with the mon_role role.The definitions for the monitoring tables have a similar syntax to CIS definitions, which allow remote procedures to be defined as local tables. For example, the following syntax is used to create the monNetworkIO table on a server named “loopback:
create existing table monNetworkIO ( PacketsSent int, PacketsReceived int, BytesSent int, BytesReceived int, ) external procedure at "loopback...$monNetworkIO"
See the Performance and Tuning Guide for more information.
