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.
-- 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.
...
<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. |
...
<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.
...
<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
).
<?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?