From: cs_mj@gsbacd.uchicago.edu (Mark Jaeger)
Subject: Re: Verifying DB integrity
Organization: 
Distribution: usa
Date: Tue, 15 Dec 1992 21:26:56 GMT

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.

The statement that "you are on your own" is only partially true.  INGRES
has the "verifydb" command with the "table" operation, and it does do
_some_ checking.  Warning: Only do this in "report" mode!!!

The "verifydb" utility tries to verify the integrity of the index
structure of a table or secondary index.  The report can be rather
volumninous, and the fact that _all_ reports go to a single file in the
INGRES directory tree is not entirely satisfactory, but it's better than
nothing.

In my personal experience, I have used "verifydb" only on those
occasions where I knew there was a problem with a table because of other
problems (server crashed, or server reported table corruption in the
error log).  But I suppose one could use it proactively as well.

It seems to me there are several levels of checking:

o Verify that the server can select the data from each table.  This is
what the DBA Guide suggests.  It would be very easy to use the report
writer to generate a script to do "select * from x", where x ranges over
all tables and secondary indexes, excluding views.  Depending on the
size of the database, this could take a _very_ long time to execute,
and could consume significant system resources (disk i/o,
front-end/back-end communication).

o Verify the internal integrity of each individual table and secondary
index with the "verifydb" command.

o Verify the integrity of each secondary index vis-a-vis its base table.
This, too, could be automated using the INGRES system catalogs.

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.

--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.

Newsgroups: comp.databases.ingres