Tips

Logging without logger

You can still write a log without specifying any logger. Sometimes, you just want to write a message to the LOGS table and you do not care about the hierarchy or similar things. This can be done by using ROOT logger, which is always defined in the CONF_LOGGER_EFFECTIVE. However, the associated logger level or appender could be configured to not log ROOT.

call logger.error(0, 'My own message without logger');
call logger.log(0, 3, 'My other message without logger');

You can also call the procedures without all the necessary parameters:

call logger.log('My own message without logger');
call logger.log('My other message without logger', lev_id => 2);

By doing this, you do not take advantage of the hierarchy feature, but it is a fast way to use the logging utility.

Hierarchy

The process to define a hierarchy could be difficult when you start to use this utility. However, you have to understand why it is useful and how your existing stored procedures are organized.

If your stored procedures are organized by schemas and/or modules, you can define the logger hierarchy based on this current structure.

DB distribution   |  log4db2 hierarchy
schema.SP        --> level1.level2 (level1: schema, level2: SP1)
schema.module.SP --> level1.level2.level3 (level1: schema, level2: module, level3: SP1)

However, your current schemas or modules could not reflect the internal organization of the database. In this case, you need to group the stored procedure and functions according to their nature. For example, all routines associated with sales on one level, all from marketing to another. Once you have done that, you can group those already-grouped routines in more specific levels to have a second level in the hierarchy.

sells
sells.local
sells.global
marketing

Once you have defined this hierarchy, it could change, it should not be rigid but flexible for new levels. Make sure that new developments use this hierarchy.

It is highly recommended to name a given logger with only letters and numbers. Try to not use the percentage sign '%' as part of the name, especially followed by an m (%m).

When using recursion in stored procedures, it is highly recommended to control the exception SQL0724 when the maximum nested levels have been reached. This prevents the stored procedure to finish abruptly and recovers the process smoothly

DECLARE CONTINUE HANDLER FOR SQLSTATE '54038'
  BEGIN
   -- Do something
  END;

Retrieving the messages

The messages are written in the LOGS table, and there are several ways to retrieve that information:

• Directly with a SELECT on the LOGS table.
• By querying the LOG_MESSAGES view.
• By calling the stored procedures LOGS or NEXT_LOGS.

However, all these options retrieve the information statically. They do not show how the table is filled. In order to solve this issue, there is a script for all platforms that query continuously this table and show the table content dynamically. Actually, it calls the NEXT_LOGS procedure and then waits. The number of iterations and the pause between iterations can be customized; however, the default configuration is unlimited iterations with a second pause between each one, which is an appropriate configuration for most cases.

• tail_logs
• tail_logs.bat
• tail_logs.ps2

This script is part of the distribution, and there is no need to install it, just call it.

Performance

The utility performance can be improved by changing some properties of the DDL. However, these changes have implications and you should understand them.

• Many routines have comments indicating which part of the code can be commented out. These parts will reduce some operations that normally are not used and commenting on them, will reduce some CPU cycles.
• When using the LOGS table to write the messages, this table can be configured with NOT LOGGED INITIALLY. However, when you put a table with this feature, the commit could be longer and there are some HARD issues: KnowledgeCenter.

Remove log4db2 without modifying the code

You could probably need to not use log4db2 but it could also be already used in your code, and this change hardly impacts your development. Here, there are three ways to not use log4db2:

• Change the configuration to not use any logger. You can do that by removing any row in the REFERENCES table, or you can change the level for any logger in the CONF_LOGGERS table to 0. In this way, you configure the utility to do nothing, however, it is still installed, and your code stays the same.
• Uninstall the current log4db2 and replace it with the fake install that is part of the sources, in the examples directory, disable. It has the scripts to install log4db2 with all public headers but there is no logic behind that, just empty routines. This method removes completely log4db2 and replaces it with a mock layer, and your code stays the same.
• Modify your own code, replacing any single call to log4db2. The is the most expensive, with many side effects. If your code has many components, and many calls to log4db2, this could imply laborious work. Instead, it reduces completely any overhead and extra code for logging.