Skip to content

Features

Jump to bottom Edit New page
Andres Gomez Casanova edited this page Jun 3, 2022 · 6 revisions

Increases the instrumentation for SQL-PL code

This utility allows the logging and tracking of major events in the execution of an application.

Based on the log4j architecture

The log4db2 architecture is based on the standard logging utility for Java called log4j. This is an architecture that has been proved that works very well in many conditions, and it is well-known in the development world. Normally, the developers know this kind of logging utility, and this reduces the learning curve and the time to adopt log4db2 in their developments.

More information:

Different Log levels

The utility has different log levels that allow configuring which levels should be written and which not. This feature allows to change dynamically the possibility to write for certain log levels, this means that some calls to the utility do not end with a message written in the LOG table or similar mechanism. This is very useful because an application could be developed with many log messages with different log levels, and the application will run the same in different environments, DEV, TEST, and PROD. And the best is the applications will not be modified to put it on Production; the only thing to do when changing the environment is to change the log4db2 configuration, to register the lower levels (WARN, ERROR, FATAL) for the loggers that should be registered.

Normally, the developers put many flags (Output) in the code to track the progress of a process. Once, the code 'works', those flags are deleted because they degrade the performance. However, if a bug is detected in the code, those flags could be rewritten again in the code, and this is a long process. With log4db2, you just write the messages in the code, and according to the configuration, the messages will be or will not be processed.

More information:

Configurable logger levels

The utility brings 6 levels by default:

  • OFF
  • FATAL
  • ERROR
  • WARN
  • INFO
  • DEBUG

However, these levels can be modified for your requirements in case you need other names or other levels in the hierarchy.

NOTE: Levels modification implies that the macros to write in a specific log level could not be appropriate. It is recommended to rewrite the macros according to your requirements, because the macros work with the logger level ID, but not according to the macro name. For example, if a new hierarchy is: OFF-0, ERROR-1, TRACE-2, the level 1 is modified to write 'error' messages, but the existent FATAL macro will write the log messages in the level 1, and the ERROR macro will write in the level 2, which is incorrect according to the new hierarchy.

More information:

Independent configuration for appenders

The appenders could be configured independently one from another, allowing to write certain messages with one appender, and other messages with another appender. This also allows having multiple configurations for the same appender (i.e. different layouts for different loggers in the hierarchy.)

More information:

Appender usage according to log level

Not all messages should be sent to all appenders. This allows configuring some appenders to receive only low-level messages, like the most important. For example, when an error message arrives, it could be logged in the table as the other messages, but also sent by email (SMTPAppender).

More information:

Different types of appenders

The utility can not only send messages to a table but also they can be sent to other mechanisms. This allows for integration log4db2 with other logging utilities.

NOTE: This implies the usage of external stored procedures that require extra privileges to be installed.

More information:

Plug-ins system to create additional appenders

The utility has an architecture that can be easily extended to write log messages in other mechanisms.

The utility already comes with several options to write via Java, or another logging utility already existent.

More information:

Appender to write to file

There is an appender that allows writing messages to a file. The path and filename can be customized by the user.

More information:

Appender to write to create a global temporary table (CGTT)

There is an appender that allows writing messages to a created global temporary table. This reduces the logging, however, the messages are not persistent, nor accessible from other connections/sessions.

More information:

Message pattern layout is configurable

The generated messages can contain contextual information about the execution environment. The messages can include the username that established the connexion, the server name, the database hour, etc. All these values can be configured thanks to the configuration flexibility of the utility.

Note: Not all appenders use pattern layout, because they write the messages and their information in other formats.

More information:

Logger's hierarchy to control the logger's level

The logger hierarchy is based on a dot-separated string (like the Java packages) that allows to configure each log level independently and to have a bigger granularity of how the utility works.

foo
foo.bar
foo.toto

In the same way, this allows the grouping of elements that share the same configuration.

More information:

Set of error codes

The utility uses its own set of error codes. They are described in the section Error codes, and they explain the reason for the errors and what the user can do.

More information:

Completely configurable from the database

The configuration is stored in a set of tables in the same database, where the relations between the tables are very easy to understand. It is not necessary to deal with configuration files, formatting XML files, or similar things. You just need to perform DML (insert/update/delete) operations in a very small set of tables.

The default installation comes with a basic configuration ready to use.

