Database Logging

The Database Logging plugin in the Administrative Console (Database Admin, Utilities) allows the user to set up logging of columns in any table in the database. Entries and changes made in the Database Logging page/worksheet are not active until database triggers are recreated at a later time, usually during system downtime. Changes can be made in the worksheet without affecting the logging status. When you want to activate the changes, preferably during downtime, run the xschema process on the Application server for the subsystem to be changed. This process creates the commands needed to establish database triggers that will log any changes to the table columns selected for logging. Database logging feature can be used for a maximum of 10 columns at a time.

Navigation

Use the Entity List panel to select from any tables already configured for logging. To add a new table, go into Add ( + ) mode and select Lookup from the Source Table field drop-down menu. The Table Selection dialog opens. Tables are organized by subsystem; expand the appropriate subsystem to select the desired table. Select OK.

The main Database Logging worksheet has two panels: The header panel contains the table level fields and the Select Column panel contains the column level list.

Header

Source Table: Table to be logged.

Enabled: Enable or disable logging for the source table.

Status: Displays the current logging status of this table. Possible values are Enabled, Disabled, Changes Pending, and Disable Pending. Changes Pending and Disable Pending indicate that the user has made a change, but the triggers have not been rebuilt to make those changes effective yet.

Log Inserts/Updates/Deletes: These checkboxes indicate which database actions you wish to log. By default, Updates and Deletes are logged since Inserted data can usually be ascertained by looking at the delete and update information in conjunction with the current record itself.

Logging Table: Displays the name of the table that holds the logged information. By default, it is the original table name with _X appended to the name. The logging table has the following structure:

CREATE TABLE sourcetable_x (
tx_id CHAR(36),
at_id CHAR(18),
event_id CHAR(1),
old_value (150),
new_value (150),
rec_unique_key CHAR(36),
log_who CHAR(16),
log_when,
unique_key CHAR(36)
)

Logging Procedure: Displays the name of the stored procedure used to log changes, if any.

Select Columns

After you select a source table in the Header panel, the Select Column panel displays the columns in the source table so you can select which columns should be logged.

Column: Select this checkbox for any column to be logged.

Status: The Status displays the current logging status of the column. Possible values are blank, Enabled, Enable Pending, and Disable Pending. Enable pending means it has been selected for logging but is not currently logged. Disable pending means it is currently being logged but is marked for disabling. When the xschema/xsql process is subsequently implemented to actually make all changes active, the pending status is changed.

Create Date: The date and time when the last change was made.

It is not recommended to enable logging for the attribute column. Although the maximum column size is much larger, logging limits it to 255 characters. Enabling logging for this column may result in data truncation. For example, the HR_EOSETTING table has an attribute column size set to Max.

Activate Logging

BusinessPlus Database Logging is accomplished using database triggers.

For BusinessPlus 23.5.6.0 and later, logging is enabled when you click Save in the Admin Console Database Logging tab.

For BusinessPlus 23.5.5.0 and earlier, a pair of application server programs—xschema and xsql—are used to read the information entered into the Database Logging Worksheet and create the database triggers needed to activate the logging function.

Whenever anything needs to be done to the structure of the BusinessPlus Database, including changes to database triggers, it is normally done for a given subsystem using the xschema/xsql processes. This usually occurs during the Update Services process. In fact, the Update Services process will activate any pending changes for logging.

There are two options to activate logging without waiting for an Update Services:

• By subsystem

• Globally for all subsystems

To illustrate, suppose you have just added a Purchasing table in the logging worksheet, and have some Enabled Pending columns. To activate, you would log onto your application server using a terminal connection, and an administrative user ID, for example bsi. The following should generally be performed when the system is not in use.

One option would be to rebuild all of the triggers for the Purchasing subsystem, as follows:

cd schema
xschema xxdb (where xx = hr or py or po; in this case podb for Purchasing)
xsql xxdb.trig (where xx is the one you just built above; in this case podb.trig)

Briefly, the above steps make the current directory the app/schema directory, where the schema files exist. Then, the xschema podb takes the podb schema file as input, and outputs a number of SQL files, one of which is the podb.trig file. This file contains the SQL commands to drop and recreate all triggers for the Purchasing tables. The xsql podb.trig connects to the database and actually runs the SQL against the database.

The other option is to rebuild only the triggers for tables that have logging defined and active for them, regardless of subsystem. The steps for that option are:

cd schema
xschema –logging [table name]
xsql logging.sql

In this case, the -logging option of xschema generates only the logging.sql file, which contains the SQL needed to activate any pending logging triggers. If the table name is included, it only rebuilds triggers for that table. There is also a –logging2 option that will force rebuilding of all logging triggers, even those which are already active.

Viewing Logged Information

To display the logging history for a given record from BusinessPlus, browse to the record of interest and open the Record Information window. The Log History tab shows the logging history with the most recent changes first.