Database journals are used to recover from both system failures and from database problems caused by programs. If an abnormal system termination occurs because of a hardware or software failure, the journaled database files are automatically recovered when the system is re-IPLed. These database files will be updated to reflect all activity recorded in the journal receivers. If erroneous data is entered by a user program into a physical file that is journaled, the AS/400 provides the capability to do either forward or backward recovery of the file. Forward recovery first restores a backup version of the file. Then the journal entries are applied to the file until the point in time just before the user program entered the erroneous data. Backward recovery removes the erroneous changes from the file. Backward recovery requires that the journaling write the copy of the record both before and after the change.

System-Managed Access Path Protection

One of the difficulties the AS/400 and its users have encountered in the past was long IPL times after an abnormal system termination. When an access path has been opened for the purpose of updating a file, and an abnormal termination occurs, that access path has to be rebuilt when the system is re-IPLed. Recall from Chapter 5 that we mentioned the capability to delay logical file maintenance. This means that the integrity of the logical file is exposed if the system terminates abnormally. Depending on the number and size of the keyed access paths that are open, the time required to rebuild them can be very long — several hours is not uncommon for a larger system.

As we just saw, the AS/400 offers the capability to journal files, including the logical files. If a user journals the access paths, the IPL time can be substantially reduced. The potential problem is that the user must first determine which files to journal, estimate the size of the journal receivers, and issue the commands to start the journaling. Some customers do this, but most do not. IBM designed System-Managed Access Path Protection (SMAPP) to do this journaling automatically. The system calculates the maximum amount of time it will allow for rebuilding access paths during an IPL after an abnormal termination. This maximum allowable time is used by the system to determine how much access-path journaling will be required. The user can always override this system-calculated maximum time with a larger or smaller value. The smaller the value, the more system resources will be required for the journaling. Any journaling requires a trade-off to be made between the system resources used during normal operations and the recovery time.

Once the maximum allowable time is either calculated or set by the user, the system looks at all the keyed access paths that exist in the database. It then calculates the total time required to rebuild all these access paths. If this total time exceeds the maximum allowable time, the system automatically begins to log selective access paths to guarantee that the maximum allowable time will not be exceeded.

SMAPP uses a special logging area that requires no action on the part of the user. The logging area is circular, meaning it wraps around on itself. The system always keeps enough entries in this logging area to ensure that the maximum time objective is met.

Commitment Control

With multiple users able to access and change the contents of records in a physical file, there are times when data integrity is exposed. Suppose one user fetches a record with the intention of updating a field in that record. What happens if some other user is also in the process of updating the same field in the record? If the second user changes the field after the first user has fetched it, we could have a data integrity problem. Fortunately, the database provides concurrent update protection so this won’t happen. There are, however, more complex situations involving changes to multiple records that the system does not automatically protect against.

Suppose, for example, we have a situation where changes to multiple, related records must be made at the same time. The usual example to describe this situation involves a transaction at a cash machine. A customer at the terminal initiates the transaction by inserting a cash card in the machine, keying in a security code, and selecting the type of transaction. This causes the customer record to be read from the database in the host computer. That database can be in a system across town or across the world. If the customer has requested a cash withdrawal, the record is checked to see whether there is a sufficient balance. The balance is then decremented by the amount requested, and a message is sent to the cash machine to dispense the cash. What if there is a failure, and the machine is unable to dispense the cash? Before completing this failed transaction, we would want to roll back the change to the customer’s balance. The AS/400 facility to do this is commitment control.

Because it is not normally possible to perform all changes simultaneously, the system must protect the group of related records and not release them until all changes have completed. A COMMIT operation lets the system make the changes to the group of records with what appears to be a single unit of work. If all changes cannot be made, the entire group of changes can be removed through the ROLLBACK operation. Commitment control uses journaling to accomplish these operations.

Triggers

A trigger is defined as an action that automatically occurs whenever a change is made to a physical file. Triggers provide a convenient way to tie related database activities together. They are a form of “roll-your-own” database integrity built into the file definition. Situations often occur when a change to the database, such as adding or deleting a record, requires some other action to be taken. A trigger can invoke a program to take the required action. In other situations, a change to an existing record may call for a program to be run to check the value of a field in the record. For example, an update to a record in an inventory file may cause the quantity of items in stock to fall below a predetermined value. A trigger on this file could initiate a program every time an update is made to check the value and to reorder the item from a supplier if the value is too low.