Thiago, Firebird
as in other banks have internal tables for controls, through these internal tables you can get the information of connections, transactions and executed commands, etc.
Use the following select
to get the information you want and more.
select
A.MON$ATTACHMENT_ID as "Attachment ID",
A.MON$SERVER_PID as "Server PID",
A.MON$STATE as "State",
A.MON$ATTACHMENT_NAME as "Attachment Name",
A.MON$USER as "User",
A.MON$ROLE as "Role",
A.MON$REMOTE_PROTOCOL as "Remote Protocol",
A.MON$REMOTE_ADDRESS as "Remote Address",
A.MON$REMOTE_PID as "Remote PID",
CS.RDB$CHARACTER_SET_NAME as "Character Set",
A.MON$TIMESTAMP as "Established At",
A.MON$GARBAGE_COLLECTION as "Garbage Collection",
A.MON$REMOTE_PROCESS as "Remote Process",
A.MON$STAT_ID as "Statistics ID",
ST.MON$STATEMENT_ID as "Statement ID",
ST.MON$TRANSACTION_ID "Transaction ID",
case
when ST.MON$STATE = 0
then
'IDLE'
when ST.MON$STATE = 1
then
'ACTIVE'
end as "State",
ST.MON$TIMESTAMP "Started At",
ST.MON$SQL_TEXT "Statement Text",
R.MON$RECORD_SEQ_READS as "Non-indexed Reads",
R.MON$RECORD_IDX_READS as "Indexed Reads",
R.MON$RECORD_INSERTS as "Records Inserted",
R.MON$RECORD_UPDATES as "Records Updated",
R.MON$RECORD_DELETES as "Records Deleted",
R.MON$RECORD_BACKOUTS as "Records Backed Out",
R.MON$RECORD_PURGES as "Records Purged",
R.MON$RECORD_EXPUNGES as "Records Expunged",
IO.MON$PAGE_READS as "Page Reads",
IO.MON$PAGE_WRITES as "Page Writes",
IO.MON$PAGE_FETCHES as "Page Fetches",
IO.MON$PAGE_MARKS as "Page Marks"
from
MON$ATTACHMENTS A
join MON$STATEMENTS ST on ST.MON$ATTACHMENT_ID = A.MON$ATTACHMENT_ID
join RDB$CHARACTER_SETS CS on (A.MON$CHARACTER_SET_ID = CS.RDB$CHARACTER_SET_ID)
left join MON$RECORD_STATS R on (A.MON$STAT_ID = R.MON$STAT_ID)
left join MON$IO_STATS IO on (A.MON$STAT_ID = IO.MON$STAT_ID)
where
upper(ST.MON$SQL_TEXT) like upper(:PSQL) -- Comando SQL que deseja encontrar
and
ST.MON$STATE = 1 -- Para somente conexões ativas
In the parameter of select
you can enter a complete SQL or just a part.
Example: ST.MON$SQL_TEXT like '%USUARIO%'
or ST.MON$SQL_TEXT like 'select * from usuario where id =1'
.
The "Remote Address" field is the IP of the connection you ran.
Now if you are using the IBExpert
tool you can access the top menu under Services > Database Monitoring, there you can query this information and overturn a particular connection.