Journaling Grows Up for Smaller Systems

Large IT shops with sophisticated staffs have many options for supporting disk and file maintenance. The AS/400 has many great backup facilities built in. First it rarely goes down to the point where you need them, and second if they do, you have pretty good confidence that you can restore to where you last backed up. Unlike NT based systems, if you ever did have to restore the 400, the odds are you will be able to recover to the point of the last backup. However that may not be good enough. Even losing a day can be expensive. We have spent years trying to get people to enter orders and transactions directly into our system, which means that we do not have the paper orders as a backup.

For a long time, we have had a great facility in Journaling and Commitment control. The problem has been that it was difficult to manage, and required a lot of "baby sitting". What this meant is that most software vendors, and most shops just did not use the facility. We have, in the last few releases, been given tools to greatly reduce the complexity and improve the support for this database utility.

You can now setup the journaling process so that the system manages it for you for the most part. This greatly reduces the operations effort required to use journaling. If you are not familiar with journaling, it is the system process of tracking the changes made to a database table. There are two basic purposes for doing this. First journaling can be used to get current changes logged in case a recovery is needed. Second, journaling can be used to help in problem determination (who changed what when, etc). Additionally, you might use journaling to keep two systems in sync, or you might use it to update a reporting system (like a data warehouse) or PC laptops with selected changed data.

If you are journaling your database, and you lose a disk drive, you might be able to recover up to the point of the disk failure. This requires that the journals are stored either in their own ASP or in a partition different from the original data that the journal is tracking. It does make the restore a little more complicated in that there is an additional step to restore the journals to the database, but it gives you much more current data.

A few things to watch out for if you are going to use the journals for recovery. The first is to make sure the journals are stored on physical disks isolated from the data. Another thing that frequently gets forgotten is that you need to journal everything that changes. If I track just the order file as an example, then all the data that is updated from the order file (allocation, balances, etc) would not be correct if I restored just that journal.

It is important if you are going to start journaling that you have someone who understands the impact of journaling, and the database (and application) that you are running. Once setup though, it is a wonderful tool, which gives you comfort as well as flexibility in your recovery options. It is also important that you set the journals and receivers up so that the system manages them for you. That is unless you are going to watch them manually.

If you do not, the journals will take up disk space very quickly. You want them to reach a certain size, and then roll to a new one. Then when you do a system backup, the journals are removed from the system automatically freeing up that space. If you do not have the system do this automatically, you will have to. You will be surprised how fast these journals fill up the disk, especially if you track the before and after image of all the records that are changed.

The system also has another very nice utility available that many shops do not know exists. That is the System Audit journal. Creating a journal in QSYS called QAUDJRN, and turning on a value for the system value QAUDCTL turns this on. Some of the things this journal can show you includes: who changed an object, deleted an object, used an object, authority failures, and system value changes. The command DSPAUDJRN gives you the option to select these various records. This can be invaluable in watching what is going on in the system.

Journaling is a very powerful tool that has changed a lot over the last few releases, making it much easier to manage and use. It does require some thought in setting up based on your goal for journaling, but it can provide many benefits if used correctly. More importantly, it could save your system in case of a disaster.

John Bussert is president of Swift Technologies (Marengo, Ill.), a company specializing in AS/400 and Windows NT software.

Related Editorial:

  • Database Design for New Capabilities