Usage

Requirements

These are the requirements to be able to use this utility in your code:

• Having installed log4db2 in the database.
• Change/activate the SQL access level in the stored procedures or functions MODIFIES SQL DATA.
• The user used to execute your routines should be able to execute the log4db2 routines.

Usage in the source code

NOTE: Each time you call log4db2 make sure the message you are passing does not contain sensitive information. The security for the business tables could not be the same for the logger table. Because of this reason, information in the LOG table could be exposed.

To use log4db2 from your code, you need to know two stored procedures:

• GET_LOGGER.
• LOG

Also, instead of using the LOG stored procedure, you can use the macros:

• DEBUG.
• INFO.
• WARN.
• ERROR.
• FATAL.

The previously stored procedures will be explained in detail in the following sections.

The table below shows the database objects used when developing an application. All the routines (stored procedures and functions) are under LOGGER module, which is under the LOGGER_x schema (where x is the log4db2 version).

Object name	Type	Description
DEBUG	Proc	Macro to log messages in 'DEBUG'-5 level.
ERROR	Proc	Macro to log messages in 'ERROR'-2 level.
FATAL	Proc	Macro to log messages in 'FATAL'-1 level.
GET_LOGGER	Proc	Registers the name of a logger and returns the ID for that logger.
GET_LOGGER_NAME	Func	Retrieves the logger name for a given logger ID.
INFO	Proc	Macro to log messages in 'INFO'-4 level.
LOG	Proc	Writes the log message to the appenders. This function formats the message according to the layout.
LOG	Tabl	This table contains the log messages. This table is in the LOGDATA schema, but it has a public alias that allows to access the table without the schema.
WARN	Proc	Macro to log messages in 'WARN'-3 level.

GET_LOGGER Procedure

First, a logger could be considered like a stored procedure or function (a routine), or a set of them that generate logs.

The GET_LOGGER stored procedure receives a name and returns the ID of that name in the internal hierarchy of the utility. The hierarchy is defined by dots, starting from the lower level and ascending to the last son. Root is the ancestry of all loggers.

For example the name foo.bar.tata has the following hierarchy:

ROOT
+--foo
   +--bar
      +--tata

Another example could be foo.toto and its hierarchy is:

ROOT
+--foo
   +--toto

The two examples in the same hierarchy give:

ROOT
+--foo
   +--bar
   |  +--tata
   +--toto

As you can see toto and bar are siblings, with a common parent foo. ROOT is always present because is the root logger.

It is important to indicate that YOU define the hierarchy. You give names separated by dots to indicate different 'modules', 'packages', or whatever you consider appropriate.

Advice is to use the same hierarchy of the database (at least to start with this approach): schema.module.storedProcedure and schema.module.function.

The returned number is generated by the utility, and it allows to determine which logger generated a log. This is not important, and you do not have to deal with this number; you just have to generate it (GET_LOGGER) and pass it when logging (LOG).

It is very important to create your routines with the flag MODIFIES SQL where log4db2 is going to be used directly or indirectly. If you do not do that, when issuing the first call, an error message will be raised. Please check this article.

Note: The ROOT word is reserved only for root logger. Therefore, no other logger can have this name nor a part of a hierarchy.

Example

Let's consider two stored procedures called 'pitagoras' and 'fibonacci' that realize mathematical computations. Both of them are in the same schema 'maths'.

maths.pitagoras
maths.fibonacci

You pass the complete name of each stored procedure to the logger.get_logger stored procedure. Each of them will have a different logger id because they were registered with a different hierarchy.

CREATE PROCEDURE MATHS.PITAGORAS (...)
BEGIN
  DECLARE LOGGER_ID SMALLINT;
  LOGGER.GET_LOGGER('maths.pitagoras', LOGGER_ID);
  ...
END@

CREATE PROCEDURE MATHS.FIBONACCI (...)
BEGIN
  DECLARE LOGGER_ID SMALLINT;
  LOGGER.GET_LOGGER('maths.fibonacci', LOGGER_ID);
  ...
END@

However, you could decide that you are going to register them only 'maths', and you will have the same logger id for both stored procedures.

CREATE PROCEDURE MATHS.PITAGORAS (...)
BEGIN
  DECLARE LOGGER_ID SMALLINT;
  CALL LOGGER.GET_LOGGER('maths', LOGGER_ID);
  ...
END@

CREATE PROCEDURE MATHS.FIBONACCI (...)
BEGIN
  DECLARE LOGGER_ID SMALLINT;
  CALL LOGGER.GET_LOGGER('maths', LOGGER_ID);
  ...
END@

As you can see, you can define the hierarchy as you want.

LOG Procedure

This is a stored procedure that invokes the utility to register an event (log), but it depends on the current configuration if the log is really stored or just by-passed.

To call this stored procedure, you indicate the logger id (retrieved from GET_LOGGER), the level id, and a message.

Level id is a number between 0 and the number of levels defined in the LEVELS table. The default ones are:

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

The message contains what you will write into the log. When performing a concatenation, it is highly recommended to be sure that the strings being concatenated are not empty (NULL); if a NULL is passed, the result will be an empty string (NULL) and you will not be logging anything.

The configuration will decide if the given message is actually written via the appender or not (just by-passed).

For more information about how to configure log4db2, please visit this section.

A working logger.

CALL LOGGER.LOG(LOGGER_ID, 4, 'A message logged in INFO level');

Depending on the configuration it will log (An active appender with INFO level active):

XXXXX, 4, myRoutine, A message logged in INFO level

