Validates the indexes and keys on some or all the tables and materialized views in a database.
Syntaxdbvalid [ options ] [ object-name, ... ]| Option | Description | ||
|---|---|---|---|
| @data |
Reads in options from the specified environment variable or configuration file. See Configuration files. If you want to protect passwords or other information in the configuration file, you can use the File Hiding utility to obfuscate the contents of the configuration file. See File Hiding utility (dbfhide). |
||
| -c "keyword=value; ..." |
Specifies database connection parameters. For information about the connection parameters, see Connection parameters. The user ID must have DBA authority or VALIDATE authority. For example, the following command validates the database, including all tables and materialized views for c:\salesdata.db, connecting as user DBA with password sql:
|
||
| -d |
Validates that all pages in the database belong to the correct object, and performs a checksum validation. The -d option validates the correctness of indexes. The -d option cannot be used with the -i, -s, or -t options. See VALIDATE statement. |
||
| -fx | Disables primary key/foreign key referential integrity checking. | ||
| -i | Defines object-name as a list of indexes. | ||
| -o filename | Writes output messages to the named file. | ||
| -q | Does not display output messages to the client. You can still log the messages to file using the -o option, however. | ||
| -s | Validates the database using checksums. Checksums are used to determine whether a database page has been modified on disk. Checksum validation reads each page of the database from disk and calculates its checksum, if the page has a 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 pages appear in the database server messages window. The -s option cannot be used in conjunction with -d, -i, -t, or the -fx options. | ||
| -t |
Defines object-name as a list of tables and materialized views. |
||
| object-name |
Specifies the name of the table or materialized view to validate. If -i is used, object-name refers to an index to validate instead. |
RemarksWith the Validation utility, you can validate the indexes and keys on some, or all, of the tables and materialized views in a database. You can also use the Validation utility to verify the database file structure to ensure that all pages in the database belong to the correct object, and that page checksums are correct. By default, dbvalid validates all the tables, materialized views, and indexes in the database and validates the database file structure.
When validating a table, dbvalid also validates all of the table's indexes to verify that the set of rows and values in the table matches the set of rows and values contained in each index. All BLOBs in the table are also traversed, BLOB allocation maps are verified and orphaned BLOBs are detected. The Validation utility also checks the physical structure of all index pages, the ordering of the index hash values, and the index's uniqueness requirements (if any are specified). Unless the -fx option is specified, each foreign key value is looked up in the corresponding primary key table to check that referential integrity constraints are intact.
When the -i option is specified, dbvalid validates each index in the object-list. Validating an index works exactly the same as validating a table, except that only the specified index and its underlying table are validated. If the index is a foreign key, each value is looked up in the primary key table unless the -fx option is specified.
If you start database validation while the database cleaner is running, the validation does not run until the cleaner is finished running. See sa_clean_database system procedure.
To run the Validation utility, you must have either DBA or VALIDATE authority.
You can also access the Validation utility in the following ways:
From Sybase Central, using the Validate Database Wizard. See Validating a database.
From Interactive SQL, using the VALIDATE statement. See VALIDATE statement.
The Validation utility can be used in combination with regular backups to give you confidence in the integrity of the data in your database. If you want to validate a backup copy of your database, it is recommended that you make a copy of the backup and validate the copy. Doing this ensures that you do not make changes to the file that is used in recovery. See Backup and data recovery.
Backup copies of the database and transaction log must not be changed in any way. If there were no transactions in progress during the backup, or if you specified BACKUP DATABASE WITH CHECKPOINT LOG RECOVER or WITH CHECKPOINT LOG NO COPY, you can check the validity of the backup database using read-only mode or by validating a copy of the backup database.
However, if transactions were in progress, or if you specified BACKUP DATABASE WITH CHECKPOINT LOG COPY, the database server must perform recovery on the database when you start it. Recovery modifies the backup copy, which prevents subsequent transaction log files from the original database from being applied.
If running the Validation utility starts a database automatically, the database starts in read-only mode. This behavior prevents changes from being made to the database if the validation is part of a backup or recovery plan.
If the Validation utility connects to a running database that was not started in read-only mode, the utility displays a warning. This warning is a reminder that the database being validated cannot be used as part of a recovery plan. Because of the way backups are performed, most databases created by dbbackup are marked as needing recovery. If the database you are validating requires recovery and you want to force it to start as read-write, you can either start the database before running dbvalid or specify a valid value for the DBS connection parameter. See DatabaseSwitches (DBS) connection parameter.
Both of the following commands allow dbvalid to run if the mycopy.db database needs to be recovered:
dbvalid -c "UID=DBA;PWD=sql;DBF=mycopy.db;DBS=-n mycopy" |
dbvalid -c "UID=DBA;PWD=sql;DBF=mycopy.db;DBS=-dh" |
Validating a table or an entire database should be performed while no connections are making changes to the database; otherwise, errors may be reported indicating some form of database corruption even though no corruption actually exists.
The Validation utility may return warnings about checksum violations for databases that do not have global checksums enabled. This is because the database server automatically calculates checksums for critical database pages, regardless of whether checksums are enabled. A database may also have checksums on some pages because it was started with write checksums.
The database server also creates checksums automatically for databases running on Windows Mobile and for databases running on storage media that may be less reliable, such as removable drives. See Corruption detection using checksums.
Validation does not acquire exclusive access to the table being validated and may report failures if the database is modified during validation. For this reason, it is best to validate when there is no other activity on the database.
Exit codes are 0 (success) or non-zero (failure).
For more information about specific checks made during validation, see VALIDATE statement.
See also |
Discuss this page in DocCommentXchange.
|
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 |