SAP Sybase IQ – dbcc for IQ? Yes! Just use sp_iqcheckdb

If you’re coming from Microsoft SAP SybaseSQL Server or Sybase ASE, SAP’s IQ doesn’t have the slew of dbcc commands you might expect. It rolls up all the pertinent dbccs into a single stored procedure. sp_iqcheckdb has four operational modes with a granularity at the database, table or index level:

  1. 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.
  2. 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.
  3. 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.
  4. When the 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.

No changes are actually performed with the exception of the dropleaks mode. If check or verify detects an issue with allocation, you will need to run sp_iqcheckdb in the dropleaks mode while the database is in single user mode. For any issues with indexes, use sp_rebuildindex on both the index and possibly the affected column of the table.

By default output of sp_iqcheckdb will go to the (instance_name).iqmsg log file but you can redirect it to its own file for easier reading:

set temporary option dbcc_log_progress='on';
sp_iqcheckdb 'verify database' ># filename

Please note that the file name may not contain spaces or special symbols and the path will be local to the IQ box. So, unless you want to hunt for your checkdb output file, please specify the full path. No, you can not put the filename in any type of quotes and you can’t escape interesting characters.

Unless you’re under a serious time constraint, always run sp_iqcheckdb in verify mode to determine structural issues with your IQ databases.

Share Button

3 Replies to “SAP Sybase IQ – dbcc for IQ? Yes! Just use sp_iqcheckdb”

  1. As I’ve been using it more and more I’ve been finding the sp_iqcheckdb is not very thorough at finding corruption on FP indexes. It will say everything is perfectly fine but when you rebuild the FP index, either the error:

    — Could not execute statement.
    — main Bufman: An error was detected on a database page. You may have a
    — damaged index. For additional information, please check your IQ message
    — file or run sp_iqcheckdb.
    — — (slib/s_blockmap.cxx 12119)
    — SQLCODE=-1009039, ODBC 3 State=”HY000″

    will be reported with sp_rebuildindex or the IQ instance will crash.

  2. Hi Jason,
    some question about the Sybase IQ 12.7 in Sybase IQ15.X the Server startup utility switches removed [CR 537028]

    -iqdroplks – replaced by the DROPLEAKS option for sp_iqcheckdb
    so sp_iqcheckdb ‘dropleaks database | dbspace’ not working do you know hot to fix this in Sybase IQ 12.7 ?
    Many Thanks,

    Joerg

Leave a Reply

Your email address will not be published. Required fields are marked *