When a trigger is added to a physical file, three attributes need to be defined. The first is the event that will cause the trigger to fire. A trigger event can be either an insert, an update, or a delete of a record from the file. The second attribute to define is when to fire the trigger — before or after the event. Finally, the third attribute to define is the identification of the trigger program to be run. The trigger program is a user-supplied program that can be written in any HLL supported by the AS/400.

From the above, we can infer that up to six triggers can be defined for each physical file. For each update, insert, and delete, two triggers can be defined — one that runs before the event and one that runs after the event. These triggers are added using the ADDPFTRG (Add Physical File Trigger) command and can be removed with the RMVPFTRG (Remove Physical File Trigger) command.

Referential Integrity

In the real world, data in one physical file is dependent on data in another file. If a program updates one file without regard to the other, the integrity of the data may be compromised. It is often the responsibility of the application program to ensure that the dependencies are handled. Referential integrity is a facility built into the AS/400 database that removes this responsibility from the application programs.

Referential integrity allows data consistency to be maintained across two physical files. It allows rules or constraints to be defined on one physical file to ensure that every record has a corresponding or matching record in the second file. A program will be prevented from modifying a record if doing so would violate the rules.

As a simple example, suppose we have a customer master file that contains a record for each of our customers. The customer ID is used as a key for this file. Within the database, there are other files that also use the customer ID as a key. We might want to add a referential constraint to each of these dependent files to ensure that no application program is allowed to add a customer ID to a file unless the ID already exists in the customer master file. Obviously, we could create far more complex scenarios to ensure database integrity.

High-Availability Disk Systems

Disks are mechanical devices, and mechanical devices can fail. The standard form of protection for any computer system is to periodically save the data on disks to some other media, usually tape. This backup copy contains a snapshot of the database, or part of the database, at some instant in time. In case there is a problem with the data on the disk, the copy of the data on the tape can be used to restore the lost information. Earlier, we discussed forward recovery of the database using a journal. The first step was to restore the backup copy of the data. Then the journal entries from the time the backup was made are applied until the database has been recovered.

The AS/400 provides extensive save/restore facilities. Sometimes, however, the time required to restore lost data if a disk fails is unacceptable for a business. Usually, the system is unavailable to the user during the restore operation. This can be a major problem if the disk has to be physically replaced before the data can be restored. An alternative is to have a disk subsystem that can tolerate a disk failure without causing the system to become unavailable. The AS/400 supports two types of high-availability disk protection. The first uses disk mirroring, and the second uses disk arrays.

Disk mirroring requires that disk drives are paired together to provide total redundancy. Whenever the system issues a disk write, the data is written to both disks in the pair. All the data on one disk is duplicated on the second disk. If one of the disks should fail, the data is available from the second disk; the system can continue to run, unless the second disk also fails. To guarantee an even higher level of availability, the disks in the pair can be attached to separate disk controllers, on separate I/O processors, and with separate buses. By attaching the mirrored disk units to an optical I/O bus, you can keep these disks in a remote room for even greater physical protection. Chapter 10 describes the details of the AS/400’s I/O structure, and how these components interact with one another.

Mirroring provides the highest level of availability; but it is expensive, because it requires total duplication of the disks. Another approach involves the use of disk arrays. With disk arrays, disks are grouped into sets and data is written to all the disks in the set. A sector is a fixed block of data on the disk. A page in memory is usually stored in multiple sectors on the disk, and a write operation spreads the data across the disks in the set.

Adding a redundant disk to the array offers the opportunity to discover a failed disk and automatically recover the lost information. This technology is known as redundant arrays of inexpensive disks (RAID). The technique used for RAID technology is to perform an exclusive or (XOR) operation on the data in all sectors in the set.

Anyone who has played with this binary operator knows that after two operands are XORed together, either of the operands can be re-created by simply XORing the result and the other operand. Figure 6.3 shows an example of such an XOR operation. The XOR operation yields a value of true (i.e., a 1) if — and only if — one of the operands is true (i.e., is a 1) and the other is false (i.e., a 0). Otherwise, if both are true or both are false, this operation yields a value of false (i.e., a 0), as this example illustrates.

Figure 6.3  An Example of XOR Operation

The data stored in each sector of a disk is XORed with the data in the corresponding sectors on all the other disks in the set. The result of the XOR operation is stored in a sector on the redundant disk. If a disk failure should occur, the data on a failed sector can be recovered by XORing the data in the corresponding sectors of all the good disks in the set. To prevent any one disk in the set from being overused, a different disk is used as the redundant disk for different sets of sectors. Thus, any given disk in the set contains some database data and some of the XOR results.

Data integrity, recovery, and availability are major topics for any computer system. We have just touched on some of the facilities the AS/400 provides to ensure database integrity and recovery from failures. Our discussion has not been all-inclusive, but we have covered the highlights of this support.

Other Database Functions

DB2/400 includes several additional functions to enhance the AS/400’s usefulness in both a client/server and a distributed-database environment. Other functions enhance database performance. We look at some of the more important ones in this section.