Firebird: Unterschied zwischen den Versionen

Aus Vosp.info
Wechseln zu:Navigation, Suche
(→‎Monitor Tabellen)
(→‎connect to database)
 
(30 dazwischenliegende Versionen von 2 Benutzern werden nicht angezeigt)
Zeile 5: Zeile 5:
 
= info über datenbank  =  
 
= info über datenbank  =  
 
<source lang=bash>
 
<source lang=bash>
−
fbstat VARIO8 -header
+
fbstat DATABASENAME -header
 
</source>
 
</source>
−
 
  
 
= connect to database =
 
= connect to database =
Zeile 15: Zeile 14:
 
isql-fb -user sysdba -password masterkey "/path/to/db/file.fdb"
 
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
 
isql-fb -user sysdba -password masterkey 192.168.0.123/123:DB -i file.sql
 +
</source>
 +
 +
 +
== php 7.4 ==
 +
 +
<SOURCE LANG=bash>
 +
    $host = 'firebird:dbname=192.168.1.1/56300:DBNAME';
 +
    $password = 'password';
 +
    $username = 'user';
 +
    $firebird = new \PDO($host, $username, $password, [\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION]);
 +
 +
    $sql = 'SELECT FIRST 10 speichername FROM sel ';
 +
   
 +
    foreach ($firebird->query($sql) as $row) {
 +
      $string .= '"' . trim($row['SPEICHERNAME']) . '<br/>';
 +
    }
 
</source>
 
</source>
  
Zeile 26: Zeile 41:
 
* Systemtabelle Inhalte
 
* Systemtabelle Inhalte
 
** MON$IO_STATS Input/Output-Statistiken (beinhaltet Primärschlüssel MON$STAT_ID)
 
** MON$IO_STATS Input/Output-Statistiken (beinhaltet Primärschlüssel MON$STAT_ID)
−
** MON$ATTACHMENTS Informationen über aktive Datenbankattachments
+
*** MON$ATTACHMENTS Informationen über aktive Datenbankattachments
−
** MON$CALL_STACK Stackaufrufe von aktiven Abfragen durch Stored Procedures und Trigger
+
*** 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$CONTEXT_VARIABLES Informationen zu benutzerdefinierten Kontextvariablen
−
** MON$DATABASE Informationen über die Datenbank, welche durch die CURRENT_CONNECTION verbunden ist
 
 
** MON$MEMORY_USAGE Statistiken über den Speicherverbrauch
 
** MON$MEMORY_USAGE Statistiken über den Speicherverbrauch
 
** MON$RECORD_STATS Record-Level-Statistiken
 
** MON$RECORD_STATS Record-Level-Statistiken
−
** MON$STATEMENTS Zur Ausführung vorbereitete Statements
 
−
** MON$TRANSACTIONS Gestartete Transaktionen
 
  
  
 +
 +
== zeige aktuelle sql queries an die ausgeführt werden ==
 
<source lang=sql>
 
<source lang=sql>
 +
 
SELECT *  
 
SELECT *  
 
FROM MON$IO_STATS AS ncstats
 
FROM MON$IO_STATS AS ncstats
−
LEFT JOIN MON$STATEMENTS AS ncstatements ON ncstats.MON$STAT_ID = ncstatements.MON$STAT_ID  
+
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;
 
ORDER BY ncstats.MON$STAT_GROUP;
 +
</source>
 +
 +
== zeige angemeldete user an ==
 +
 +
<source lang=sql>
 +
-- 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
 +
 +
</source>
 +
 +
* 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 ==
 +
<source lang=sql>
 +
 +
select  * from MON$DATABASE  ;
 +
</source>
 +
 +
 +
= fbtrace / fbsvcmgr - zeige/capture/log sql queries =
 +
 +
* /etc/firebird/3.0/fbtrace.conf
 +
<source lang=bash>
 +
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
 +
}
 +
</source>
 +
 +
* firebird neu starten
 +
<source lang=bash>
 +
# für änderungen in fbtrace.conf bedarf es nicht unbedingt ein neustart
 +
systemctl restart firebird3.0.service
 +
</source>
 +
 +
* service (daemon) starten
 +
** hier wird jetzt auch erstmal alles ausgegeben
 +
<source lang=bash>
 +
fbsvcmgr service_mgr action_trace_start trc_name "nctrace" trc_cfg fbtrace.conf
 +
</source>
 +
 +
* fbsvcmgr: auf anderer konsole kann nun der service gestartet, pausiert und abgeschossen werden
 +
<source lang=bash>
 +
# 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
 +
</source>
 +
 +
 +
 +
 +
 +
 +
 +
 +
* noch mal .. zeige nur die sql querrys
 +
<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
 +
 +
</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>
 +
 +
 +
 +
 +
= Optimierungen bei Abfragen  =
 +
* aus
 +
<source lang=sql>
 +
in('1','2','3','5')
 +
</source>
 +
 +
wird
 +
<source lang=sql>
 +
between '1' and '3' or ...='8'
 +
</source>
 +
 +
 +
 +
= Beispiele  =
 +
== Subselect mit LIST() ==
 +
<source lang=sql>
 +
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
 
</source>
 
</source>

Aktuelle Version vom 18. Mai 2021, 11:59 Uhr

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


php 7.4

    $host = 'firebird:dbname=192.168.1.1/56300:DBNAME';
    $password = 'password';
    $username = 'user'; 
    $firebird = new \PDO($host, $username, $password, [\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION]);

    $sql = 'SELECT FIRST 10 speichername FROM sel ';
    
    foreach ($firebird->query($sql) as $row) {
       $string .= '"' . trim($row['SPEICHERNAME']) . '<br/>';
    }

Installation

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

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“)

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
Abgerufen von „https://vosp.info/index.php?title=Firebird&oldid=5878“