4.10 Referential Integrity
In a database user environment, there are frequent cases where the data in one file is dependent upon the
data in another file. Without support from the database management system, each application program
that updates, deletes or adds new records to the files must contain code that enforces the data dependency
rules between the files. Referential Integrity (RI) is the mechanism supported by DB2 that offers its users
the ability to enforce these rules without specifically coding them in their application(s). The data
dependency rules are implemented as referential constraints via either CL commands or SQL statements
that are available for adding, removing and changing these constraints.
For those customers that have implemented application checking to maintain integrity of data among
files, there may be a noticeable performance gain when they change the application to use the referential
integrity support. The amount of improvement depends on the extent of checking in the existing
application. Also, the performance gain when using RI may be greater if the application currently uses
SQL statements instead of HLL native database support to enforce data dependency rules.
When implementing RI constraints, customers need to consider which data dependencies are the most
commonly enforced in their applications. The customer may then want to consider changing one or more
of these dependencies to determine the level of performance improvement prior to a full scale
implementation of all data dependencies via RI constraints.
For more information on Referential Integrity see the chapter
Ensuring Data Integrity with Referential
Constraints
in
DB2 Universal Database for System i Database Programming
manual
and the redbook
Advanced Functions and Administration on DB2 Universal Database for System i
.
4.11 Triggers
Trigger support for DB2 allows a user to define triggers (user written programs) to be called when records
in a file are changed. Triggers can be used to enforce consistent implementation of business rules for
database files without having to add the rule checking in all applications that are accessing the files. By
doing this, when the business rules change, the user only has to change the trigger program.
There are three different types of events in the context of trigger programs: insert, update and delete.
Separate triggers can be defined for each type of event. Triggers can also be defined to be called before or
after the event occurs.
Generally, the impact to performance from applying triggers on the same system for files opened without
commitment control is relatively low. However, when the file(s) are under commitment control, applying
triggers can result in a significant impact to performance.
Triggers are particularly useful in a client server environment. By defining triggers on selected files on
the server, the client application can cause synchronized, systematic update actions to related files on the
server with a single request. Doing this can significantly reduce communications traffic and thus provide
noticeably better performance both in terms of response time and CPU. This is true whether or not the file
is under commitment control.
IBM i 6.1 Performance Capabilities Reference - January/April/October 2008
©
Copyright IBM Corp. 2008
Chapter 4 - DB2 Performance
58
