The following procedures describe how to drop multiplex dbspaces using Sybase Central.
Dropping a local
dbspace from a multiplex database
If your database has multiple local dbspaces, follow this procedure to drop all but the last remaining local dbspace. When there is only one local dbspace, follow the steps in “Dropping the last local dbspace on query server.”
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.
Dropping the last local dbspace on query server
When a dbspace is created, IQ needs to save some checkpoint information for the transaction control. If the dbspace is the only one for the type of store (IQ Local Store, in this case), the checkpoint data is saved in the very same dbspace. This makes the dbspace “in use,” even though nothing has been done yet from a user’s point of view. The dbspace is therefore protected from being dropped.
To release the checkpoint information saved in the dbspace, a DBA needs to issue a CHECKPOINT statement to force IQ to flush out the checkpoint data. If there are multiple connections to the server, only the CHECKPOINT statement — which executes after the completion of all transactions temporarily overlapping with the transaction of creating the dbspace — will flush the checkpoint data. Therefore a DBA may have to issue CHECKPOINT several times to drop the dbspace.
A stored procedure, sp_iqdbspace, reports the current status of all IQ dbspaces, including whether or not a dbspace is ready to be dropped. See “Dropping a dbspace,” in Chapter 5, “Working with Database Objects” in Sybase IQ System Administration Guide for details on the sp_iqdbspace returned results.
If the IQ Local Store to be dropped is not empty, you must do the following before you can drop it:
Alter the database to relocate mode. For details, see “Altering dbspaces” in Chapter 6, “Managing Dbspaces” in Introduction to Sybase IQ.
Run sp_iqrelocate on the database. For details, see Chapter 9, “System Procedures” in Sybase IQ Reference Manual.
For more information on dropping dbspaces, see “DROP statement” in Chapter 6, “SQL Statements” in 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 read-only 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.
