FAQs

Why do not use DBMS_OUTPUT

• Context:

  • There are routines that currently write output messages with DBMS_OUTPUT.

• Question:

  • Why do I need log4db2 instead of continuing using DBMS_OUTPUT?

• Answer:

  • When you use DBMS_OUTPUT you are only writing the message to the system output.
  • The messages written are only visible in the current session. Not other session can access the messages.
  • With DBMS_OUTPUT, you do not have control over when the messages are going to be written in Sysout.
  • With DBMS_OUTPUT, you do not have other options to write a message (persistence, for example).
  • When using DBMS_OUTPUT, the configuration does not have a low granularity; you just active all output or nothing.
  • You need an interactive session from CLP to use DBMS_OUTPUT. With log4db2, you can use any type of database client.
  • DBMS_OUTPUT could "contaminate" real important Sysout messages.
  • The output of DBMS_OUTPUT is retrieved once the execution is finished. With log4db2 you can see the progress in real time.

This is a good article about what a logging framework should be (focused for Oracle): http://radiofreetooting.blogspot.fr/2005/06/oracle-logging-in-plsql.html

Uninstall the utility

• Context:

  • The utility is already installed in the database.

• Question:

  • How can I uninstall the utility once it is installed on the database, and I do not have the sources on the server.

• Answer:

  • The easiest way to uninstall the utility is via the uninstall script that is provided in each release.
  • If you do not have this script on your server, you can download any release from https://github.com/angoca/log4db2/releases, and once extracted you can execute the uninstall script.
  • Also, you can copy/paste the code from the repository, put it in a file, and then execute it.
  • Otherwise, you can try to drop the utility schemas (logdata, logadmin, logger_X where X is the version), and then check the dependencies.

Transaction logging

• Context:

  • The content of LOGS table is not important when a rollback is done.

• Question:

  • How can I improve the performance of writing messages in the LOGS table?

• Answer:

  • You can remove the transaction logging capabilities of this table (NOT LOGGED INITIALLY); however, you have to be sure about all possible scenarios.

For example, if using HADR, that table will not be replicated, and in the case of a takeover, the table cannot be accessed. Therefore, you should be prepared to recreate the table in the old standby-new primary in order to use LOGS table with NOT LOGGED INITIALLY.

The same occurs when doing a restore+rollforward, the table will not be accessible.

Also, if you are using some kind of replication, like SQL replication, this table will not be available.

For more information, please visit: Reducing logging with the NOT LOGGED INITIALLY parameter.

Hide log4db2 calls for installing but still working.

• Context:

  • You have a set of routines and you want to include log some messages.
  • When installing, your routines cannot increase the dependencies on other frameworks.

• Question:

  • Can I use log4db2 without including this as a dependency?

• Answer:

  • The only way to not increase the number of dependencies at installation time, is by calling other routines dynamically (EXECUTE IMMEDIATE). For example, you can create a global variable in your set of routines that indicates if the logger utility is used. In a production environment will be FALSE, and in a development environment will be TRUE. When the routine passed the TRUE value, it will call dynamically log4db2 and this will register the messages according to your configuration.

The overhead in Production (when installing) is just to check a global variable.

Remember, if you set it to True, and you do not have log4db2 installed, then Db2 will raise an SQL exception.

Errors in db2diag.log while installing

• Context:

  • You are installing the utility and you are monitoring the diagnostic file.

• Question:

  • What is the meaning of the following messages?

db2diag.log:

FUNCTION: DB2 UDB, SW- common services, sqlnn_regen_function, probe:1
FUNCTION: DB2 UDB, relation data serv, sqlr_init_tstat, probe:14696
FUNCTION: DB2 UDB, relation data serv, sqlr_init_tstat, probe:14717
FUNCTION: DB2 UDB, relation data serv, sqlr_init_tstat, probe:14777

• Answer:
  • They are normal, however the origin of these messages is unknown. They are produced when executing the following files:

05-Tables.sql:

INSERT INTO REFERENCES (LOGGER_ID, APPENDER_REF_ID)
  VALUES (0, 1);
RUNSTATS ON TABLE LOGDATA.CONF_APPENDERS ON ALL COLUMNS AND INDEXES ALL;

30-Log.sql

ALTER MODULE LOGGER ADD
  PROCEDURE LOG (
  IN LOG_ID ANCHOR LOGDATA.CONF_LOGGERS.LOGGER_ID DEFAULT 0,
  IN LEV_ID ANCHOR LOGDATA.LEVELS.LEVEL_ID DEFAULT DEFAULT_LEVEL,
  IN MESSAGE ANCHOR MESSAGE
  )

• References cursor.
• GET_LOGGER_DATA function.

50-AdminBody.sql

ALTER MODULE LOGADMIN ADD
  PROCEDURE RESET_TABLES (
  )

• Delete at LOGDATA.CONF_APPENDERS, LOGDATA.APPENDERS, LOGDATA.LEVELS.
• Insert at LOGDATA.REFERENCES.

Time travel

• Context:

  • Log4db2 allows to write any kind of message in the logs, and it can be part of a trigger.
  • Time Travel is a DB2 feature that saves the previous values of a table.
  • How to deal with temporal data (not temporary.)

• Question:

  • If log4db2 is configured inside a trigger, in a way that the utility records every change on the table (inserts, updates, deletes), what is the advantage of using log4db2 instead of Time Travel?

• Answer:

  • The temporal data feature, specifically when using Time Travel - System Time, writes the changes of the row (previous values), but this feature does not keep information about the context of the modification.

With log4db2 is possible to record not only the values on the row but also the context values of the modification, such as the user who performed the change, the machine where the sentence was issued, etc. Also log4db2 could be configured to record only context information.

In addition, the data is written by Time Travel - System Time persists on "important" tables. On the other hand, the data stored by log4db2 is written as logs, and logs are always considered deletable if necessary; this means that if they are removed they will not impact the business history.

The conclusion is to use the best of both worlds. On one side, with Time Travel - System Time you can issue queries to get previous values at a specific time, and the information retrieved could be sensitive, therefore it is better if this information is stored in business tables. On the other side, log4db2 will store information only about the environment, it will not contain any business information, and in this way, the logs can be deleted without a problem.

Performance - Deterministic

You can improve the performance of the utility if many of the functions are changed from NOT DETERMINISTIC to DETERMINISTIC. However, the configuration cannot be changed dynamically which in most the cases is not a problem. The functions that can be changed to DETERMINISTIC are clearly indicated in the code.