A null string could be:

SET value = NULL;
CALL LOGGER.LOG(LOGGER_ID, 2, 'The value for this key is ' || value);

This will write:

XXXXX, 2, myRoutine, NULL

Macros

There are some macros for the default levels. They reduce the number of parameters to pass to the stored procedure by changing the name of this last. With the macros, you do not need to recall the ID number for each level.

For example, if you want to write a log with Warn level, you could use:

CALL LOGGER.WARN(LOGGER_ID, 'The application has started');

Instead of:

CALL LOGGER.LOG(LOGGER_ID, 3, 'The application has started');

In a similar way for the other 5 levels (OFF does not have macro because it never writes a log):

CALL LOGGER.FATAL(LOGGER_ID, 'Dump stack trace');
CALL LOGGER.ERROR(LOGGER_ID, 'File not found');
CALL LOGGER.INFO(LOGGER_ID, 'Finishing process');
CALL LOGGER.DEBUG(LOGGER_ID, 'The value ' || VALUE || ' is bigger that ' || MAX);

For a complete example, please check the source code src/examples/sql-pl. The description of these examples is on the Examples page.

Administration

The usage of this utility implies:

• Consult the generated logs.
• Consult and modify the current configuration.

The utility has a set of stored procedures for different tasks.

Administration

The following table shows the objects to be used to administrate the generated logs.

Routine name	Type	Description
LOGGER.ACTIVATE_CACHE*	Proc	Activates the cache.
LOGGER.DEACTIVATE_CACHE*	Proc	Cleans the cache and deactivates it.
LOGGER.GET_DEFINED_PARENT_LOGGER	Func	For a given logger returns the closer ascendancy that has a defined logger level.
LOGADMIN.LOGS	Proc	Shows the last 100 log messages truncated to 72 characters with the hour (not the whole timestamp).
LOG_MESSAGES	View	Retrieves the log messages with the date, the logger level name, and the whole logger name.
LOGGER.MODIFY_DESCENDANTS	Proc	Modify the logger level of a logger with all its descendants.
LOGADMIN.NEXT_LOGS	Proc	Shows the log messages by pages, truncating the message to 72 characters with the hour (not the whole timestamp).
REFRESH_CONF	Proc	Refreshes the cache configuration immediately.
LOGADMIN.RESET_TABLES	Proc	Deletes all rows, and leaves the basis tables as they were after the installation.
LOGADMIN.REGISTER_LOGGER_NAME	Proc	Register a logger and specify a level name.
LOGADMIN. REGISTER_LOGGER	Proc	Register a logger and specify a level.
LOGGER.SHOW_CACHE*	Proc	Shows the content of the logger cache if it is currently used.
LOGGER.SHOW_CONF	Proc	Shows the current content of the configuration cache and information about its freshness.
LOGADMIN.SHOW_LOGGERS	Proc	Returns a list of the loggers registered in the utility.

(*) Cache operations will be removed. This will be the default option.

Appenders

This is the list of appenders. They are never called directly from the user code, but instead, there are internally used by log4db2.

Routine name	Type	Description
LOG_DB2DIAG	Proc	Sends the log message to the db2diag.log file.*
LOG_DB2LOGGER	Proc	Sends the log message to the DB2LOGGER utility.*
LOG_JAVA	Proc	Sends the log message to a Java Stored Procedure.*
LOG_SQL	Proc	Writes the log message to a table.
LOG_UTL_FILE	Proc	Writes the log message to an external file.

(*) These appenders are not yet developed.

The layout of a message is defined in CONF_APPENDERS. For more information about the possible values, please check the configuration section.

Query the logs

You can query the logs in different ways:

• Directly from the LOGDATA.LOGS. However, there are some hidden columns, and the logged level and logger are givens as IDs. Thus, you have to do the joins between tables.
• Using the view LOGGER_X.LOG_MESSAGES. This view executes the underlying query to join the tables, and you get information that can be easily read. X is the log4db2 version.
• With the LOGADMIN.LOGS procedure. It returns the 100 more recent rows of the table, with the messages truncated to 72 characters, and the hour (no date.)

Here some examples:

SELECT * FROM logs;
SELECT * FROM logger_X.log_messages;
CALL logadmin.logs()

Modify the logger hierarchy

General configuration

First, to check the current configuration, you can use the SHOW_CONF stored procedure.

CALL logger.show_conf();

To change the configuration, you just modify the configuration table

UPDATE logger_X.configuration
  SET value = Y
  WHERE key = Z;

Where X is the log4db2 version, Y the value to use, and Z the name of the configuration parameter.

If you want to force the configuration reload, please execute:

CALL logger.refresh_conf();

Loggers configuration

First, to see the current configuration you can obtain a list of registered loggers by executing:

CALL logger.show_loggers();

If you want to modify you one level, you issue the DML directly on the database

UPDATE logger_X.conf_loggers
  SET level_id = Y
  WHERE logger_id = Z;

Where X is the log4db2 version, Y is the level Id to insert, Z is the logger ID.

For the logger_id, you can obtain it by:

CALL logger.get_logger(W);

Where W is the logger name.

If you want to modify a given logger and all its descendants, then use the following stored procedure:

CALL logger.modify_descendants(X, Y);

Where X is the logger ID and Y is the level ID.

Also, if you only one to modify a specific logger, you can do it by calling the following stored procedures, providing the name or the id of the level:

CALL logadmin.registe_logger_name(X, LEVEL_NAME);
CALL logadmin.registe_logger(X, LEVEL_ID);