More information:

Configuration is changed dynamically

The configuration can be changed dynamically without recycling the database (disconnect all users and deactivate.) You just need to put the required configuration in the tables, and the utility automatically will reload the values.

More information:

Sensitive information is not exposed

The log messages on certain occasions contain sensitive information. These messages could travel on the network without any encryption or be written in places where there is a low or non-existent security schema. Because of the nature of this utility that is installed inside the database, the messages share the same security schema of the database; at least a user should be authenticated and depending on the authorities and privileges it has, this user could have been authorized or denied to access and manipulate the logs. Only authorized personnel will have access to the sensitive information that could be eventually found in the logs.

This utility ensures the security of the registered events better than if other logging utilities were used. The messages are not transmitted on the network, nor written on flat-files*. Instead, they are stored locally (when using the Tables appender) and this offers a higher protection level to the registered information.

(* If a different appender from the Tables appender is used, the security of the data is not insured.)

Traffic network is zero

The messages are generated from routines (stored procedures or functions) inside the database, and those messages do not go out of the server, they stay and are stored there. This implies that log4db2 does not generate an overhead on the traffic network.

Very low performance impact when a logger is not active

A logger can be configured to not write any message, and when the application calls the utility that uses this logger, it returns the control to the application as fast as possible, without performing unnecessary operations.

When the LOG procedure is call, the first operation done is to check if the logger is configured to write. If it is configured, then the whole process to write the log is performed. Instead, if the logger is configured to not register events, the utility do not do anything else, it just return the control to the caller.

More information:

Easy administration

The disk quota used by log4db2 you just need to check the database health, specifically the tablespace where the messages are written. But it is even easier with the more recent DB2 versions, because they expand the tablespaces automatically. Thus you just need to have enough disk space for the database. Also, you can delete all messages from the LOGS table, and that's all. You do not have to deal with external files that do not stop growing.

The log4db2 configuration is done via a set of tables. You just need to execute a set of UPDATEs, or sometimes INSERTs/DELETEs depending on your requirements. But the global configuration is easy to do.

More information:

Configuration is stored in a memory cache

The configuration parameters are used in each call made to the utility, however this does not imply that the configuration table is read in each call. The configuration parameters are stored in an internal cache (array) and this allows to keep the values in memory, reducing the quantity of IO operations. The configuration parameters are refreshed from time to time, by reading the values from the table.

More information:

Maximum 3 levels of cascade calls

The LOG and GET_LOGGER procedures use a maximum of 3 cascade levels, which does not impact the quantity of nested calls. Remember that DB2 limits this to 16 nested calls for triggers and 64 nested calls for routines (SQL0724N.) Because of this, the usage of 3 nested calls is not that much.

If it happens to arrive to the limit of nested calls, the utility has a mechanism to write messages without filter them nor taking account on the configuration, and just inserting the message directly on the LOGS table.

However, the other features, like changing the level of a big hierarchy could imply a recursion and arrive to more than 64 nesting calls. For this reason, it is not recommended to use a very big hierarchy; with more than 20 levels, the hierarchy starts to become difficult to understand and the process is longer.

More information:

All objects types are anchored to a few ones

Stored procedure and functions uses data types that are anchored to tables or some global defined data types. It helps to change the values of those elements, in a way that you just need to replace in one place, and everything will use the new data type.

More information:

Installation is done with just the DDL / DML file

In order to install the basic functionality in the database, it is only necessary to obtain the file that contains the DDL / DML sentences. The file can be download from the releases page: https://github.com/angoca/log4db2/releases/latest

It is not necessary to install a driver, nor to have a specific application, nor to have special permission on the operative system or file system. The only necessary thing is to have access to the database where the utility will be installed, and have the enough privileges to execute the process.

The execution and access to the generated data depends on the security schema of the database.

NOTE: There are special appenders that require the addition of certain files to the DB2 installation directory.

More information:

Installation scripts in multiple platforms

The installation scripts are in multiple platforms:

  • Linux - UNIX - MacOS.
  • Windows terminal (CMD).
  • Windows PowerShell.

With different types of scripts, the user could choose any platform to work with and integrate log4db2 within his job.

More information:

Optional administration scripts

There are some script to administer the utility, and they are installed by default. However they could be not installed, and the rest (the core) of the utility works perfectly without them.

