You can issue a DROP DBSPACE command to remove a database file. In order to drop a dbspace, the following must be true:
It must not contain any data from user tables or join indexes. Sybase IQ does not allow you to drop a dbspace unless it is empty.
It must not 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.
In order 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. You need to relocate the tables by using the ALTER TABLE MOVE command.
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” for details.
A DBA can determine in which dbspace 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 the stored procedure sp_iqdbspace. Look at the Block Types column, which tells you the contents of each dbspace. A dbspace can be dropped if it contains only block types ‘H,’ ‘F,’ ‘R’, ‘B,’ ‘X,’ and ‘C.’
Block type ‘A’ is data from active table versions. Use sp_iqdbspaceinfo to determine which tables need to be relocated.
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.
