Examples

Log4db2 comes with some examples to show how can it be used inside the code. The examples are:

• Bank operation simulation.
• Connection detection.
• n-queen on a chessboard.
• Resignal via logger.
• Fake installation*.

These scripts are not included in the distributable version, but they are part of the sources. You can find the required files in src/examples/sql-pl. Before continuing, you need to follow the instruction to install from sources.

cd src/examples/sql-pl

Bank

bank directory

This is a set of scripts that creates and drops the functions and executes the simulation.

cd bank

DemoBank Create

Creates the tables for the simulation. The following instruction creates the stored procedures with logging capabilities, which allows you to see how the logging impacts the performance.

db2 -tsf DemoBankCreate.sql

In order to compare the execution, you can create the same stored procedures without logging capabilities.

db2 -tsf DemoBankCreateNoLogger.sql

It is up to you to decide in which mode you are going to start, however, you should execute the whole simulation for both cases. You could see that both files have the same business logic, and they differ only in the logs.

diff DemoBankCreate.sql DemoBankCreateNoLogger.sql

DemoBank Execution

The simulation creates accounts, realizes withdrawals and deposits, queries the balance and deletes the accounts. However, the order of these operations can be random or (semi)sequential.

For random operations:

db2 -tvf DemoBankRandom.sql

For (semi)sequencial operations:

db2 -td@ -vf DemoBankSequential.sql

It was called semi sequential because the operations are in the same order, but the amounts of the transactions are random, thus the process in some cases could not be the same. For example, let's suppose you have in an account $100, and the next operation is a withdrawal of a random value; if the withdrawal is less or equal to $100 the answer is different from a withdrawal of a value greater than $100 (invalid operation.)

To change the logger configuration during the execution, you can use:

CALL logadmin.register_logger('DemoBank.Operation', 5);

Finally, there is a simulation without logging calls. This is useful to test the impact of the logger.

db2 -td@ DemoBankSequentialNoLogger

There is an article in 'Ideas for DB2' blog that tests the performance of log4db2 with all these examples. Take a look at the analyses and results to discover how low log4db2 performance impact is.

DemoBank Drop

Once you have finished the simulations, you can drop the tables and stored procedures. This step is required to run after having finished a simulation and re-starting with a different set of stored procedures (with or without logging capabilities)

db2 -tf DemoBankDrop.sql
cd ..

Connections

connection directory

Each time a connection is issued, you would probably want to know who got connected, from where, and get information about the environmental conditions of the connection. For this purpose, we will use the feature that DB2 provides via a configuration parameter that calls a stored procedure once a connection has been established.

In this example, we will show how to log in log4db2 the information about the connection, and then you would have the possibility to exploit it dynamically.

There are two files in this example. The first creates and configures DB2; the other removes the configuration done before.

Let's suppose your database is called log4db2. It is recommended to use 2 terminal windows to run this example.

Execute these commands in terminal A.

cd connection
db2 connect to log4db2
db2 -tsf ExampleConnection.sql

In terminal B:

db2 connect to log4db2

In terminal A check the logs:

db2 "select * from logdata.logs"

At this moment, you will see an entry in the LOGS table with information about the established connection.

In terminal A, we clean the objects.

db2 -tf UndoExample.sql
cd ..

You can further extend this example by taking more context information and logging all of that. This article (invalid link) (new valid link) could give you more ideas.

Resignal

resignal directory

When you develop stored procedures sometimes you have signals raised from other parts of the code, that you do not control. It is not always easy to deal with them, sometimes they only indicate a warning, or probably they really represent a problem. The best way is to write them down, and here log4db2 is used to write that information in the logs.

Take a look at the code to understand what it is doing: https://github.com/angoca/log4db2/blob/master/src/examples/sql-pl/resignal/ExampleResignal.sql

The code raises a division-by-zero error.

cd resignal
db2 -tvsf ExampleResignal.sql
db2 "select * from logdata.logs"
cd ..

n-queens algorithm

The n queens on a chessboard algorithm is a typical computational problem, where you need to put n queens on an nxn chessboard, in a fashion that not 2 queens treat themselves. The natural solution is using backtracking, but it consumes a lot of resources. Therefore, this takes a while to solve a big chessboard. This algorithm is included in the examples because it can simulate a stored procedure that runs for several minutes.

The algorithm is based on a set of tools to manage matrices in Db2. Thus, you need to install this tool, then the n-queen algorithm, and finally run it with different sizes. Once you have finished, you can drop all objects.

cd n-queens
db2 -tvsf 1-createMatrixToolsLog4db2.sql
db2 -tvsf 2-N-QueensLog4db2.sql

For the previous 2 files, there are corresponding files without using log4db2.

Now, to run it, you can use the 2-N-QueensLog4db2.sql or use the stored procedure CALL N_QUEENS(16) specifying the board size and the number of queens.

During the execution, you check the logs with the tail_logs script or perform queries directly on the database on the LOGS table. Because the algorithm could take several minutes with 16 queens, it is a great moment to modify the configuration of the appender to write more o fewer messages. Remember that when using the default configuration, it takes a maximum of 30 seconds to apply the configuration. To change the logger configuration, you can use:

CALL logadmin.register_logger('N_QUEENS.SOLVE_N_QUEENS', 5);

To remove everything you can run:

db2 -tf 98-dropN-Queens.sql
db2 -tf 99-dropMatrixTools.sql
cd ..

Performance

As you probably noticed, there are 2 files for the matrix tools, as well as for the n-queens algorithm. One of them in both cases uses log4db2 and the other is exactly the same but without any variable or call to the utility.

By running both of them on the same server, you can compare the impact on the performance when using log4db2.

The next table shows the execution time with a different chessboard when not installing log4db2, and with it installed and with different logger levels for the table appender. The only logger active was N_QUEENS.SOLVE_N_QUEENS.

Chessboard size	Iterations	No log4db2	off	error	warn (default)	info	debug
1x1	2	0.02	0.04		0.13
2x2	3	0.02	0.03		0.15
3x3	6	0.02	0.07		0.33
4x4	9	0.03	0.09		0.23
5x5	6	0.07	0.08		0.16
6x6	32	0.18	0.42		0.90
7x7	10	0.09	0.14		0.40
8x8	114	0.60	2.46		3.67
9x9	42	0.24	1.02		1.56
10x10	103	0.88	2.39		3.99
11x11	53	0.39	1.83		2.23
12x12	262	2.77	9.19		12.11
13x13	112	1.36	3.74		5.16
14x14	1900	27.43	86.51		123.17
15x15	1360	21.25	62.88		87.90
16x16	10053	186.08	527.70		683.10

The time was calculated by putting values current timestamp before and after each run.

The tests were run 3 times for each combination. The tests were run on the same computer (MacBook Pro, macOS Catalina, Core i7, 16 Gb, with Db2 11.5.7 in Docker container.

Fake installation

This is not really an example of log4db2 usage. Instead, it is a way to completely disable this utility, by creating empty stored procedures and functions. This is very handy when you do not need to have a log4db2 in a database, but you do not want to modify other SQL PL routines.

cd disable
db2 -tf FakeTables.sql
db2 -tf FakeUtilityBody.sql
db2 -tf FakeUtilityHeader.sql
cd ..