on 2015 Dec 29 12:26 PM
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.
That may have been possibly answered by John The Wise here:
Are the default checks of sa_validate() and DBVALID identical?
How do I get the maximum amount of information from dbvalid?
IIRC, there was also common agreement that the docs are not that clear w.r.t. these details...)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
John Smirnios's comment on that second link clarifies it:
VALIDATE DATABASE visits every page and performs some other sanity checks. By virtue of reading each page, checksums are verified. So, VALIDATE DATABASE is effectively a superset of VALIDATE CHECKSUM. FWIW, VALIDATE DATABASE does its work by hauling everything through the cache but VALIDATE CHECKSUM reads directly from the file. I'm not sure why -- not my code 🙂
Thanks Volker - I hadn't seen that one. I'll try doing my own table and posting it.
The answer is to read the docs on dbvalid.exe together with the VALIDATE DATABASE page and the text in the Sybase Central wizard - all then becomes clear.
Not specifying any of -d, -s, -t or -i gives you the fullest possible validation. You can do the same thing in the Sybase Central Wizard by choosing "Validate Database Pages" together with the "Full check" option AND "Validate tables and materialized views" together with the "Normal check" option.
There doesn't seem to be a way of achieving the same thing through direct SQL statements except by running VALIDATE DATABASE and then generating a VALIDATE TABLE statement for each table in the database.
A further wrinkle is sa_validate(). With no parameters it does everything or with parameters just the tables (inc indexes) belonging to a specified user or just a specified table. There is no mention of the express check option, but looking at the code of sa_validate(), it runs the full VALIDATE TABLE.
FWIW, you might also use the DBValidate API with the VALIDATE_COMPLETE flag:
VALIDATE_COMPLETE: Perform all possible validation activities.
Isn't that what we are all asking for? 🙂
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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 🙂
User | Count |
---|---|
67 | |
11 | |
10 | |
10 | |
9 | |
8 | |
6 | |
4 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.