More information:

Very well documented code

The code is completely documented. Each routine (Stored procedure and function) describe the purpose and parameters. The objects are described complete including comments for each element.

More information:

Unit tests TDD focus

Most parts of the code have been developed following an TDD (Test Driven Development) approach. This means that each new feature comes with a set of unit test to validate its behavior. This has allowed to test the code, find problems in development phase and build a utility more stable. Tests has the structure of a jUnit suite, however using internal mechanisms.

Currently, the utility has more than 300 tests for different parts of the code.

More information:

Written completely in SQL-PL

The basic functionality of this utility is completely written in SQL-PL. This brings us the following advantages:

  • The source code can be acceded / read / analyzed from the catalog tables.
  • The installation is easier and it does not require special permission on the DB2 installation directory.
  • It can be modified easily, and in the same way, new releases can be applied easily.
  • There is not memory leaks (It depends on the db2pvm.)
  • The utility does not impact the database security. In contrast, the utility uses the same security schema.
  • Ready to use after restoring the database in a new environment.

More information:

Multiple utility versions are allowed

In a single database, there could be multiple version of this utility. This leverage the impact of updating this utility, and only new routines or updated routines will use the newer version. Old routines will continue to use old versions of this utility.

Each release changes the schema where the utility is created, and you can use the most recent version or any other version in parallel.

For more information:

Transactions of short duration

The transactions of the utility have been designed to last the minimum, with few CPU cycles, and without using big quantities of transaction logs. In the same way, if the application that calls the utility incurs into a rollback, the quantity of operations to undo is few.

More information:

Low quantity of lock generation

The utility functionality does not incur in the usage of big quantity of locks. It is the opposite, the utility tries to use as less as possible locks for its transactions.

More information:

Commit independent - Autonomous transactions

The Units of Work write log messages independently from the transactions for the application when using TABLES appender. It means, if a rollback is issued, log4db2 will be write the message in the LOGS table and they will not be part of the undo process. That means that log message are always persisted, and this feature is quite useful in order to detect problems.

This feature is activated by default, but the autonomousLogging configuration parameter allows to activate or deactivate it.

More information:

Open source

The sources are open for the community, everyone can see the code, but also modify it, improved it, changed it or redistribute id. The code is licensed under BSD license, and the documentation (this documentation) is licensed under FreeBSD Documentation license.

This allows to DBAs and developers to see and analyze the code before using in their environments, especially production. They can modify it for their owns requirements and share the modifications with the community.

More information:

Keep track of logger configuration

Thanks to TimeTravel, every single change in the logger configuration is saved in a historical table. This allow to identify when a row was changed, which is very useful for forensic analysis.

This is an optional feature, that can be installed by providing the -t option in the installer.

More information:

LOGS table can be partitioned

The LOGS table can increase its size easily if a lot of log messages are written. Administer a big table is always a problem, and this king of table creates issues when performing queries against it. To overcome this problem, this table can be partitioned. It is recommended to have small partitions, like daily partitions. This allows to remove old logs very easily (detach partition + drop table), and this improves the queries that shows the latest logs; like tail_logs script which uses NEXT_LOGS procedure.

Comparison with DB2LOGGER

