A number of monitoring tables provide a record of individual events rather than information about the current state. These tables are called “historical” because the events reported in them provide a record of the history of the server over a period of time. Adaptive Server maintains context information for each client connection that accesses the historical tables, and on each successive query on the table returns only rows that the client has not previously received. This “stateful” property of the historical monitoring tables is designed to maximize performance and to avoid duplicate rows when used to populate a repository for historical data.
The historical monitoring tables are:
monErrorLog
monDeadLock
monSysStatement
monSysSQLText
monSysPlanText
In monSysPlan Text and monSysSQLText,
the values of the columns BatchID, ContextID, ProcedureID,
and PlanID are modified effective Adaptive Server
versions 15.0.3 and later. For the changes in these columns, see the Reference
Manual: Tables.
You can identify historical tables from their monTables.Indicators column:
select TableName from master..monTables where Indicators & 1=1
The information returned from historical tables is stored in buffers, one for each historical monitoring table. The sizes of these buffers, which are specified by configuration parameters, affects the length of time data is stored. Use the sp_configure options to configure the size of the buffer and the information to be captured. The sp_configure options you use depend on which monitoring table you are configuring. For example, for the monSysPlanText table, configure:
plan text pipe max messages – the number of messages to be stored for the particular buffer.
plan text pipe active – to indicate whether Adaptive Server writes information to the buffer.
The following table lists the configuration parameters that affect the historical monitoring tables:
Monitoring table |
Configuration parameters |
|---|---|
monErrorLog |
errorlog pipe active errorlog pipe active messages |
monDeadLock |
deadlock pipe active deadlock pipe max messages |
monSysStatement |
statement pipe active statement pipe max messages |
monSysSQLText |
sql text pipe active sql text pipe max messages |
monSysPlanText |
plan text pipe active plan text pipe max messages |
monProcessSQLText and monSysSQLText |
max SQL text monitored |
Some historical tables require that you set other configuration
parameters in addition to those listed above. See Table 1-3.
The values of the max messages parameters determine the maximum number of messages per engine. Multiply this value by the number of configured engines to determine the total number of messages that can be stored.
Each message stored adds one row to the monitoring table. Once all entries in the buffer have been used, new messages overwrite old messages in the buffers, so only the most recent messages are returned.
See Chapter 5, “Setting Configuration Parameters” of the System Administration Guide, Volume 1 and “Configuring the monitoring tables to collect data” for more information about sp_configure.
Adaptive Server returns only the data that was added since the previous read, so you may get seemingly inconsistent result sets from queries that attempt to filter results using a where clause because:
A select from the monitoring table marks all previously unread messages in the table as having been read.
Adaptive Server language layer performs the filtering, so rows not contained in the result set of the query are still considered as “seen” by the connection.
In this example, the buffer associated with the monErrorLog table contains two messages:
select SPID, ErrorMessage from master..monErrorLog
SPID ErrorMessage ------ -------------------------------------- 20 An error from SPID 20 21 An error from SPID 21 (2 rows affected)
If you reconnect, the two messages are returned, but you receive the following messages when you filter the result set with a where clause:
select SPID, ErrorMessage from master..monErrorLog where SPID=20
SPID ErrorMessage ------ -------------------------------------- 20 An error from SPID 20 (1 row affected)
And:
select SPID, ErrorMessage from master..monErrorLog where SPID=21
SPID ErrorMessage ------ -------------------------------------- (0 rows affected)
Because the first query moved the client connection’s context to include both of the rows for spids 20 and 21, the second query does not return either of these rows. The filter specified in the first query required the server to retrieve and evaluate both rows to return the specified result. Adaptive Server marks the row for spid 21 as “read” even though it did not participate in a result set returned to the client connection.
Because
of the stateful nature of the historical monitoring tables, do not use
them for ad hoc queries. Instead, use a select * into or insert
into to save data into a repository or temporary
table and then perform analysis on the saved data.
