dbvalid.exe options

[MarkCulp]

I'm trying to sort out exactly what validation methods are used with the different switches for dbvalid.exe

The docs say that by default "dbvalid validates all the tables, materialized views, and indexes in the database and validates the database file structure". And if run just using -c & -o parameters the output logs "VALIDATE DATABASE", followed by "VALIDATE TABLE ...." for each table. No mention is made of indexes etc but presumably these are done with each table.

If the -s switch is used then the output says "VALIDATE CHECKSUM".

Is validating of checksums in included in VALIDATE DATABASE or should one do both separately? I think it is included as that is how the docs on the SQL statement VALIDATE DATABASE read - but the dbvalid docs aren't clear that it is just executing the sql statements.

A cross reference table showing how the different options in Sybase Central Wizard, the SQL statements and the dbvalid.exe utility all tie in together would be really handy.

[MarkCulp]

Which version are you using? The way validate works has change across the versions!

[justin_willey]

its the v16 docs I'm looking at.

[MarkCulp]

Oh, where to start?!

Short answer is: Yes, indexes are checked by default (see below).

First let's talk about checksum validation. dbvalid does not let you combine checksum validation (-s option) with any of the other options. The reason for this is that checksum validation does a sequential scan of the entire database - i.e. for each file read each page - and the "process" of reading each page verifies that the checksum on the page (if there is a checksum on the page) is the correct value.

If you run dbvalid with no "what do I do" options (i.e. -d -i -s -t or -fx) then the default is to do table validation (-t option). Note: remember I am talking about v16 here. If table validation is selected and no table names are specified then all tables in the database are validated in sequence. Note: By checking all tables in the database the "structure" of the database is checked. Also note that "tables" in the SQLA (internal) sense includes materialized views since the underlying mat view is stored in a table. In v16 a table validation first computes a row-order-independent hash of the entire table (by doing a sequential table scan of every row) and then compares the result against a similarly computed value when doing an index scan, and this comparison is done for every index defined on the table. Note that this method is fast but if the computed results differ it does not indicate which row(s) differ.

An index scan (dbvalid -i option) does a similar operation as describe above (for table validation) but does it for only the specified index.

HTH

[justin_willey]

Many thanks Mark - I think I'm clear now.

I see that v17 introduces a snapshot isolation mode for dbvalid that looks really useful.

The structure of the various help articles in v17 is still much the same, I wonder if the doc team could be persuaded to link them together so it's clear how the options in the various methods all interrelate. This seems to be a topic that comes up a fair amount 🙂

[MarkCulp]

I have sent a message to the doc team. Feel free to post a comment on dcx.sap.com as well.