The command-line Validation utility dbvalid validates the indexes and keys on some or all of the SQL Anywhere tables in the catalog store. The Validation utility scans the entire table and looks up each record in every index and key defined on the table. By default, the Validation utility uses the express check option.
The dbvalid utility lets you easily
validate SQL Anywhere catalog store tables, but does not validate
IQ tables. Use the IQ stored procedure sp_iqcheckdb to
validate IQ tables.
You can access the dbvalid utility at the system command-line level, which is useful for incorporating dbvalid into batch or command files.
dbvalid [ options ] [object-name,... ]
Table 3-10 lists the options available for the dbvalid utility.
Option |
Description |
|---|---|
object-name |
The name of a table or (if -i is used) an index to validate |
-c “keyword=value; ...” |
Supply database connection parameters |
-o filename |
Log output messages to a file |
-f |
Validate tables with full check |
-fd |
Validate tables with data check |
-fi |
Validate tables with index check |
-fx |
Validate tables with express check |
-i |
Each object-name is an index |
-q |
Operate quietly—do not print messages |
-s |
Validate database pages using checksums |
-t |
Each object-name is a table |
With the dbvalid command-line utility, you can validate the indexes and keys on some or all of the SQL Anywhere tables in the catalog store. dbvalid scans the entire table and confirms that each row exists in the appropriate indexes. It is the same as running the SQL Anywhere VALIDATE TABLE statement on each catalog store table.
VALIDATE TABLE is not supported in Sybase IQ. sp_iqcheckdb provides a
similar function for IQ store tables.
By default, the Validation utility uses the express check option. However, the express check option is not used if you specify -f, -fd, -fi, -fn, or -i.
If the catalog store table is inconsistent, dbvalid reports an error. If errors are reported, you can drop all of the indexes and keys on a table and re-create them. You must also recreate any foreign keys to the table.
WARNING! Validate a table or entire catalog store only when no connections are making changes to the database; otherwise, spurious errors may be reported, indicating some form of database inconsistency even though no inconsistency actually exists.
Program exit code |
Description |
|---|---|
0 |
Database validated successfully |
1 |
General failure in utility |
2 |
Error validating database |
7 |
Cannot find database to connect to (database name is wrong) |
8 |
Cannot connect to database (user ID/password is wrong) |
11 |
Cannot find server to connect to (server name is wrong) |
12 |
Incorrect encryption key for starting database |
Connection parameters (-c) Supply database connection parameters. See “Connection parameters” in Chapter 4, “Connection and Communication Parameters” in the System Administration Guide: Volume 1. The user ID must have DBA authority or REMOTE DBA authority.
For example, the following validates the iqdemo database, connecting as user DBA with password sql:
dbvalid -c “uid=DBA;pwd=sql;dbf-c:\sybase\IQ-15_1\demo\iqdemo.db”
Full check for each table (-f) In addition to the default validation checks, carry out both data checks (-fd) and index checks (-fi). This option corresponds to the WITH FULL CHECK option of the SQL Anywhere VALIDATE TABLE statement. Depending on the contents of your catalog store, this option may significantly extend the time required to validate.
Data check for each table (-fd) In addition to the default validation checks, check that all of each LONG BINARY, LONG VARCHAR, TEXT or IMAGE data type can be read. Entries with these data types may span more than one page. In the IQ catalog store:
Domain – user-defined data type.
IMAGE – a domain to LONG BINARY.
TEXT – a domain to LONG VARCHAR.
This option instructs the database server to check all pages used by each entry. This corresponds to the WITH DATA CHECK option on the SQL Anywhere VALIDATE TABLE statement. Depending on the contents of your catalog store, this option may significantly extend the time required to validate.
Index check for each table (-fi) In addition to the default validation checks, validate each index on the table. This corresponds to the WITH INDEX CHECK option of the SQL Anywhere VALIDATE TABLE statement. Depending on the contents of your catalog store, this option may significantly extend the time required to validate.
Express check for each table (-fx) In addition to the default and data checks, check that the number of rows in the table matches the number of entries in the index. This corresponds to the WITH EXPRESS CHECK of the SQL Anywhere VALIDATE TABLE statement. This option does not perform individual index lookups for each row.
Validate specified indexes (-i) Instead of validating tables, validate indexes. Ensure that every row referenced in the index actually exists in the table. For foreign-key indexes, -i also ensures that the corresponding row exists in the primary table. If you supply a table-name instead of an index-name, validates the primary key index. In this case, for dbvalid, each of the object-name values supplied represents an index instead of a table and has a name in the following format:
[ [ owner.]table-name.]index-name
Must be the owner of the table on which the index is created, have DBA authority, or have REMOTE DBA authority.
Log output messages to file (-o) Write output messages to the named file.
Operate quietly (-q) Do not display output messages.
Validate database using page checksums (-s) Checksums are used to determine whether a database page has been modified on disk. If you created a database with checksums enabled, you can validate the catalog store using checksums. Checksum validation reads each page of the catalog store from disk and calculates its checksum. If the calculated checksum is different from the checksum stored on the page, the page has been modified on disk and an error is returned. The page numbers of any invalid catalog store pages appear in the server messages window. You cannot use -s in conjunction with -i, -t, or any of the -f options.
Validate tables (-t) The list of object-name values is a list of tables. This is also the default behavior.
The following command validates the catalog store of the iqdemo database, connecting as user DBA with password sql:
dbvalid -c “uid=DBA;pwd=sql;dbf-c:\sybase\IQ-15_1\demo\iqdemo.db”
“sa_validate system procedure” in Chapter 7, “System Procedures” in Reference: Building Blocks, Tables, and Procedures
“sp_iqcheckdb procedure” in Chapter 7, “System Procedures” in Reference: Building Blocks, Tables, and Procedures
“Database verification” in Chapter 13, “System Recovery and Database Repair” in the System Administration Guide: Volume 2
