The sp_iqcheckdb Stored Procedure

The SAP Sybase IQ Database Consistency Checker (DBCC) performs database verification. The sp_iqcheckdb stored procedure, in conjunction with server startup options, is the interface to DBCC.

You select the different modes of check and repair by specifying an sp_iqcheckdb command string. sp_iqcheckdb reads every database page and checks the consistency of the database, unless you specify otherwise in the command string.

Note: On a secondary server sp_iqcheckdb does not check the free list. It performs all other checks.

DBCC has three modes that perform increasing amounts of consistency checking and a mode for resetting allocation maps. Each mode checks all database objects, unless individual dbspaces, tables, partitions, indexes, or index types are specified in the sp_iqcheckdb command string. If you specify individual table names, all indexes within those tables are also checked.

Note: The sp_iqcheckdb stored procedure does not check referential integrity or repair referential integrity violations.

DBCC Performance

The execution time of DBCC varies according to the size of the database for an entire database check, the number of tables or indexes specified, and the size of the machine. Checking only a subset of the database, i.e., only specified tables, indexes, or index types, requires less time than checking an entire database.

For the best DBCC performance, be as specific as possible in the sp_iqcheckdb command string. Use the 'allocation' or 'check' verification mode when possible and specify the names of tables or indexes, if you know exactly which database objects require checking.

sp_iqcheckdb Check Mode

In check mode, sp_iqcheckdb performs an internal consistency check on all IQ indexes and checks that each database block has been allocated correctly. All available database statistics are reported. This mode reads all data pages and can detect all types of allocation problems and most types of index inconsistencies. Check mode should run considerably faster than verify mode for most databases.

When to run in check mode:

If metadata, null count, or distinct count errors are returned when running a query.

Examples of check mode:

sp_iqcheckdb Check Mode Examples
Command	Description
sp_iqcheckdb 'check database'	Internal checking of all tables and indexes in the database
sp_iqcheckdb 'check table t1'	Default checking of all indexes in table `t1`
sp_iqcheckdb 'check index t1c1hg'	Internal checking of index `t1c1hg`
sp_iqcheckdb 'check indextype FP database'	Checking of all indexes of type FP in the database

sp_iqcheckdb Verify Mode

In verify mode, sp_iqcheckdb performs an intra-index consistency check, in addition to internal index consistency and allocation checking. All available database statistics are reported. The contents of each non-FP index is verified against its corresponding FP index(es). Verify mode reads all data pages and can detect all types of allocation problems and all types of index inconsistencies.

When to run in verify mode:

If metadata, null count, or distinct count errors are returned when running a query

Examples of verify mode:

sp_iqcheckdb Verify Mode Examples
Command	Description
sp_iqcheckdb 'verify database'	Verify contents of all indexes in the database
sp_iqcheckdb 'verify table t1'	Verify contents of all indexes in table `t1`
sp_iqcheckdb 'verify index t1c1hg'	Verify contents of index `t1c1hg`
sp_iqcheckdb 'verify indextype HG table t1'	Verify contents of all HG indexes in table `t1`

Note: If you check individual non-FP indexes in check mode, the corresponding FP index(es) are automatically verified with internal consistency checks and appear in the DBCC results.

sp_iqcheckdb Allocation Mode

In allocation mode, sp_iqcheckdb checks that each database block is allocated correctly according to the internal physical page mapping structures (blockmaps). Database statistics pertaining to allocation are also reported. This mode executes very quickly. Allocation mode, however, does not check index consistency and cannot detect all types of allocation problems.

When to run in allocation mode:

To check for leaked blocks or inconsistent indexes due to multiply owned blocks
After forced recovery, run sp_iqcheckdb in dropleaks mode to reset the allocation map (must use database as the target)
To check for duplicate or unowned blocks (use database or specific tables or indexes as the target)
If you encounter page header errors

Examples of allocation mode:

sp_iqcheckdb Allocation Mode Examples
Command	Description
sp_iqcheckdb 'allocation database'	Allocation checking of entire database
sp_iqcheckdb 'allocation database dumpleaks'	Allocation checking of entire database and print block numbers for leaked blocks to IQ message file
sp_iqcheckdb 'allocation table t1'	Allocation checking of table `t1`
sp_iqcheckdb 'allocation index t1c1hg'	Allocation checking of index `t1c1hg`
sp_iqcheckdb 'allocation indextype LF table t2'	Allocation checking of all LF indexes in table `t2`

If some partitions of the table are offline, you can specify a partition target to check only part of a table.

You can combine all modes and run multiple checks on a database in a single session. In the following example, sp_iqcheckdb performs a quick check of partition p1 in table t2, a detailed check of index i1, and allocation checking for the entire database using half of the CPUs:

sp_iqcheckdb 'check table t2 partition p1 
verify index i1
allocation database resources 50'

Allocation mode options are only allowed with the DBCC command 'allocation database'.

The following allocation mode options print block numbers for affected database blocks to the IQ message file:

dumpleaks — leaked blocks
dumpdups — duplicate blocks
dumpunallocs — unallocated blocks

The resetclocks option corrects the values of internal database versioning clocks, in the event that these clocks are slow. Do not use the resetclocks option for any other purpose unless you contact Technical Support.

The resetclocks option must be run in single user mode and is only allowed with the DBCC command 'allocation database'. The syntax of the resetclocks command is:

sp_iqcheckdb 'allocation database resetclocks'

sp_iqcheckdb Dropleaks Mode

When the SAP Sybase IQ server runs in single-node mode, you can use dropleaks mode with either a database or dbspace target to reset the allocation map for the entire database or specified dbspace targets. If the target is a dbspace, then the dropleaks operation must also prevent read-write operations on the named dbspace. All dbspaces in the database or dbspace list must be online.

In the following example, the first statement resets allocation maps for the entire database, and the second statement resets allocation maps for the dbspace dbsp1.

sp_iqcheckdb 'dropleaks database'
sp_iqcheckdb 'dropleaks dbspace dbsp1'

Note: Use sp_iqrebuildindex to repair index errors.