A number of monitoring tables provide the most recent historical monitoring information rather than information about the current state. Adaptive Server maintains context for each client that accesses these tables and only returns information that the client has not previously returned. These “stateful” historical monitoring tables were designed to maximize performance and to avoid duplicate rows when used to populate a repository for historical data.
The following stateful monitoring tables provide data that provides the most recent historical data rather than information about the current state of Adaptive Server:
monErrorLog
monDeadLock
monSysStatement
monSysSQLText
monSysPlanText
You can identify stateful historical tables from their monTables.Indicators column:
select TableName from monTables where Indicators & 1=1
The information returned from stateful historical tables is stored in buffers, one for each historical monitoring table. Use the sp_configure options to configure the size of the buffer and the information to be captured. Which sp_configure options you use depends on which monitoring table you are interested in configuring. For example, for the monSysPlanText table, you configure:
plan text pipe max messages – configures the number of messages to be stored for the particular buffer.
plan text pipe active – indicates whether Adaptive Server writes information to the buffer.
Each stored message stored contributes one row to the monitoring table. New messages overwrite old messages in the buffers, so only the most recent messages are returned.
See Chapter 5 of the System Administration Guide, “Setting Configuration Parameters” and “Adaptive Server configuration options” of this chapter for more information about using sp_configure.
Because Adaptive Server retains a context for every client connection and returns only the data that was added since the previous read, 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 always returns all previously unread messages.
The filtering is performed by the Adaptive Server language layer.
In the following example, the buffer associated with the monErrorLog table contains two messages:
select SPID, ErrorMessage from 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 monErrorLog where SPID=20
SPID ErrorMessage ------ -------------------------------------- 20 An error from SPID 20 (1 row affected)
And:
select SPID, ErrorMessage from monErrorLog where SPID=21
SPID ErrorMessage ------ -------------------------------------- (0 rows affected)
Even though you never see the row for SPID 21, a result set containing the SPID was passed to Adaptive Server’s language layer, which filtered the row before returning the result set to the client, and the message is marked as “read” for this connection.
Because of the stateful nature of these tables, you
should not use these tables for ad-hoc queries. Instead, you should
use a select * into or insert into to
populate a repository.
