Troubleshooting Firebird Database

Note:  I will be using the employee database(/opt/firebird/examples/empbuild/employee.fdb) which is available by-default with the Firebird 2.5.7  in this post.

How to connect to a Firebird Database and test (using command-line)

1. Go to /opt/firebird/bin directory.
2. Run ./isql (Command-line tool for Firebird)

udara@Razorx1:/opt/firebird/bin$ ./isql
Use CONNECT or CREATE DATABASE to specify a database
SQL>

3. Run connect "/opt/firebird/examples/empbuild/employee.fdb"

SQL> connect "/opt/firebird/examples/empbuild/employee.fdb"
CON>

4. Provide the user/password to connect, note the semicolon (;) at the end.

CON> user 'SYSDBA' password 'admin';
Database:  "/opt/firebird/examples/empbuild/employee.fdb", User: SYSDBA
SQL>

5. Run the intended query,

SQL> select * from employee;

 EMP_NO FIRST_NAME      LAST_NAME            PHONE_EXT                 HIRE_DATE DEPT_NO JOB_CODE JOB_GRADE JOB_COUNTRY                    SALARY FULL_NAME                            
======= =============== ==================== ========= ========================= ======= ======== ========= =============== ===================== =====================================
      2 Robert          Nelson               250       1988-12-28 00:00:00.0000  600     VP               2 USA                         105900.00 Nelson, Robert                       
      4 Bruce           Young                233       1988-12-28 00:00:00.0000  621     Eng              2 USA                          97500.00 Young, Bruce                         
      5 Kim             Lambert              22        1989-02-06 00:00:00.0000  130     Eng              2 USA                         102750.00 Lambert, Kim                         
      8 Leslie          Johnson              410       1989-04-05 00:00:00.0000  180     Mktg             3 USA                          64635.00 Johnson, Leslie                      
      9 Phil            Forest               229       1989-04-17 00:00:00.0000  622     Mngr             3 USA                          75060.00 Forest, Phil                         
     11 K. J.           Weston               34        1990-01-17 00:00:00.0000  130     SRep             4 USA                          86292.94 Weston, K. J.                        
     12 Terri           Lee                  256       1990-05-01 00:00:00.0000  000     Admin            4 USA                          53793.00 Lee, Terri   

How to enable trace configuration

1. Open /opt/firebird/fbtrace.conf in your favorite text editor and update relevant configuration.

How to trace the database

1. Go to /opt/firebird/bin directory.
2. Run ./fbtracemgr with proper parameter set.

udara@Razorx1:/opt/firebird/bin$ ./fbtracemgr -SE service_mgr -START -NAME fire -CONFIG ../fbtrace.conf -USER SYSDBA -PASS admin

Sample trace output for the select * from employee;query.

2017-02-22T11:58:23.2200 (1980:0x7f85df7ae678) TRACE_INIT
    SESSION_4 fire
   

2017-02-22T11:58:23.2210 (1980:0x7f85df7ae678) PREPARE_STATEMENT
    /opt/firebird/examples/empbuild/employee.fdb (ATT_63, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
    /opt/firebird/bin/isql:7445
        (TRA_264, READ_COMMITTED | NO_REC_VERSION | WAIT | READ_WRITE)

Statement 128:
-------------------------------------------------------------------------------
select * from employee
      0 ms

2017-02-22T11:58:23.2210 (1980:0x7f85df7ae678) FREE_STATEMENT
    /opt/firebird/examples/empbuild/employee.fdb (ATT_63, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
    /opt/firebird/bin/isql:7445

Statement 127:
-------------------------------------------------------------------------------
select * from employee

2017-02-22T11:58:23.2220 (1980:0x7f85df7ae678) EXECUTE_STATEMENT_START
    /opt/firebird/examples/empbuild/employee.fdb (ATT_63, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
    /opt/firebird/bin/isql:7445
        (TRA_263, CONCURRENCY | WAIT | READ_WRITE)

Statement 128:
-------------------------------------------------------------------------------
select * from employee

2017-02-22T11:58:23.2260 (1980:0x7f85df7ae678) CLOSE_CURSOR
    /opt/firebird/examples/empbuild/employee.fdb (ATT_63, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
    /opt/firebird/bin/isql:7445

Statement 128:
-------------------------------------------------------------------------------
select * from employee

Firebird trace utility

Firebird Trace utility.
Usage: fbtracemgr <action> [<parameters>]

Actions:
  -STA[RT]                              Start trace session
  -STO[P]                               Stop trace session
  -SU[SPEND]                            Suspend trace session
  -R[ESUME]                             Resume trace session
  -L[IST]                               List existing trace sessions

Action parameters:
  -N[AME]    <string>                   Session name
  -I[D]      <number>                   Session ID
  -C[ONFIG]  <string>                   Trace configuration file name

Connection parameters:
  -SE[RVICE]  <string>                  Service name
  -U[SER]     <string>                  User name
  -P[ASSWORD] <string>                  Password
  -FE[TCH]    <string>                  Fetch password from file
  -T[RUSTED]  <string>                  Force trusted authentication

Examples:
  fbtracemgr -SE remote_host:service_mgr -USER SYSDBA -PASS masterkey -LIST
  fbtracemgr -SE service_mgr -START -NAME my_trace -CONFIG my_cfg.txt
  fbtracemgr -SE service_mgr -SUSPEND -ID 2
  fbtracemgr -SE service_mgr -RESUME -ID 2
  fbtracemgr -SE service_mgr -STOP -ID 4