Validation utility (dbvalid)

The command-line Validation utility dbvalid validates the indexes and keys on some or all of the Adaptive Server 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 Adaptive Server 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.

Syntax

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

Parameters

Table 3-16 lists the options available for the dbvalid utility.

**Table 3-16: dbvalid options**
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

Usage

With the dbvalid command-line utility, you can validate the indexes and keys on some or all of the Adaptive Server Anywhere tables in the Catalog Store. This utility scans the entire table and confirms that each row exists in the appropriate indexes. It is equivalent to running the Adaptive Server Anywhere VALIDATE TABLE statement on each Catalog Store table.

VALIDATE TABLE is not supported in Sybase IQ. The procedure 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 recreate them. You must also recreate any foreign keys to the table.

WARNING! Validating a table or an entire Catalog Store should be performed while no connections are making changes to the database; otherwise, spurious errors may be reported indicating some form of database corruption even though no corruption actually exists.

Exit codes are:

Program Exit Code	Description
0	Database validated successfully
1	General failure in utility
2	Error validating database
7	Unable to find database to connect to (database name is wrong)
8	Unable to connect to database (user ID/password is wrong)
11	Unable to find server to connect to (server name is wrong)
12	Incorrect encryption key for starting database

For information on specific checks made during validation, see the option descriptions that follow.

Options

Connection parameters (-c) Supply database connection parameters. For a description, see “Connection parameters” on page 133 in the Sybase IQ System Administration Guide. The user ID must have DBA authority or REMOTE DBA authority.

For example, the following validates the sample database, connecting as user DBA with password SQL:

dbvalid -c “uid=DBA;pwd=SQL;dbf-c:\sybase\ASIQ-12_7\demo\asiqdemo.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 on the Adaptive Server 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 Adaptive Server 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 on the Adaptive Server 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) This option is only supported for databases created with Sybase IQ version 12.5 or later. 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 on the Adaptive Server 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, it 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. The –s option cannot be used 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.

Example

The following command validates the Catalog Store of the sample database, connecting as user DBA with password SQL:

dbvalid -c “uid=DBA;pwd=SQL;dbf-c:\sybase\ASIQ-12_7\demo\asiqdemo.db”