From: sfc@tpghq.com (Steve Caswell)
Subject: Re: Verifying DB integrity
Organization: Palmer & Associates, Inc., Norcross, GA
Distribution: usa
Date: Wed, 16 Dec 1992 16:02:48 GMT

In article <1992Dec15.152656.1@gsbacd.uchicago.edu> cs_mj@gsbacd.uchicago.edu (Mark Jaeger) writes:
>In article <1992Dec8.171609.19032@unet.net.com>, nowacki@porsche.net.com 
>(Michael Nowacki I.T. Database Contractor) writes:
>> How do you, as a DBA, validate the integrity of production databases
>> for which you are responsibile?
>> 
>> Have you ever been required to be able to pro-actively verify that no internal
>> file structures, such as index trees, are corrupt?
>> 
>> I have aquired experience with DEC's Rdb/VMS in the last 2 years. Rdb has a
>> verify utility that allows you to selectively read various parts of the db's
>> internal structures in order to conclusively verify that formats are valid
>> and pointers are pointing to the correct objects.
>> 
>> See the last paragraph of pg 15-6 of the v6.4 DBA guide for Ingres' statement
>> on this subject. To summarize, "you're on your own".
>> 
>> I really liked being able to effectively guarantee that the database in the
>> nightly checkpoint was (NOT)garbage...  My preferrence
>> is to require application developers to include an integrity checker utility
>> as part of the app...
>
>I've tried to think about this problem on occasion, but it seems to me
>to be too daunting a task to come up with a complete solution.  It is an
>interesting question, though.
>
...lines ommitted...
>
>
>o Verify the integrity of the tables you create.  As Michael suggested,
>the programmer who designed the tables ought to write this, as he/she
>knows what the data in the tables is _supposed_ to look like.  This also
>could be implemented in an automated fashion, but would require some
>kind of data dictionary.
>
>The last step has several layers as well: the integrity of the data in
>each individual column (right range, domain, etc.); column-to-column
>checks (e.g., is the prefix for females one of Ms., Miss, or Mrs.?); and
>inter-table checks.
>
>I didn't see any responses to the original posting.  I'd be interested
>to see what all those other dba types out there think about this general
>problem, and how you solve it in practice.  As I said, we don't do
>anything proactive, not because we don't think it's a good idea, but
>rather because the pressure of getting applications out the door has
>precluded our spending any time on it.

We do what Mark suggested in the point above.  Typically the designer of the
database or the application that affects particular tables writes a set of
SQL statements that perform various checks on the data looking for integrity
problems (i.e., orphan records).  As Mark points out, it would be hard for
INGRES to do this since it doesn't really know anything about relationships
between tables.

>
>--Mark Jaeger				internet: cs_mj@gsbvax.uchicago.edu
>Graduate School of Business		yellnet:  (312) 702-0328
>University of Chicago			faxnet:   (312) 702-0233
>Disclaimer: My opinions are my own and not those of my employer.
>Ich bin ein Virus.  Mach' mit und kopiere mich in Deine .signature.
>


-- 

Steve Caswell           |   (404) 448-7727    |  "The opinions expressed are my
Principal Consultant    |   sfc@tpghq.com     |   own.  They may not be perfect,
The Palmer Group        |   uunet!tpghq!sfc   |   but they're all I've got."