log4db2 #

A logging utility like log4j for IBM DB2 SQL PL

log4db2

Log4db2 is a logging utility for Db2 for LUW that uses SQL instructions with SQL PL code.

Its purpose is to provide an easy way to write messages from a SQL routine, with the possibility to query these messages directly from the database, allowing to monitor the progression of a process. This utility aims to reduce the time used for developing, testing, debugging and monitoring SQL routines, by centralizing the messages produced by the code.

The idea and architecture of this utility are based on the popular Java logging utilities, such as Log4j and slf4j/logback.

The licenses of this project are:

With these two licenses you are free to use, modify and distribute any part of this utility.

Author:

Andres Gomez Casanova (@AngocA)

These are some useful links:

Installation

One variable needs to the specified in order to run the install and example scripts.

LOG4DB2_PATH

This variable is initialized via the init script.

Before installing the scripts in a database, a connection to it has to be established. If not, an error will be raised.

Linux/UNIX/Mac OS:

Just follow these steps:

tar -zxvf log4db2.tar.gz
cd log4db2
. ./install

Make sure to put the dot (source command) before the script. This will source the values and use the current connection.

Windows Terminal (CMD - db2clp):

First, unzip the file log4db2.zip, and then:

cd log4db2
init.bat
install.bat

Windows PowerShell:

First, unzip the file log4db2.zip, and then:

cd log4db2
.\init.ps1
.\install.ps1

Check install

After the install, all statements should have been successful.

A more detailed guide to install the utility can be found in the Install section of the wiki.

You can also install the utility from the sources and run the examples and tests: wiki/Install%20from%20sources.

Once the utility is installed, you can customize the utility. For more information, please visit the configuration section: wiki/Configuration.

Write code

This could be the structure of your routine’s code (Procedure or function).

CREATE ... HELLO_WORLD ()
 MODIFIES SQL
 BEGIN
  DECLARE LOGGER_ID SMALLINT;
  -- Your declarations

  CALL LOGGER.GET_LOGGER('Your.Hierarchy', LOGGER_ID);
  -- Your code
  CALL LOGGER.INFO(LOGGER_ID, 'Your informational message');
  -- Your code
  CALL LOGGER.ERROR(LOGGER_ID, 'Your error message (important!)');
  -- Your code
  CALL LOGGER.DEBUG(LOGGER_ID, 'Your message for debugging purposes');
  -- Your code
 END @

As you can see, there is a call to GET_LOGGER to register the logger, and obtain its id. Then, you write messages by providing the id and the text. That’s all!

Execution

1. Invoke the code

Then, you invoke your code (Depending if it is a stored procedure or a function.)

CALL HELLO_WORLD(); -- Stored procedure.
VALUES HELLO_WORLD(); -- Function.

2. Check the logs

This is the easiest way to check the log messages.

CALL LOGADMIN.LOGS();

From the CLP is:

db2 "CALL LOGADMIN.LOGS()"

Check the Usage section for more information about the levels, how to access the messages and configure the utility: wiki/Usage.

3. Changing the verbosity

The easiest way to change the logger configuration is by calling the following stored procedure.

db2 "CALL logadmin.register_logger_name('Your.Hierarchy', 'debug')"

Depending of the logger levels on the code, and the confiration, you can see more messages or less messages in the logs.

Files and directories

These are the files included in the released version:

The * in the install-related files means that several files for each name can be found: