May 04, 2010

Using DB2 RI

Using DB2 enforced referential integrity (DB2 RI) can be a good choice, but it depends on what you're trying to accomplish. You'd typically avoid defining DB2 RI on a table that's linked to a code table (e.g., state code). That's because for every row you'd insert, a read request would be performed to ensure that you're inserting with valid state code. In this instance, application RI would be a more efficient design -- you'd allow the program validate to the state code once and then perform the inserts for all rows that use that code. This can dramatically reduce CPU and getpages.

On the other hand, if you're inserting a row into an invoice table, you want to make sure the parent order stays intact. And if you delete the order, you want to make sure the invoice gets deleted as well. It's in these situations that I prefer DB2 RI.

Once you choose DB2 RI, you must decide whether to use DELETE CASCADE or DELETE RESTRICT. With DELETE CASCADE, you can delete the parent ORDER row, and DB2 will delete the child INVOICE row(s) for you. DELETE RESTRICT requires that you delete the child rows in INVOICE before deleting the parent rows in ORDER.

While DELETE CASCADE offers greater convenience, DELETE RESTRICT gives you control over the size of the logical unit of work by issuing a commit. If hundreds of rows are being deleted for each parent row, you can delete the children rows first and issue a commit every few hundred rows to avoid a long rollback if something goes wrong. DELETE CASCADE is much easier to use, but you must be careful with it. DETELE CASCADE can lead to lock escalation, dead lock and timeouts, and, in some cases, long rollback times when a deadlock situation occurs.

With either DB2 RI option, you must ensure that the table indexes support the referential integrity. I can't tell you how shops I've seen incur performance problems because they didn't have the proper indexes defined to support the RI.

Next week I'll provide some sample SQL that reports on missing supporting indexes for foreign key relationships.