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 all current versions of tables.
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 9, “System Procedures” of the Sybase IQ Reference Manual.
Dropping
a main dbspace from a multiplex database
If you have created multiple main dbspaces, you can drop the IQ_SYSTEM_MAIN dbspace as long as you do not drop the last main dbspace remaining.
Connect to the multiplex write server.
Open the dbspaces container.
Right-click the main dbspace and choose Properties.
On the Usage tab, choose Readonly from the Mode dropdown list. Click Apply and OK.
In Interactive SQL, run sp_iqtransaction. Check the version_ID column and make sure that no query servers are running with old version IDs. You must make sure that transactions on the query servers have caught up to the current state of the main dbspace. Check the Version ID column to make sure that none are running with old version IDs.
Reopen the dbspaces container on the write server.
Right-click the main dbspace and choose Properties.
On the Usage tab, choose Relocate from the Mode dropdown list. Click Apply and OK.
Use sp_iqrelocate to relocate all data on the dbspace, then commit.
After all objects and user data are relocated to other dbspaces, the dbspace is ready to drop using the DROP DBSPACE command. Before executing the DROP DBSPACE command, check the block type information on the dbspace Properties tab to be sure the dbspace does not have blocks of type ‘A’ or ‘O’ (blocks with data from active table versions or old versions that may still be in use). (You may instead run sp_iqdbspace, as shown in “Dbspace management example”.)
Right-click the main dbspace and choose Delete. If the delete dbspace fails, issue a checkpoint and repeat this step.
Dropping a temporary
dbspace from a multiplex database
Open the dbspaces container.
Right-click the temporary dbspace and choose Properties.
On the Usage tab, choose Relocate from the Mode dropdown list. Click Apply and OK.
Use sp_iqdbspace to make sure no one is using the dbspace.
Note: You may have to disconnect users or possibly restart the server to free all temporary space.
Make sure the write server is running. (If it is not, the dbspace will appear to return after you synchronize query servers.)
Connect to the server that owns the dbspace.
Open the dbspaces container.
Right-click the temporary dbspace and choose Delete.
Dropping a local
dbspace from a multiplex database
Connect to the multiplex query server.
Open the dbspaces container.
Right-click the local dbspace and choose Properties.
On the Usage tab, choose Relocate from the Mode dropdown list. Click Apply and OK.
Use sp_iqrelocate to relocate all data on the dbspace, then commit.
Use sp_iqdbspace to make sure no one is using the dbspace.
Right-click the local dbspace and choose Delete. If the delete dbspace fails, issue a checkpoint and repeat this step.
