You can use Sybase Central or issue a DROP DBSPACE command to remove a database file. (For multiplex databases, always use Sybase Central to remove a dbspace.) In order to drop a dbspace, the following must be true:
It must not contain any data from user tables. Sybase IQ does not allow you to drop a dbspace unless it is empty.
It must not be SYSTEM or IQ_SYSTEM_MSG. These dbspaces can never be dropped.
In order to empty a dbspace, you must:
Relocate or drop all tables on the dbspace.
Commit or roll back only transactions that are using older versions of tables (on a multiplex server, this includes query server transactions).
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 DBSPACE command or Sybase Central to set the dbspace in relocate mode and then run the sp_iqrelocate stored procedure.
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.
sp_iqrelocate does not automatically commit. You must commit the changes before they are persistent. Also note that sp_iqrelocate is used with main and local dbspaces only.
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 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. You can relocate by setting the dbspace mode to relocate (using the ALTER DBSPACE command), then using the sp_iqrelocate stored procedure.
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.
The following is sample output from sp_iqdbspace.
Name |
Path |
Segment Type |
RW Mode |
Usage |
DBS Size |
Reserve |
Stripe Size |
Blk Types |
First Blk |
Last Blk |
|---|---|---|---|---|---|---|---|---|---|---|
IQ__ SYSTEM_MAIN |
D:\IQ\ dbspacedb.iq |
MAIN |
RW |
24 |
10M |
100M |
8K |
1H,64F,32D,62A,20X,128M |
1 |
1280 |
dbspacedb2 |
D:\IQ\ dbspacedb.iq2 |
MAIN |
RW |
9 |
10M |
20M |
8K |
1H,32F,56A, 19X |
1045440 |
1046719 |
dbspacedb3 |
D:\IQ\ dbspacedb.iq3 |
MAIN |
RW |
12 |
10M |
40M |
8K |
1H,32F,59A, 49X |
2090880 |
2092159 |
IQ_ SYSTEM_TEMP |
dbspacedb. iqtmp |
TEMPORARY |
RW |
8 |
10M |
10M |
8K |
1H,64F,12A, 20X |
1 |
1280 |
For more information on the values of the output fields of sp_iqdbspace, see “sp_iqdbspace procedure” in Chapter 10, “System Procedures” of the Sybase IQ Reference Manual.
