Configuration

The configuration of this utility is done via insert/update/delete on the set of tables described below. There are no properties files or similar things in log4db2; this is just because the utility is coded only in SQL-PL, and everything is related to SQL-PL objects or tables.

Each table except LOGS has a role in the configuration. Please follow the following link to see a ER diagram to understand the relations between tables.

CONFIGURATION table

This is a key-value table. This table is under the LOGDATA schema.

• autonomousLogging: This parameter allows to choose the logging method when using Tables appender. The utility can write the log in the same transaction, or it can write the log in an independent transaction (autonomous). If a rollback is done in the transaction when using autonomous, the log is written anyway; when not using autonomous, at the end the message is not written (it is rollbacked).
• defaultRootLevelId: This parameter defines the default 'level' for the root logger. If a given logger does not have a defined level, and root logger is neither defined, this value will be taken into account. In other words, when there is not a value for ROOT in CONF_LOGGERS, this is the level ROOT will have assigned.
• internalCache: This value switches the cache on/off. The cache reduces the select operations (I/Os) by querying an associative array where the configuration is stored, instead of querying the configuration in the tables each time a call to the utility is issued. This improves the time to retrieve the ID of a logger and the values of the configuration table (this table.)
• secondsToRefresh: Determines the configuration refresh frequency in seconds. The configuration could be modified at any time, and it will be reloaded periodically. The configuration could also be cached, and this parameter indicates the time necessary to check for the new configuration.

Testing

This value is used only when running internal tests with db2unit.

• logInternals: Activates the utility internal logging. This is mostly used for debugging or developing purposes when parts of the code write logs about their internal status. Not necessary when using log4db2 normally.

LEVELS table

These are the levels in the utility. 0 - OFF should always exist, and it means that logs are not going to be written. Any other level should be bigger than 0 and consecutive to the previous number (0, 1, 2, ..., no gaps between ids.) This table is under the LOGDATA schema.

The default ones are:

• 0 - OFF.
• 1 - FATAL.
• 2 - ERROR.
• 3 - WARN.
• 4 - INFO.
• 5 - DEBUG.

You could eventually have a different set of levels for your own needs. To achieve this, you can modify them except the 0, as already said, it should always exist. Please use only letters for the level names, and try to not use symbols, especially the percentage sign '%' should be avoided.

CONF_LOGGERS table

This indicates the level for a given logger. ROOT should be defined before configuring a specific logger/level, because all loggers are descendants from ROOT. This table is under the LOGDATA schema.

CONF_LOGGERS_EFFECTIVE table

This is an internal table for the utility, and the user cannot modify it. The utility itself modifies the values of this table according to the configuration in the CONF_LOGGERS table. The table contains the 'effective levels' for each logger because some intermediate levels could not have an explicitly declared level in the CONF_LOGGERS table. This table is under the LOGDATA schema.

REFERENCES table

Indicates which loggers are active to write the messages (to log) with which appenders. You could eventually want to log only a part of the hierarchy. ROOT could not be always active. This table is under the LOGDATA schema.

APPENDERS table

In this table, the different types of appenders are defined. Each appender writes the events in different kind of mechanisms, and each one could have multiple configurations. This table is under the LOGDATA schema.

There are 2 built-in appenders and 2 more dynamic appenders. Also, you can create your own appender.

Built-In appenders

• NULL: Just drops the messages. Good for tests.
• TABLES: Writes the log messages directly in the LOGS table. This is a pure SQL PL implementation, and it works perfectly with the DB2 Community (Express-C) edition. This appender could be executed as an autonomous transaction or not. To define this behavior use the autonomousLogging value in the CONFIGURATION table.
• STDOUT: Writes the log messages to the standard output. It needs SERVEROUTPUT ON and the messages will be printed when returning the control to the db2clp.

Dynamic appenders

• UTL_FILE: The log messages are written in an external file. This uses the built-in modules with the same name. This could not work when using DB2 Community / Express-C edition.
  • The configuration of this appender is done via an XML file. The necessary files are under the xml directory.
