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.

asked 29 Dec '15, 12:26

Justin%20Willey's gravatar image

Justin Willey
7.6k137179249
accept rate: 20%

1

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

(29 Dec '15, 12:49) Mark Culp
Replies hidden

its the v16 docs I'm looking at.

(29 Dec '15, 12:52) Justin Willey

That may have been possibly answered by John The Wise here:

IIRC, there was also common agreement that the docs are not that clear w.r.t. these details...)

permanent link

answered 29 Dec '15, 12:41

Volker%20Barth's gravatar image

Volker Barth
40.0k361549819
accept rate: 34%

converted 29 Dec '15, 13:17

Justin%20Willey's gravatar image

Justin Willey
7.6k137179249

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.

(29 Dec '15, 12:54) Justin Willey
1

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.

(29 Dec '15, 13:25) Justin Willey
Replies hidden

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? :)

(30 Dec '15, 03:48) Volker Barth

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

permanent link

answered 29 Dec '15, 13:34

Mark%20Culp's gravatar image

Mark Culp
24.9k10139297
accept rate: 41%

1

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 :)

(29 Dec '15, 13:47) Justin Willey
Replies hidden

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

(29 Dec '15, 15:32) Mark Culp
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×260
×30
×11
×2

question asked: 29 Dec '15, 12:26

question was seen: 2,437 times

last updated: 30 Dec '15, 03:48