Debug SQL commands in Firebird is necessary for several purposes, the biggest one is perhaps to analyze the execution of queries at development time, for example, parameterized queries if analyzed from the client side do not have values, that is, their parameters are not revealed , but on the server side we can check all queries and the values they received.
The debug tool can also be used to create log files that will later be analyzed by third-party tools, it works like this, you determine a testing period, for example, 3 days and in that period you leave the trace on, after that you submit the log file to an analysis tool that will point out faults that have occurred or points of attention such as excessive waiting for the execution of a query.
First, we need to create a file with the debug definition we want, to have just the basics, let's create the fbtrace.conf file with the following content:
database mydatabase.fdb
{
# Do we trace database events or not
enabled = true
# Operations log file name. For use by system audit trace only
#log_filename = name
# Maximum size of log file (megabytes). Used by system audit trace for
# log's rotation : when current log file reached this limit it is renamed
# using current date and time and new log file is created. Value of zero
# means that the log file size is unlimited and rotation will never happen.
max_log_size = 0
# SQL query filters.
#
# Only SQL statements falling under given regular expression are reported
# in the log.
include_filter=%(SELECT|INSERT|UPDATE|DELETE)%
# SQL statements falling under given regular expression are NOT reported
# in the log.
#exclude_filter
# Put attach/detach log records
log_connections = true
# Trace only given connection id. If zero - trace all connections
#connection_id = 0
# 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
# Put record when stored procedure is start execution
log_procedure_start = true
# Put record when stored procedure is finish execution
log_procedure_finish = true
# Put record when stored function is start execution
log_function_start = true
# Put record when stored function is finish execution
log_function_finish = true
# Put trigger execute records
log_trigger_start = true
# Put trigger execute records
log_trigger_finish = true
# Put context variable change records (RDB$SET_CONTEXT)
#log_context = false
# Put errors happened
log_errors = true
# Put warnings
log_warnings = true
# Filters for errors and warnings GDS codes.
# Comma separated list of GDS codes values and\or names.
# For example: deadlock, req_sync, 335544321
# Include filter. If empty, trace all errors\warnings events.
# Else trace event if any code from list is found in status-vector.
#include_gds_codes
# Exclude filter. If empty, trace all errors\warnings events.
# Else trace event if no code from list is found in status-vector.
#exclude_gds_codes
# Put trace session init and finish messages
#log_initfini = true
# Sweep activity
log_sweep = true
# Print access path (plan) with sql statement
print_plan = true
# Use legacy (false) or explained (true) plan format
explain_plan = true
# Print detailed performance info when applicable
print_perf = true
# Put blr requests compile/execute records
#log_blr_requests = false
# Print blr requests or not
#print_blr = false
# Put dyn requests execute records
#log_dyn_requests = false
# Print dyn requests or not
#print_dyn = false
# Put xxx_finish record only if its timing exceeds this number of milliseconds
time_threshold =0 # default 100
# Maximum length of SQL string logged
# Beware when adjusting max_xxx parameters! Maximum length of log record
# for one event should never exceed 64K.
max_sql_length = 4096 # default 300
# Maximum length of blr request logged
#max_blr_length = 500
# Maximum length of dyn request logged
#max_dyn_length = 500
# Maximum length of individual string argument we log
max_arg_length = 4096 # default 80
# Maximum number of query arguments to put in log
max_arg_count = 0 # default 30
}The file "C:\temp\fbtrace.conf” you see above was based on the template “C:\Program Files\Firebird\Firebird_4_0\fbtrace.conf”. where do you see mydatabase.fdb is actually the name of the database you want to analyze or debug, if you don't specify a name, the trace will be done on all of them. You can also specify the database name by a regex based rule like:
- database = (%[\\/](e[[:DIGIT:]]{{2}}).fdb)
To proceed we will need to have the parameters at hand:
- Hostname or IP
- Door
- Username and Password
- trace configuration file
- Firebird installation location, if Windows.
If you don't know any of them, it will be impossible to proceed.
The next step requires us to open the Windows terminal with administrator permissions and run:
set path=%path%;"C:\Program Files\Firebird\Firebird_4_0"
The above command is for the utilities that come with FirebirdSQL to be visible to the terminal. You won't need it, but if you do, you can save on typing the full path to the utility name. After that, finally run the command that will allow you to debug the SQL commands that FirebirdSQL will process:
"C:\Program Files\Firebird\Firebird_4_0\fbtracemgr.exe" -se 127.0.0.1/3050:service_mgr -user SYSDBA -password masterkey -start -conf "C:\temp\fbtrace.conf"
Where:
- 127.0.0.1 is the hostname, in this case we use IP.
- 3050 is the port of the service used by FirebirdSQL
- SYSDBA is the username
- masterkey is the SYSDBA user password
- “C:\temp\fbtrace.conf” is the name of the file with the trace definitions.
Then, all executions that pass through the server and that are compatible with the definitions contained in fbtrace.conf will be displayed in the terminal. If you don't want to see the information in the terminal, but in another file, then use the stdout redirector:
"C:\Program Files\Firebird\Firebird_4_0\fbtracemgr.exe" -se 127.0.0.1/3050:service_mgr -user SYSDBA -password masterkey -start -conf "C:\temp\fbtrace.conf" <strong>>c:\temp\fbtrace.log</strong>
In the Linux environment, the process is basically the same, what only changes are the names of the directories where the utilities are installed.
Conclusion
Study the template file to learn how to make the fbtrace.conf file suitable for your needs. Sometimes you will want to modify it just so that all executions on the server are seen by you, but other times you may prefer to generate a log file that will later be submitted to a tool that will do the analysis and point out points of attention or failures, this is an example of the online tool from IBSurgeon.