Issue a DROP DBSPACE command to remove a database file. To drop a dbspace:
You must have DBA or SPACE ADMIN authority.
It cannot contain any data from user tables or join indexes. Sybase IQ does not allow you to drop a dbspace unless it is empty.
It cannot be a required dbspace: SYSTEM, IQ_SYSTEM_MAIN, IQ_SYSTEM_TEMP, or IQ_SYSTEM_MSG. These dbspaces can never be dropped, but you may drop other dbspaces from the IQ main store or catalog store if the dbspace contains no user-created objects.
To empty a dbspace, you must:
Relocate or drop all objects resident on the dbspace.
Commit or roll back only transactions that are using older versions of tables.
Because of the way Sybase IQ fills dbspaces with data, it is unlikely that a dbspace will become empty only after explicitly relocating tables and join indexes, especially if disk striping is in use. Typically, you cannot empty a dbspace by truncating the tables in it, as even an empty table takes some space. To relocate the tables, use ALTER TABLE MOVE.
If you relocate a table while other users are reading from it, the normal rules of table versioning apply, that is, old table versions persist until the transactions of the readers complete; see Chapter 10, “Transactions and Versioning.”
A DBA can determine the dbspace in which tables and indexes are located by running the stored procedures sp_iqspaceinfo, sp_iqdbspaceinfo, and sp_iqindexinfo. These procedures show the number of blocks used by each table, join index, and index in each dbspace.
To find out whether you can drop a particular dbspace, run sp_iqdbspace. Look at the Block Types column (Blk Types), which tells you the contents of each dbspace. You can drop a dbspace if it contains block types “H,” “F,” “R,” “B,” “X,” and “C,” but not other block types.
Block type “A” is data from active table versions. Use sp_iqdbspaceinfo to determine which tables to relocate.
Block type “O” indicates old versions that may still be in use. You must roll back or commit active connections to release this space. Block type “M” indicates multiplex.
For more information on the values of the output fields of sp_iqdbspace, see “sp_iqdbspace procedure” in Chapter 7, “System Procedures,” in Reference: Building Blocks, Tables, and Procedures.
