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
MON$ATTACHMENTS

Information about active attachments to the database

MON$CALL_STACK

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

MON$CONTEXT_VARIABLES

Information about custom context variables

MON$DATABASE

Information about the database to which the CURRENT_CONNECTION is attached

MON$IO_STATS

Input/output statistics

MON$MEMORY_USAGE

Memory usage statistics

MON$RECORD_STATS

Record-level statistics

MON$STATEMENTS

Statements prepared for execution

MON$TRANSACTIONS

Started transactions

E.1. MON$ATTACHMENTS

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

Column NameData TypeDescription

MON$ATTACHMENT_ID

INTEGER

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(31)

The name of the user who is using this connection

MON$ROLE

CHAR(31)

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

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

  • Later attempts to use this connection (i.e., use its handle in API calls) will return errors

ExampleClosing all connections except for your own (current):

  |DELETE FROM MON$ATTACHMENTS
  |  WHERE MON$ATTACHMENT_ID <> CURRENT_CONNECTION