Journal management for distributed relational databases

IBM® i journal support provides an audit trail and forward and backward recovery. Forward recovery can be used to take an older version of a table and apply changes logged in the journal to the table. Backward recovery can be used to remove changes logged in the journal from the table.

When a collection is created, a journal and an object called a journal receiver are created in the collection. Improved performance is gained when the journal receiver is on a different ASP from the tables. Placing the collection on a user ASP places the tables and journal and journal receivers all in the same user ASP. There is no gain in performance there. Creating a new journal receiver in a different ASP (used just for this journal's journal receivers) and attaching it with the Change Journal (CHGJRN) command will get the next system-generated journal receivers all in the other user ASP, and then the user will see improved performance.

When a table is created, it is automatically journaled to the journal SQL created in the collection. You are then responsible for using the journal functions to manage the journal, journal receivers, and the journaling of tables to the journal. For example, if a table is moved into a collection, no automatic change to the journaling status occurs. If a table is restored, the normal journal rules apply. That is, if a table is journaled when it is saved, it is journaled to the same journal when it is restored on that system. If the table is not journaled at the time of the save, it is not journaled at restore time. You can stop journaling on any table by using the journal functions, but doing so prevents SQL operations from running under commitment control. SQL operations can still be performed if you have specified COMMIT(*NONE), but this does not provide the same level of integrity that journaling and commitment control provide.

With journaling active, when changes are made to the database, the changes are journaled in a journal receiver before the changes are made to the database. The journal receiver always has the latest database information. All activity is journaled for a database table regardless of how the change was made.

Journal receiver entries record activity for a specific row (added, changed, or deleted), and for a table (opened, table or member saved, and so on). Each entry includes additional control information identifying the source of the activity, the user, job, program, time, and date.

The system journals some file-level changes, including moving a table and renaming a table. The system also journals member-level changes, such as initializing a physical file member, and system-level changes, such as initial program load (IPL). You can add entries to a journal receiver to identify significant events (such as the checkpoint at which information about the status of the job and the system can be journaled so that the job step can be restarted later) or to help in the recovery of applications.

For changes that affect a single row, row images are included following the control information. The image of the row after a change is made is always included. Optionally, the row image before the change is made can also be included. You control whether to journal both before and after row images or just after row images by specifying the IMAGES parameter on the Start Journal Physical File (STRJRNPF) command.

All journaled database files are automatically synchronized with the journal when the system is started (IPL time) or while varying on an independent ASP. If the system ended abnormally, or the independent ASP varied off abnormally, some database changes might be in the journal, but not yet reflected in the database itself. If that is the case, the system automatically updates the database from the journal to bring the tables up to date.

Journaling can make saving database tables easier and faster. For example, instead of saving entire tables every day, you can save the journal receivers that contain the changes to the tables. You can still save the entire tables on a regular basis. This method can reduce the amount of time it takes to perform your daily save operations.

The Display Journal (DSPJRN) command can be used to convert journal receiver entries to a database file. Such a file can be used for activity reports, audit trails, security, and program debugging.