If you can't find the product or version you're looking for, visit support.ataccama.com/downloads

Advanced Database Appender Configuration

The following steps provide instructions about how to log plan execution or write the ONE Runtime Server log file to database tables. The Database Appender inserts logging events into three database tables in a format independent of the Java programming language. These three tables are logging_event, logging_event_property, and logging_event_exception. They must exist before the server is started.

Create database tables for logback

Logback is shipped with SQL scripts that create the required tables. They can be found in the Logback GitHub project.

There is a dedicated script for each of the most popular database systems. The following SQL example creates the database structure in a PostgreSQL database.

postgresql.sql

-- This SQL script creates the required tables by ch.qos.logback.classic.db.DBAppender
-- It is intended for PostgreSQL databases.

DROP TABLE    logging_event_property;
DROP TABLE    logging_event_exception;
DROP TABLE    logging_event;
DROP SEQUENCE logging_event_id_seq;

CREATE SEQUENCE logging_event_id_seq MINVALUE 1 START 1;

CREATE TABLE logging_event
  (
    timestmp         BIGINT NOT NULL,
    formatted_message  TEXT NOT NULL,
    logger_name       VARCHAR(254) NOT NULL,
    level_string      VARCHAR(254) NOT NULL,
    thread_name       VARCHAR(254),
    reference_flag    SMALLINT,
    arg0              VARCHAR(254),
    arg1              VARCHAR(254),
    arg2              VARCHAR(254),
    arg3              VARCHAR(254),
    caller_filename   VARCHAR(254) NOT NULL,
    caller_class      VARCHAR(254) NOT NULL,
    caller_method     VARCHAR(254) NOT NULL,
    caller_line       CHAR(4) NOT NULL,
    event_id          BIGINT DEFAULT nextval('logging_event_id_seq') PRIMARY KEY
  );

CREATE TABLE logging_event_property
  (
    event_id          BIGINT NOT NULL,
    mapped_key        VARCHAR(254) NOT NULL,
    mapped_value      VARCHAR(1024),
    PRIMARY KEY(event_id, mapped_key),
    FOREIGN KEY (event_id) REFERENCES logging_event(event_id)
  );

CREATE TABLE logging_event_exception
  (
    event_id         BIGINT NOT NULL,
    i                SMALLINT NOT NULL,
    trace_line       VARCHAR(254) NOT NULL,
    PRIMARY KEY(event_id, i),
    FOREIGN KEY (event_id) REFERENCES logging_event(event_id)
  );

Configure the Logback extension

The ConnectionSource interface provides a pluggable means of transparently obtaining JDBC connections for logback classes that require the use of a java.sql.Connection.

There are currently three implementations of ConnectionSource, namely DataSourceConnectionSource, DriverManagerConnectionSource, and JNDIConnectionSource. The following example uses a configuration with DriverManagerConnectionSource and a PostgreSQL database.

logging-extension.xml

...
<appender name="DB" class="ch.qos.logback.classic.db.DBAppender">
  <connectionSource class="ch.qos.logback.core.db.DriverManagerConnectionSource">
    <driverClass>org.postgresql.Driver</driverClass>
    <url>jdbc:postgresql://host:port/database</url>
    <user>username</user>
    <password>password</password>
  </connectionSource>
</appender>
...

Connecting to a database using a DataSource is rather similar. The configuration now uses DataSourceConnectionSource, which is an implementation of ConnectionSource that obtains the Connection in the recommended JDBC manner based on a javax.sql.DataSource.

This example uses a implementation of DataSource provided by the PostgreSQL JDBC driver without connection pooling.

To be compatible with a different database vendor, this example needs to be modified. For details, refer to the documentation of the selected JDBC driver.

logging-extension.xml

...
<appender name="DB" class="ch.qos.logback.classic.db.DBAppender">
  <connectionSource class="ch.qos.logback.core.db.DataSourceConnectionSource">
    <dataSource class="org.postgresql.ds.PGSimpleDataSource">
      <serverName>host</serverName>
      <port>port</port>
      <databaseName>database</databaseName>
      <user>username</user>
      <password>password</password>
    </dataSource>
  </connectionSource>
</appender>
...

Many logging events are usually created in a short time span. To keep up with the flow of events that must be inserted into a database, it is recommended to use connection pooling with the DBAppender. Connection pooling with DBAppender affects performance positively.

With the previous configuration example, logging events are sent to the database without any pooling. A dedicated external library is necessary to use connection pooling with DBAppender. The next example uses c3p0.

To be able to use c3p0, you must download it and place the c3p0-VERSION.jar in the classpath. With the following configuration example, the connection pooling is used when connecting to the PostgreSQL database and writing performance will be significantly increased.

logging-extension.xml

...
<appender name="DB" class="ch.qos.logback.classic.db.DBAppender">
  <connectionSource class="ch.qos.logback.core.db.DataSourceConnectionSource">
    <dataSource class="com.mchange.v2.c3p0.ComboPooledDataSource">
      <driverClass>org.postgresql.Driver</driverClass>
      <jdbcUrl>jdbc:postgresql://host:port/database</jdbcUrl>
      <user>username</user>
      <password>password</password>
    </dataSource>
  </connectionSource>
</appender>
...

Logback extension for Oracle database

The following is a full working example for using the DBAppender with an Oracle database and connection pooling.

As a prerequisite, a database schema must be created first. To do this, use a script prepared by Logback developers (Logback script for Oracle).

Since the example also uses the c3p0 library for connection pooling, you must download it and unpack additional JAR files from the c3p0 lib folder to the Java classpath (most likely to: <ATACCAMA_HOME>/runtime/lib).

logging-extension.xml

<?xml version="1.0" encoding="UTF-8"?>
<included>
    <!-- <property name="root.level" value="INFO" /> -->
    <!-- <property name="stdout.level" value="INFO" /> -->
    <!-- <property name="ataccama.level" value="INFO" /> -->
    <!-- <property name="ataccama.stdout.level" value="INFO" /> -->
    <!-- <property name="ataccama.additivity" value="false" /> -->

    <appender name="server-log" class="ch.qos.logback.core.FileAppender">
        <file>../storage/server.log</file>
        <append>true</append>
        <encoder>
            <pattern>%d{yyy.MM.dd HH:mm:ss} %-7([%level]) %msg%n</pattern>
        </encoder>
    </appender>

    <appender name="DB-log" class="ch.qos.logback.classic.db.DBAppender">
      <connectionSource class="ch.qos.logback.core.db.DataSourceConnectionSource">
        <dataSource class="com.mchange.v2.c3p0.ComboPooledDataSource">
          <driverClass>oracle.jdbc.OracleDriver</driverClass>
          <jdbcUrl>jdbc:oracle:thin:@//localhost:1521/ORCL</jdbcUrl>
          <user>logback</user>
          <password>logback</password>
        </dataSource>
      </connectionSource>
    </appender>

    <logger name="ataccama" level="info" additivity="false">
        <appender-ref ref="server-log" />
        <appender-ref ref="DB-log" />
    </logger>
</included>

Was this page useful?