Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page: 136 / 536

Working with databases
116
Reserving space for
DDL commands
In the event that you run out of space to perform an operation, you will see a
message telling you to more space. In addition to space for the new dbspace
itself, you also need a small amount of space to issue the
ADD DBSPACE
command. To ensure that you have the space for this and related DDL
commands, set the options
MAIN_RESERVED_DBSPACE_MB
and
TEMP_RESERVED_DBSPACE_MB
. Do not wait until you have run out of
space to set these options. See the “Database Options” chapter of the Adaptive
Server IQ Reference Manual for option details.
Dropping dbspaces
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. Adaptive Server IQ does not allow you to
drop a dbspace unless it is empty.
• It must be the last one added. (After you drop the last dbspace, the next
most recently added dbspace becomes the last one, and can be dropped.)
• It must not be one of the four initial dbspaces,
SYSTEM
,
IQ_SYSTEM_MAIN
,
IQ_SYSTEM_TEMP
, and
IQ_SYSTEM_MSG
. These
dbspaces can never be dropped.
Because of the way Adaptive Server IQ fills dbspaces with data, it is unlikely
that you will be able to drop the last dbspace, especially if disk striping is in
use. You also cannot empty a dbspace by truncating the tables in it, as even an
empty table takes some space. The only way to completely remove a table and
its data is with a
DROP TABLE
statement (or by dropping the table in Sybase
Central).
If you drop or truncate a table while other users are reading from it, the normal
rules of table versioning apply, that is, old table versions remain until readers'
transactions complete; see Chapter 8, “Transactions and Versioning” for
details.
To find out whether you can drop a particular dbspace, run the stored procedure
sp_iqstatus
. Look at the DB Blocks value, which tells you the block numbers
each dbspace includes. Compare this value to the Main IQ Blocks Used (or
Temporary IQ Blocks Used), to see whether the Max Block # is in the dbspace.
If it is, you cannot drop this dbspace.