It is possible to drop DBSPACEs on an IQ Server, if there is enough space to move all data to other DBSPACEs.
1. Check DBSPACEs
The sp_iqdbspace command should be used to list all DBSPACEs and their status.
(DBA)> sp_iqdbspace
Execution time: 0.053 seconds
Name Path SegmentType RWMode Usage DBSSize Reserve StripeSize BlkTypes FirstBlk LastBlk
----------------------------------------------------------------------------------------------------------
IQ_SYSTEM_MAIN /var/sybase/IQ_Server/devices/IQstore.50 MAIN RW 1 65G 0B 8K 1H,320F,32D,128M 1 8516599
IQ_51 /var/sybase/IQ_Server/devices/IQstore51 MAIN RW 4 65G 0B 8K 1H,32F 9408960 9409959
IQ_52 /var/sybase/IQ_Server/devices/IQstore52 MAIN RW 4 65G 0B 8K 1H,32F 10454400 10455399
IQ_SYSTEM_TEMP /var/sybase/IQ_Server/devices/IQtmp.10 TEMPORARY RW 1 33G 0B 8K 1H,192F,16A 1 4325272
TMP_11 /var/sybase/IQ_Server/devices/IQtmp11 TEMPORARY RW 65 33G 0B 8K 1H,64F 5227200 5227299
2. Change the DBSPACE Status
The DBSPACE to be dropped should be put into "relocate" status using the "alter dbspace" command.
(DBA)> alter dbspace IQ_51 relocate
Execution time: 0.18 seconds
A DBSPACE can have one of three statuses, displayed in the RW column of the sp_iqdbspace output; RW - readwrite, RO - readonly, RR - relocate .
3. Use sp_iqrelocate
(DBA)> sp_iqrelocate database
Execution time: 0.038 seconds
Object Blocks Relocated Status
------------------------------
(First 0 rows)
(DBA)> commit
Execution time: 0.012 seconds
Whilst in relocate mode, no data will be written to the DBSPACE and next time the sp_iqrelocate command is used data on that DBSPACE will be moved to other DBSPACEs. NOTE: sp_iqrelocate can only be used with MAIN DBSPACEs. If used with a TEMP DBSPACE you will see the following error message;
sp_iqrelocate database
Could not execute statement.
There must be at least one readwrite dbspace and one relocate dbspace to
relocate data.
-- (db_iqutility.cxx 6894)
SQLCODE=-1009061, ODBC 3 State="HY000"
Line 1, column 1
sp_iqrelocate database
So, if the DBSPACE is a MAIN DBSPACE execute "sp_iqrelocate database" followed by a commit, because sp_iqrelocate does not automatically commit. If the DBSPACE is a TEMP DBSPACE this step can be skipped.
4. Drop DBSPACE
Execute the "sp_dbspace" command.
(DBA)> drop dbspace IQ_55
Execution time: 0.221 seconds
If the DBSPACE is a TEMP DBSPACE it may be necessary to restart the IQ Server to free Temporary Blocks, before dropping the DBSPACE.
The relocate steps are deeper explained here: IQ sp_iqrelocate database - written in german.