• CGTT: This appender writes the message in a temporary table.

Dynamic appender in the future

The following appenders have been planned, but they have not yet been developed for the moment.

• DB2LOGGER: This uses the existing logging facility for DB2 written in C. This makes this utility like a wrapper. This mechanism needs the installation of the DB2_LOGGER utility before using it.
• DB2DIAG: The log messages are written in the DB2 DIAG file. This uses the db2AdminMsgWrite function to write in that file. In order to activate this function, it is necessary to put the compiled C file in the DB2 binaries.
• JAVA_LOGGER: This mechanism sends the messages to a Java Stored Procedure. Depending on the Java Stored Procedure and if there is a log4j or slf4j configuration in the Java code, the logs will be written into files or another kind of mechanism.
• SMTP: This appender sends messages to an email.

CONF_APPENDERS table

Specific configuration for the appenders (instances of the appenders.) For each appender, there could be different types of configuration (each instance of the same appender has a different configuration.) Each appender instance needs the ID of the related appender, the specific configuration (depending on the appender it could be null), and the message layout. This table is under the LOGDATA schema.

• Null: This appender does not have any configuration. The field is NULL.
• Tables: This appender does not have any configuration. The field is NULL.
• UTL_FILE: The configuration field indicates the name of the file where the logs are to be written. The configuration should follow the XML Schema indicating the directory and the file.
• CGTT: This appender does not have a specific configuration. It will write the messages in the TEMP_LOGS table.

The following are the possible option for future appenders. TODO

• DB2logger: The configuration field indicates the stored procedure (with its schema) to call the DB2 logger. There are two methods in DB2LOGGER, which indicate which one will be called.
• db2diag.log.
• JavaLogger: The configuration field indicates the name of the external stored procedure (with the schema) to call. The Java stored procedure has to deal with the message (write it to a file, send in a socket, etc.).
• Mail.

Messages layout

The layout specifies how log messages are written. Depending on the appender, it could take or not into account this configuration.

The possible conversion is the following, and each symbol must be preceded by a percentage sign.

Conversion Word	Effect
c	Inserts the name of the logger that calls the utility.
m	Inserts the application-supplied message.
p	Inserts the level of the logging event.
C	Inserts the client hostname.
H	Inserts the application handle.
I	Inserts the application id.
L	Inserts the nesting level.
N	Inserts the application name.
S	Inserts the session authorization.
T	Tabulates with one space for each nesting level.

The default layout for the 'Tables' appender, called 'Tables', is: [%p] %c - %m. It means each message will be composed of the logger level, the logger name (it should be the routine's name in the logger hierarchy), and finally the message.

Historical tables TODO

They store the previous values after a modification, thanks to TimeTravel. This set of tables allows having a trace of the changes in the configuration. This Db2 feature is available since version 10.1. For these tables, there is nothing to configure.

• CONF_APPENDER_HIST
• CONF_LOGGER_HIST
• LOG_HIST

LOGS table

As mentioned at the beginning of this article, the LOGS table does not have a role in the configuration of the utility. However, this table can be configured in 2 ways:

• Regular table.
• Partitioned table.

The regular table can be used directly once the utility is installed. A regular table for logs should only be used if the number of logs will remain low, less than a hundred thousand logs.

Instead, if you plan to store thousands or even millions of log records, it is better to have a partitioned table for logs. However, when using a partitioned table you will need to define a mechanism to add new partitions. It is recommended to have many short-duration partitions, like for each day, in order to have an easy mechanism to remove the old data from the table.

For example, 31 daily partitions, and drop the oldest one corresponding one month ago.

In order to switch from one mechanism to the other, you need to provide the -p in the installer. Or when installing manually include the 10-LogsTablePartitioned.sql.

Remember, when selecting a partitioned table for logs, you will need to create at least one partition to use the utility; otherwise, logs will not be written, but no error message will be raised. You will need something like:

ALTER TABLE LOGS ADD PARTITION STARTING 'XXXX' ENDING 'YYYY' EXCLUSIVE

Where YYYY is at least 1 day after XXXX.