Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.0 » SQL Anywhere Server - Database Administration » Administering Your Database » Database administration utilities


Validation utility (dbvalid)

Validates the indexes and keys on some or all of the tables and materialized views in a database.

dbvalid [ options ] [ object-name, ... ]
Option Description

Read in options from the specified environment variable or configuration file. See Using 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; ..."

Specify database connection parameters. For a description of 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:

dbvalid -c "UID=DBA;PWD=sql;DBF=c:\salesdata.db"
-d Validate that all table pages in the database belong to the correct object, and perform a checksum validation. The -d option does not include validation of data or indexes. The -d option cannot be used with the -i, -s, or -t options.
-fx Validate every row of the table, and make sure that the number of rows in the table matches the number of rows in each index associated with the table. This option does not perform individual index lookups for each row. Using this option can significantly improve performance when validating large databases with a small cache.
-i Validate the specified index.
-o filename Write output messages to the named file.
-q Do not display output messages to the client. You can still log the messages to file using the -o option, however.
-s Validate 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 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 either of the -f options.

Specify a list of object-name values, which represents a list of tables and materialized views.


Specify the name of the table or materialized view to validate.

If -i is used, object-name refers to an index to validate instead.


By default, dbvalid validates all the tables, materialized views, and indexes, in the database, and validates the database itself.

With 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 that all table pages in the database belong to the correct object, and that page checksums are correct. By default, dbvalid validates all the tables and materialized views in the database (the same behavior as the -t option).

For each table or materialized view, the Validation utility scans the entire object, and then looks up each record in every index and key defined on the table. You can also use the Validation utility to verify that all table pages in the database belong to the correct object, and that page checksums are correct. To run the Validation utility, you must have either DBA or VALIDATE authority.

You can also access the Validation utility in the following ways:

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 the 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 autostarts a database, the database starts in read-only mode. This prevents changes from being made to the database in case 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 connection parameter [DBS].

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 checksums enabled. This is because the database server automatically still calculates checksums for critical database pages, regardless of whether checksums are enabled. 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 Using checksums.

Validation requires exclusive access to each table. 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). See Software component exit codes.

For more information about specific checks made during validation, see VALIDATE statement.