May 17, 2010

More Info on DB2 RI

The previous two weeks I've written about DB2 referential integrity (RI). My good friend Suresh Sane posted a comment to clarify my May 4 post on using RI.

Suresh also cites an IBM Redbook that he co-authored, "Data Integrity with DB2 for z/OS."

The major chapters in this publication are:

1. Introduction -- A quick review of the integrity and security built into the z/OS platform and DB2.

2. Semantic integrity -- A complete description of constraints and distinct types, the two sets of DB2 functions that help control the integrity of your DB2 data.

3. Referential integrity -- A discussion of referential constraints, RI relational model, design recommendations and much more.
4. Triggers -- A look at how triggers are used to enforce data integrity.
5. Other integrity features -- A discussion of other DB2 V8 features, including informational RI, Sequence objects and locking.
6. Recovery -- Learn the characteristics of recoverability of DB2 applications by analyzing the different recoverability functions. Topics covered include DB2 commit process, unit of work, data integrity, DB2 subsystem restart after abend, recovery of objects in error, application recovery process and point in time recovery.

Incidentally, when I read the chapter on recovery it reminded me of some situations I've encountered during my consulting years. Far too many companies do not have procedures in place to recover their data. I've actually been called into help companies retrieve their lost data. Once, I asked a DBA why his company didn't do its own recovery. Turns out they'd never done one, and the production system housed critical data that could not be reproduced.

My point is you can't afford to wait until you have a problem to figure out how to handle it. This Redbook has some good information that can help you design and implement a recovery plan. Then you can do what I like to call a recovery fire drill: Just stop a table space and delete the dataset, then step through your procedures to ensure it's recovered and in sync with all the RI related tables. You can and should "practice" recovery at least once or twice a year.