Firebird: Unterschied zwischen den Versionen
Aus Vosp.info
F (Diskussion | Beiträge) (→fbtrace / fbsvcmgr - zeige/capture/log sql queries) |
F (Diskussion | Beiträge) |
||
Zeile 156: | Zeile 156: | ||
<source lang=bash> | <source lang=bash> | ||
fbsvcmgr service_mgr action_trace_start trc_name "nctrace" trc_cfg /etc/firebird/3.0/fbtrace.conf | grep -vi 'TRACE\|FREE_STATEMENT\|security3.fdb\|CLOSE_CURSOR\|TCPv4:\|isql-fb\|Statement\|------------------------\|PLG\$\|RDB\$' >> /home/firebird/log/db.log | fbsvcmgr service_mgr action_trace_start trc_name "nctrace" trc_cfg /etc/firebird/3.0/fbtrace.conf | grep -vi 'TRACE\|FREE_STATEMENT\|security3.fdb\|CLOSE_CURSOR\|TCPv4:\|isql-fb\|Statement\|------------------------\|PLG\$\|RDB\$' >> /home/firebird/log/db.log | ||
+ | |||
+ | </source> | ||
+ | |||
+ | |||
+ | = sql = | ||
+ | |||
+ | == Common Table Expressions („WITH ... AS ... SELECT“) == | ||
+ | * https://firebirdsql.org/file/documentation/reference_manuals/html/de/fblangref25-dml-select-de.html#fblangref25-dml-select-cte-de | ||
+ | <source lang=sql> | ||
+ | with ... as ( | ||
+ | select ... | ||
+ | ) | ||
</source> | </source> |
Version vom 1. November 2019, 16:00 Uhr
https://firebirdsql.org/file/documentation/reference_manuals/html/de/
Inhaltsverzeichnis
info über datenbank
fbstat DATABASENAME -header
connect to database
# isql-fb -user sysdba -password masterkey localhost:employee
isql-fb -user sysdba -password masterkey "/path/to/db/file.fdb"
isql-fb -user sysdba -password masterkey 192.168.0.123/123:DB -i file.sql
Installation
Monitor Tabellen
- Systemtabelle Inhalte
- MON$IO_STATS Input/Output-Statistiken (beinhaltet Primärschlüssel MON$STAT_ID)
- MON$ATTACHMENTS Informationen über aktive Datenbankattachments
- MON$CALL_STACK Stackaufrufe von aktiven Abfragen durch Stored Procedures und Trigger
- MON$DATABASE Informationen über die Datenbank, welche durch die CURRENT_CONNECTION verbunden ist
- MON$STATEMENTS Zur Ausführung vorbereitete Statements
- MON$TRANSACTIONS Gestartete Transaktionen
- MON$CONTEXT_VARIABLES Informationen zu benutzerdefinierten Kontextvariablen
- MON$MEMORY_USAGE Statistiken über den Speicherverbrauch
- MON$RECORD_STATS Record-Level-Statistiken
- MON$IO_STATS Input/Output-Statistiken (beinhaltet Primärschlüssel MON$STAT_ID)
zeige aktuelle sql queries an die ausgeführt werden
SELECT *
FROM MON$IO_STATS AS ncstats
LEFT JOIN MON$STATEMENTS AS ncstatements ON ncstats.MON$STAT_ID = ncstatements.MON$STAT_ID
WHERE ncstatements.MON$SQL_TEXT IS NOT NULL
ORDER BY ncstats.MON$STAT_GROUP;
zeige angemeldete user an
-- Information about the database connections
SELECT
a.mon$attachment_id as Attachment_ID,
a.mon$server_pid as Server_PID,
case a.mon$state
when 1 then 'active'
when 0 then 'idle'
end as State,
a.mon$attachment_name as Database_Name,
a.mon$user as User_Name,
a.mon$role as Role_Name,
a.mon$remote_address as ip4,
a.mon$remote_os_user AS osuser,
a.mon$remote_host AS host,
a.mon$remote_pid as Remote_PID,
a.mon$timestamp as Established_At,
case a.mon$garbage_collection
when 1 then 'allowed'
when 0 then 'not allowed'
end as Garbage_Collection,
a.mon$remote_process as Remote_Process,
a.mon$stat_id as stat_id
FROM
mon$attachments a
- https://ib-aid.com/download/docs/firebird-language-reference-2.5/fblangref-appx05-monattach.html
- https://riptutorial.com/firebird/example/18210/get-information-about-attachments-on-the-connected-database
zeige andere Informationen
select * from MON$DATABASE ;
fbtrace / fbsvcmgr - zeige/capture/log sql queries
- /etc/firebird/3.0/fbtrace.conf
database
{
# Do we trace database events or not
enabled = true
# Operations log file name. For use by system audit trace only
log_filename = /home/firebird/log/db.log
#include_filter = select
#exclude_filter
# Put attach/detach log records
# log_connections = true
# Put transaction start/end records
# log_transactions = true
# Put sql statement prepare records
# log_statement_prepare = true
# Put sql statement free records
log_statement_free = true
# Put sql statement execution start records
# log_statement_start = true
# Put sql statement execution finish\fetch to eof records
# log_statement_finish = true
}
- firebird neu starten
# für änderungen in fbtrace.conf bedarf es nicht unbedingt ein neustart
systemctl restart firebird3.0.service
- service (daemon) starten
- hier wird jetzt auch erstmal alles ausgegeben
fbsvcmgr service_mgr action_trace_start trc_name "nctrace" trc_cfg fbtrace.conf
- fbsvcmgr: auf anderer konsole kann nun der service gestartet, pausiert und abgeschossen werden
# zeige laufende services
fbsvcmgr service_mgr action_trace_list
fbtracemgr -SE service_mgr -USER SYSDBA -LIST
# mach ne pause server 2
fbsvcmgr service_mgr action_trace_suspend trc_id 2
# mach weiter
fbsvcmgr service_mgr action_trace_resume trc_id 2
# beende serice
fbsvcmgr service_mgr action_trace_stop trc_id 2
- noch mal .. zeige nur die sql querrys
fbsvcmgr service_mgr action_trace_start trc_name "nctrace" trc_cfg /etc/firebird/3.0/fbtrace.conf | grep -vi 'TRACE\|FREE_STATEMENT\|security3.fdb\|CLOSE_CURSOR\|TCPv4:\|isql-fb\|Statement\|------------------------\|PLG\$\|RDB\$' >> /home/firebird/log/db.log
sql
Common Table Expressions („WITH ... AS ... SELECT“)
with ... as (
select ...
)