FirebirdSQL logo

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 WITH TIME ZONE

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

MON$SESSION_TIMEZONE

CHAR(63)

Name of the session time zone

MON$PARALLEL_WORKERS

INTEGER

Maximum number of parallel workers for this connection, 1 means no parallel workers.“Garbage Collector” and “Cache Writer” connections may report 0.

Retrieving information about client applications
SELECT MON$USER, MON$REMOTE_ADDRESS, MON$REMOTE_PID, MON$TIMESTAMP
FROM MON$ATTACHMENTS
WHERE MON$ATTACHMENT_ID <> CURRENT_CONNECTION

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.

Note
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):
DELETE FROM MON$ATTACHMENTS
WHERE MON$ATTACHMENT_ID <> CURRENT_CONNECTION

MON$TABLE_STATS

MON$TABLE_STATS reports table-level statistics.

Column Name Data Type Description

MON$STAT_ID

INTEGER

Statistics identifier

MON$STAT_GROUP

SMALLINT

Statistics group:

0 - database
1 - connection
2 - transaction
3 - statement
4 - call

MON$TABLE_NAME

CHAR(63)

Name of the table

MON$RECORD_STAT_ID

INTEGER

Link to MON$RECORD_STATS

Getting statistics at the record level for each table for the current connection
SELECT
  t.mon$table_name,
  r.mon$record_inserts,
  r.mon$record_updates,
  r.mon$record_deletes,
  r.mon$record_backouts,
  r.mon$record_purges,
  r.mon$record_expunges,
  ------------------------
  r.mon$record_seq_reads,
  r.mon$record_idx_reads,
  r.mon$record_rpt_reads,
  r.mon$backversion_reads,
  r.mon$fragment_reads,
  ------------------------
  r.mon$record_locks,
  r.mon$record_waits,
  r.mon$record_conflicts,
  ------------------------
  a.mon$stat_id
FROM mon$record_stats r
JOIN mon$table_stats t ON r.mon$stat_id = t.mon$record_stat_id
JOIN mon$attachments a ON t.mon$stat_id = a.mon$stat_id
WHERE a.mon$attachment_id = CURRENT_CONNECTION

MON$TRANSACTIONS

MON$TRANSACTIONS reports started transactions.

Column Name Data Type Description

MON$TRANSACTION_ID

BIGINT

Transaction identifier (number)

MON$ATTACHMENT_ID

BIGINT

Connection identifier

MON$STATE

SMALLINT

Transaction state:

0 - idle
1 - active

MON$TIMESTAMP

TIMESTAMP WITH TIME ZONE

The date and time when the transaction was started

MON$TOP_TRANSACTION

BIGINT

Top-level transaction identifier (number)

MON$OLDEST_TRANSACTION

BIGINT

Transaction ID of the oldest [interesting] transaction (OIT)

MON$OLDEST_ACTIVE

BIGINT

Transaction ID of the oldest active transaction (OAT)

MON$ISOLATION_MODE

SMALLINT

Isolation mode (level):

0 - consistency (snapshot table stability)
1 - concurrency (snapshot)
2 - read committed record version
3 - read committed no record version
4 - read committed read consistency

MON$LOCK_TIMEOUT

SMALLINT

Lock timeout:

-1 - wait forever
0 - no waiting
1 or greater - lock timeout in seconds

MON$READ_ONLY

SMALLINT

Flag indicating whether the transaction is read-only (value 1) or read-write (value 0)

MON$AUTO_COMMIT

SMALLINT

Flag indicating whether automatic commit is used for the transaction (value 1) or not (value 0)

MON$AUTO_UNDO

SMALLINT

Flag indicating whether the logging mechanism automatic undo is used for the transaction (value 1) or not (value 0)

MON$STAT_ID

INTEGER

Statistics identifier

Getting all connections that started Read Write transactions with isolation level above Read Commited
SELECT DISTINCT a. *
FROM mon$attachments a
JOIN mon$transactions t ON a.mon$attachment_id = t.mon$attachment_id
WHERE NOT (t.mon$read_only = 1 AND t.mon$isolation_mode >= 2)