`MON$STATEMENTS`

MON$STATEMENTS displays statements prepared for execution.

Column Name Data Type Description

Column Name	Data Type	Description
`MON$STATEMENT_ID`	`BIGINT`	Statement identifier
`MON$ATTACHMENT_ID`	`BIGINT`	Connection identifier
`MON$TRANSACTION_ID`	`BIGINT`	Transaction identifier
`MON$STATE`	`SMALLINT`	Statement state: `0` - idle `1` - active `2` - stalled
`MON$TIMESTAMP`	`TIMESTAMP WITH TIME ZONE`	The date and time when the statement was prepared
`MON$SQL_TEXT`	`BLOB TEXT`	Statement text in SQL
`MON$STAT_ID`	`INTEGER`	Statistics identifier
`MON$EXPLAINED_PLAN`	`BLOB TEXT`	Explained execution plan
`MON$STATEMENT_TIMEOUT`	`INTEGER`	Connection-level statement timeout in milliseconds.When `0` is reported the timeout of `MON$ATTACHMENT.MON$STATEMENT_TIMEOUT` for this connection applies.
`MON$STATEMENT_TIMER`	`TIMESTAMP WITH TIME ZONE`	Statement timer expiration time
`MON$COMPILED_STATEMENT_ID`	`BIGINT`	Compiled statement id

MON$STATEMENT_ID

BIGINT

Statement identifier

MON$ATTACHMENT_ID

BIGINT

Connection identifier

MON$TRANSACTION_ID

BIGINT

Transaction identifier

MON$STATE

SMALLINT

Statement state:

0 - idle
1 - active
2 - stalled

MON$TIMESTAMP

TIMESTAMP WITH TIME ZONE

The date and time when the statement was prepared

MON$SQL_TEXT

BLOB TEXT

Statement text in SQL

MON$STAT_ID

INTEGER

Statistics identifier

MON$EXPLAINED_PLAN

BLOB TEXT

Explained execution plan

MON$STATEMENT_TIMEOUT

INTEGER

Connection-level statement timeout in milliseconds.When 0 is reported the timeout of MON$ATTACHMENT.MON$STATEMENT_TIMEOUT for this connection applies.

MON$STATEMENT_TIMER

TIMESTAMP WITH TIME ZONE

Statement timer expiration time

MON$COMPILED_STATEMENT_ID

BIGINT

Compiled statement id

The STALLED state indicates that, at the time of the snapshot, the statement had an open cursor and was waiting for the client to resume fetching rows.

Display active queries, excluding those running in your connection

SELECT
  ATT.MON$USER,
  ATT.MON$REMOTE_ADDRESS,
  STMT.MON$SQL_TEXT,
  STMT.MON$TIMESTAMP
FROM MON$ATTACHMENTS ATT
JOIN MON$STATEMENTS STMT ON ATT.MON$ATTACHMENT_ID = STMT.MON$ATTACHMENT_ID
WHERE ATT.MON$ATTACHMENT_ID <> CURRENT_CONNECTION
AND STMT.MON$STATE = 1

Using `MON$STATEMENTS` to Cancel a Query

Monitoring tables are read-only.However, the server has a built-in mechanism for deleting (and only deleting) records in the MON$STATEMENTS table, which makes it possible to cancel a running query.

Note

Notes

If no statements are currently being executed in the connection, any attempt to cancel queries will not proceed
After a query is cancelled, calling execute/fetch API functions will return an error with the isc_cancelled code
Subsequent queries from this connection will proceed as normal
Cancellation of the statement does not occur synchronously, it only marks the request for cancellation, and the cancellation itself is done asynchronously by the server

Example

Cancelling all active queries for the specified connection:

DELETE FROM MON$STATEMENTS
  WHERE MON$ATTACHMENT_ID = 32

MON$STATEMENTS

MON$STATEMENTS

Using MON$STATEMENTS to Cancel a Query

docnext count = 0

`MON$STATEMENTS`

Using `MON$STATEMENTS` to Cancel a Query