Appendix E. Monitoring Tables

The Firebird engine can monitor activities in a database and make them available for user queries via the monitoring tables. The definitions of these tables are always present in the database, all named with the prefix MON$. The tables are virtual: they are populated with data only at the moment when the user queries them. That is also one good reason why it is no use trying to create triggers for them!

The key notion in understanding the monitoring feature is an activity snapshot. The activity snapshot represents the current state of the database at the start of the transaction in which the monitoring table query runs. It delivers a lot of information about the database itself, active connections, users, transactions prepared, running queries and more.

The snapshot is created when any monitoring table is queried for the first time. It is preserved until the end of the current transaction to maintain a stable, consistent view for queries across multiple tables, such as a master-detail query. In other words, monitoring tables always behave as though they were in SNAPSHOT TABLE STABILITY (consistency) isolation, even if the current transaction is started with a lower isolation level.

To refresh the snapshot, the current transaction must be completed and the monitoring tables must be re-queried in a new transaction context.

Access Security

  • SYSDBA and the database owner have full access to all information available from the monitoring tables
  • Regular users can see information about their own connections; other connections are not visible to them

Warning

In a highly loaded environment, collecting information via the monitoring tables could have a negative impact on system performance.

List of Monitoring Tables

Section E.1, MON$ATTACHMENTS

Information about active attachments to the database

Section E.2, MON$CALL_STACK

Calls to the stack by active queries of stored procedures and triggers

Section E.3, MON$CONTEXT_VARIABLES

Information about custom context variables

Section E.4, MON$DATABASE

Information about the database to which the CURRENT_CONNECTION is attached

Section E.5, MON$IO_STATS

Input/output statistics

Section E.6, MON$MEMORY_USAGE

Memory usage statistics

Section E.7, MON$RECORD_STATS

Record-level statistics

Section E.8, MON$STATEMENTS

Statements prepared for execution

Section E.9, MON$TABLE_STATS

Table-level statistics

Section E.10, MON$TRANSACTIONS

Started transactions

E.1 MON$ATTACHMENTS

MON$ATTACHMENTS displays information about active attachments to the database.

Column Name Data Type Description
MON$ATTACHMENT_ID BIGINT Connection identifier
MON$SERVER_PID INTEGER Server process identifier
MON$STATE SMALLINT Connection state: 0 - idle 1 - active
MON$ATTACHMENT_NAME VARCHAR(255) Connection string — the file name and full path to the primary database file
MON$USER CHAR(63) The name of the user who is using this connection
MON$ROLE CHAR(63) The role name specified when the connection was established. If no role was specified when the connection was established, the field contains the text NONE
MON$REMOTE_PROTOCOL VARCHAR(10) Remote protocol name
MON$REMOTE_ADDRESS VARCHAR(255) Remote address (address and server name)
MON$REMOTE_PID INTEGER Remote client process identifier
MON$CHARACTER_SET_ID SMALLINT Connection character set identifier (see RDB$CHARACTER_SET in system table RDB$TYPES)
MON$TIMESTAMP TIMESTAMP The date and time when the connection was started
MON$GARBAGE_COLLECTION SMALLINT Garbage collection flag (as specified in the attachment’s DPB): 1=allowed, 0=not allowed
MON$REMOTE_PROCESS VARCHAR(255) The full file name and path to the executable file that established this connection
MON$STAT_ID INTEGER Statistics identifier
MON$CLIENT_VERSION VARCHAR(255) Client library version
MON$REMOTE_VERSION VARCHAR(255) Remote protocol version
MON$REMOTE_HOST VARCHAR(255) Name of the remote host
MON$REMOTE_OS_USER VARCHAR(255) Name of remote user
MON$AUTH_METHOD VARCHAR(255) Name of authentication plugin used to connect
MON$SYSTEM_FLAG SMALLINT Flag that indicates the type of connection: 0 - normal connection 1 - system connection
MON$IDLE_TIMEOUT INTEGER Connection-level idle timeout in seconds. When 0 is reported the database ConnectionIdleTimeout from databases.conf or firebird.conf applies.
MON$IDLE_TIMER TIMESTAMP WITH TIME ZONE Idle timer expiration time
MON$STATEMENT_TIMEOUT INTEGER Connection-level statement timeout in milliseconds. When 0 is reported the database StatementTimeout from databases.conf or firebird.conf applies.
MON$WIRE_COMPRESSED BOOLEAN Wire compression active (TRUE) or inactive (FALSE)
MON$WIRE_ENCRYPTED BOOLEAN Wire encryption active (TRUE) or inactive (FALSE)
MON$WIRE_CRYPT_PLUGIN VARCHAR(63) Name of the wire encryption plugin used

Retrieving information about client applications

1. SELECT MON$USER, MON$REMOTE_ADDRESS, MON$REMOTE_PID, MON$TIMESTAMP
2. FROM MON$ATTACHMENTS
3. WHERE MON$ATTACHMENT_ID <> CURRENT_CONNECTION

E.1.1 Using MON$ATTACHMENTS to Kill a Connection

Monitoring tables are read-only. However, the server has a built-in mechanism for deleting (and only deleting) records in the MON$ATTACHMENTS table, which makes it possible to close a connection to the database.

Notes

  • All the current activity in the connection being deleted is immediately stopped and all active transactions are rolled back
  • The closed connection will return an error with the isc_att_shutdown code to the application
  • Subsequent attempts to use this connection (i.e., use its handle in API calls) will return errors
  • Termination of system connections (MON$SYSTEM_FLAG = 1) is not possible. The server will skip system connections in a DELETE FROM MON$ATTACHMENTS.

Closing all connections except for your own (current):

1. DELETE FROM MON$ATTACHMENTS
2. WHERE MON$ATTACHMENT_ID <> CURRENT_CONNECTION