Firebird

https://firebirdsql.org/file/documentation/reference_manuals/html/de/

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

Monitor Tabellen

https://firebirdsql.org/file/documentation/reference_manuals/html/de/fblangref25-appx05-montables-de.html

• 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

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

        #include_filter = select
        #exclude_filter

        # Put sql statement execution start records
         log_statement_start = true

        # Put record when stored procedure is start execution
        log_procedure_start = true

        # Put record when stored function is start execution
        log_function_start = true

        # Put trigger execute records
        log_trigger_start = true

        # Put errors happened
        log_errors = true

        # Put warnings
        log_warnings = 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“)

• https://firebirdsql.org/file/documentation/reference_manuals/html/de/fblangref25-dml-select-de.html#fblangref25-dml-select-cte-de

with ... as (
  select ...
)

Optimierungen bei Abfragen

• aus

in('1','2','3','5')

wird

between '1' and '3' or ...='8'

Beispiele

Subselect mit LIST()

select
       bek.ID
     , bek.BELEGNR
     , (
        SELECT LIST(coalesce (BEP.ID,'0') || ','|| coalesce (BEP.LIEFERTERMIN,'2000-01-01') || ','|| coalesce (BEP.VORGABE_KOMM_BEHAELTNIS,'0') || ','||coalesce  (BEP.FIXTERMIN,'2000-01-01'),';'  ) FROM BEP WHERE BEP.BELEGSCHLUESSEL = bek.BELEGSCHLUESSEL
       ) AS Positionen
from bek