There is another logging utility for DB2 that has a different approach. Let's see the similarities and differences between them:

  • Programming language:
    • log4db2 is written completely in PL-SQL. However, it has a set of plugins (appenders) that could be in other programming languages but they are not required for the installation.
    • DB2LOGGER is written with external stored procedures in C.
  • Performance:
    • log4db2 depends on the interpretation of the SQL-PL, because this is not a compiled language, but an interpreted language with a Virtual Machine behind. This could lead to poor performance. However, many operations use data cached in arrays in order to reduce the IOs.
    • DB2LOGGER could have much better performance because it is compiled and runs directly in the instance process or in an independent process (fenced).
  • Installation:
    • log4db2 is very easy to install because you just have to have the necessary rights on the database to create objects (tablespaces, tables, modules).
    • DB2LOGGER requires not only rights to create stored procedures in the database, but it also requires to access the DB2 binaries in order to install the fenced or unfenced routines. For this operation, the person that install the utility should have a high knowledge in DB2 routines and compilation in C. The installation process throws many messages, most of them warning and you should know if you can ignore them. A normal DBA could have problems to understand all this process, because the installation process lacks of detailed descriptions.
  • Dependencies:
    • log4db2 just need a DB2 database and a user with the necessary rights to install.
    • DB2LOGGER needs a Linux/UNIX machine with KSH. It does not work in Windows, and it is attached to KSH for the scripts.
  • Flexibility:
    • log4db2 could be configured via a set of tables, and the configuration takes place automatically. Many components are highly configurable: appenders (Appenders, conf_appenders with the layout, and references), loggers, quantity of levels and a global configuration.
    • DB2LOGGER does not have many ways to configure it, just activate or deactivate a logger.
  • Dynamicity
    • Both utilities has mechanism to update the configuration dynamically.
    • log4db2 update dynamically the logger levels and the global configuration.
    • DB2LOGGER changes the logger configuration in shared memory instantly.
  • Usability:
    • log4db2 takes care about how the logs are written in tables or send them to other mechanism. The user just need to know the 'logger id' and send messages indicating this id. Also, the log4db2 architecture is based on the well-known 'log4j' (appenders, loggers, pattern layout, levels), and this helps the developers to incur very easy into this utility reducing the learning curve.
    • In order to use DB2LOGGER, you need to think about the file behind this utility. I mean, you need to open the file, write, and then close the file.
  • Source code:
    • log4db2 source code is hosted in GitHub and it is licensed under open source licenses (BSD for code, FreeBSD for documentation). It allows the developers to copy the sources and make their own changes, and propose their changes as part of the official trunk. Also, the code is part of a public repository, where users can get the source code, the releases, check the wiki, create issues, etc.
    • DB2LOGGER is published in IBM DeveloperWorks and it does not have an open source license (IBM holds the rights). There is not a forge for this code and there are not signals of evolution (The last modification was in 2008).
  • Documentation
    • log4db2 has this wiki that continually grows with along the new features and new requirements.
    • DB2LOGGER has an excellent article in IBM DeveloperWorks that describe everything. In addition, there is an article in the author's blog where more details are described.
  • Administration
    • log4db2 uses tables for its configuration and to write the messages. Thus, you just need to care about the health of the whole database; not the database AND the logger utility.
    • DB2LOGGER uses external files to write its logs. From times to times, you need to check the volume is using this file. Sometimes it could grow very fast and you do not get alerted by database alerts.

Additionally, DB2LOGGER shows the time elapsed between two calls to the utility from the same agent. This helps to see the time of each step. In log4db2, this feature does not exists, however that calculation could be done by analyzing the output messages.

DB2LOGGER can be obtained here: http://www.ibm.com/developerworks/data/library/techarticle/dm-0601khatri/ (invalid link)

Comparison with a set of external UDFs

There is a primitive set of functions that allows you to write messages into a file. It could be used for debugging applications, however it is quite old, and the features are just a few.

The article is: "A UDF for File Output and Debugging from SQL" written by Sotaro Izuha, and published here: http://www.ibm.com/developerworks/data/library/techarticle/0302izuha/0302izuha.html (invalid link)

It was developed for DB2 v7, with UDFs. However, it only allowed to pass messages to the functions (numbers or strings) and the program write them on a file. That is all.

DB2 features used

These are the DB2 features used to develop this utility

  • Schemas.
  • Modules.
  • Built-in modules.
  • Stored procedures.
  • External stored procedures.
  • Internal stored procedures.
  • Local and global variables.
  • Arrays and associative arrays.
  • Cursors.
  • Dynamic SQL.
  • Get diagnostics.
  • Anchored data types.
  • Triggers.
  • Auto generated columns.
  • Hidden columns.
  • XML columns.
  • Runstats.
  • TimeTravel (Temporal tables)
  • Created global temporary tables.
  • Bufferpools.
  • Tablespaces (automatic storage)
  • User temporary tablespaces.
  • Different page sizes.
  • Views.
  • Alias.
  • Comments.
  • Signal.
  • Resignal.
  • db2clp.
  • CLPPlus.
  • db2unit.

This documentation is licensed under the terms of the FreeBSD Documentation License.

Version 1-Beta: Download Tar file Download Zip file

Logo: Colombian Coffer

Documentation Status

Average time to resolve an issue

Percentage of issues still open

Code analysis at Open HUB:

log4db2 info at Open Hub

Clone this wiki locally