The Jaybird JDBC driver library for the Firebird SQL server includes support for the Firebird “Trace and Audit Services” API. Let’s write a small Java application as a starting point.

Our example will direct all output to System.out, configure and start start a trace session, waits for infinite time, and finally close the trace session.

The full source code is available on GitHub: https://github.com/michaelJustin/firebird-jaybird-trace

The Main class

Here is the main class code. It uses a single-thread ExecutorService to launch our SystemOutTraceNotifier, waits for a keypress, and finally terminates our SystemOutTraceNotifier instance by shutting down the executor.

public class Main {

  private static final String DB = "C:\\Program Files\\Firebird\\Firebird_2_5\\examples\\empbuild\\EMPLOYEE.FDB";

  public static final void main(String... args) throws IOException {

    SystemOutTraceNotifier notifier = new SystemOutTraceNotifier(DB, "SYSDBA", "masterkey");

    ExecutorService executorService = Executors.newSingleThreadExecutor();
    executorService.submit(notifier);

    System.out.println("[*] ... hit any key ...");
    System.in.read();

    executorService.shutdownNow();
    }
}

SystemOutTraceNotifier

This class creates and configures an instance of FBTraceMonitor.

  public SystemOutTraceNotifier(String database, String user, String pass) {
    traceManager = new FBTraceManager(GDSType.getType("PURE_JAVA"));
    traceManager.setHost("localhost");
    traceManager.setDatabase(database);
    traceManager.setUser(user);
    traceManager.setPassword(pass);
  }

The main method sets the logger output stream to System.out and starts the trace session.
The FBTraceManager instance will write all trace output to the output stream, while our  method simply waits ‘forever’, or until it gets interrupted by invoking the terminateNow() method of our executor service (see above).

    public Void call() throws Exception {
        try {
            traceManager.setLogger(System.out);

            System.out.println("[*] Start trace session " + sessionName);
            traceManager.startTraceSession(sessionName, TRACE_CFG);

            try {
                Thread.sleep(Long.MAX_VALUE);
            } catch (InterruptedException ex) {
                System.out.println("[*] Trace was interrupted");
                Thread.currentThread().interrupt();
            }

            Integer sessionId = traceManager.getSessionId(sessionName);
            if (sessionId != null) {
                System.out.println("[*] Trace is stopping");
                traceManager.stopTraceSession(sessionId);
            } else {
                System.out.println("[*] Could not stop (session id is null)");
            }
        } catch (SQLException ex) {
            ex.printStackTrace(System.out);
        }
        return null;
    }

Note that we must stop the trace session. Otherwise the trace manager will not terminate its own worker thread, and our program will still run even after the call() method terminated.

A problem arises if we try to end our trace before anything has been written to the trace. In this case, the FBTraceManager has not yet recorded the session id of our trace session, and traceManager.getSessionId(sessionName) will return null. As traceManager.stopTraceSession(null) will cause an error and the trace manager will not terminate its worker thread.

Configuration of the trace session

The configuration string for the trace session:

private static final String TRACE_CFG = "database>\n"
            + "	enabled                true\n"
            + "	log_statement_finish   true\n"
            + "	print_plan             true\n"
            + "	time_threshold         0\n"
            + "/database>";

Output

To test the program we launch a Firebird client, connect to the example employee database, and issue the SQL statement “select * from customer”.

[*] ... hit any key ...
[*] Start trace session 49413da1-9a4b-44e5-bf58-540d729124da
Start trace
Trace session ID 5 started
2019-02-08T10:16:58.2480 (5792:00000000003CD9E8) TRACE_INIT
	SESSION_5 58ad9a02-b306-48d0-8afc-fd6b3d86a449
	

2019-02-08T10:16:58.2490 (5792:00000000003CD9E8) EXECUTE_STATEMENT_FINISH
	C:\PROGRAM FILES\FIREBIRD\FIREBIRD_2_5\EXAMPLES\EMPBUILD\EMPLOYEE.FDB (ATT_26, SYSDBA:NONE, NONE, XNET:T11176)
	C:\Program Files\Firebird\Firebird_2_5\bin\isql.exe:2728
		(TRA_814, CONCURRENCY | WAIT | READ_WRITE)

Statement 105:
-------------------------------------------------------------------------------
select * from customer
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN (CUSTOMER NATURAL)
15 records fetched
      0 ms, 33 fetch(es)
...

Important notice

From https://firebirdsql.org/rlsnotesh/rnfb25-trace.html#rnfb25-trace-plugin:

This Trace API exists in Firebird 2.5 and is in use. However, since it will be changed in forthcoming sub-releases, it is not officially published and must be regarded as unstable.

Source code

The code for this article will be available in a GitHub repository soon.

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s