Database Journals in IBM i ACS

The database journal has been a feature of the IBM i OS for as long as most of us can recall with its roots in the System/38 operating system. When initially implemented, it was used to activate commitment control. This allowed a program to set a point in time and then start a series of updates. If something happened during the update that meant that the update could not continue, information captured by the journal could be used to reset the database back to what it looked like before the update was started. This could also be used to recover from a system failure, ensuring that the database would be at a logical point in time.

With the advent of the need to mirror databases across multiple systems, journaling added a new mission. It could now be used to force updates made on one system to be replicated on a different system. The IBM i OS was adapted to this by allowing a journal to be set up on a remote system. As soon as changes were made one place, the remote copy of the database could then up updated to match.

One benefit of IBM i journals from a security perspective is that once created, they cannot be tampered with. This provides you with proof positive of transaction processing that has taken place on your system. If someone changes a database and then changes it back at a later time, both events will be captured by journaling and an audit track can be established.

The IBM i journals consist of two pieces, there is the actual journal object which describes the journal and which database(s) are being processed by it. The second piece is the journal receiver which contains the actual journal records. In order to create a journal, the receiver must be created first to receive the transaction activity. As receivers are filled up, they will be detached and new receivers started so that no single receiver reaches an unwieldy size.

With the latest version of Access Client Solutions (ACS 1.1.9.0 or later), you can access journal functions on your IBM i platform using a GUI interface. To do so, start ACS and open the “Schemas” option in the “Database” section. Select your system from the list and open the Schemas list. This will show you the IBM i libraries that you have registered. On most systems, the QGPL library will already show. To register more, use the “Include” function at the top right corner of the display. Be sure that you click the OK once you’ve added your library.

Highlight your library under the list of Schemas and open the box with the plus (+) sign. This will show you all of the database options that you now have at your disposal. You can easily check on the current journal status of a database. Highlight the “All Database Objects” and a list will be shown. Look for “Table” objects, these are your database files. Highlight the table you want to check and right click your mouse. From the list of options, choose journaling and you will see whether or not the file is currently being journaled. If not, you can activate journaling from here provided that a journal already exists. A single journal can be used for multiple databases.

To create a new journal, use the Journal Receivers option to first create a receiver and then the following Journal option to create the journal. These are additional options under the current library that is open.

Once a file has journaling active, you can view journal activity from this same Schema function in ACS. Find the file in question again and highlight it. Then, right click and select the View Journal Entries option. This will bring up a list of selection options. Once the options you want have been set, press the Apply or Reset button and the contents of the journal will be shown.

While this is a vast improvement over the access provided by the green screen interface, interpreting a journal is still not perfectly clear. For that, you will need a solution like Kisco’s iFileAudit software which breaks the journal information down on a field by field bases with clear identification of the field contents from before and after the change.