Changes the read/write mode, changes the size, or
extends an existing dbspace.
Syntax
ALTER DBSPACE dbspace-name
{ ADD new-file-spec [, new-file-spec ... ]
| DROP FILE logical-file-name [, FILE logical-file-name ... ]
| RENAME TO newname | RENAME 'new-file-pathname'
| READONLY | READWRITE
| ONLINE | OFFLINE
| STRIPING{ ON | OFF }
| STRIPESIZEKB size-in-KB
ALTER FILE file-name
{ READONLY | [ FORCE ] READWRITE }
| SIZE file-size [ KB | MB | GB | TB ]
| ADD file-size [ KB | MB | GB | TB | PAGES ] }
RENAME PATH 'new-file-pathname'
RENAME TO newname
Usage
ALTER DBSPACE changes the
read-write mode, changes the online/offline state, alters the file size, renames the
dbspace name, file logical name or file path, or sets the dbspace striping
parameters. For details about existing dbspaces, run sp_iqdbspace procedure, sp_iqdbspaceinfo procedure, sp_iqfile procedure, sp_iqdbspaceobjectinfo, and sp_iqobjectinfo. Dbspace and dbfile names are always
case-insensitive. The physical file paths are case-sensitive, if the database is
CASE RESPECT and the operating system
supports case-sensitive files. Otherwise, the file paths are case-insensitive.
Enclose dbspace and dbfile names either in no quotes or in double
quotes. Enclose the physical file path to the dbfile in single quotes.
In Windows, if you specify a path, any backslash characters (\) must
be doubled if they are followed by an n or an x. This prevents them being
interpreted as a newline character (\n) or as a hexadecimal number (\x), according
to the rules for strings in SQL. It is safer to always double the backslash.
- ADD FILE clause – adds one or more files to the specified dbspace. The dbfile name and the
physical file path are required for each file and must be unique. You can
add files to IQ main, IQ shared temporary, or IQ temporary dbspaces. You may
add a file to a read-only dbspace, but the dbspace remains read-only. You
can add files to multiplex shared temporary dbspaces only in read-only mode
(the default for ADD FILE).
A catalog dbspace may contain only one file, so ADD FILE may not be used on catalog dbspaces.
For an RLV
dbspace, use ADD FILE on simplex servers only.
You cannot add a file to a multiplex RLV dbspace.
- DROP FILE clause – removes the specified file from an IQ dbspace. The file must be empty. You
cannot drop the last file from the specified dbspace. Instead use DROP
DBSPACE if the dbspace contains only one file. Rename TO clause—Renames the
dbspace-name to a new name. The new name must be
unique in the database. You cannot rename IQ_SYSTEM_MAIN,
IQ_SYSTEM_MSG, IQ_SYSTEM_TEMP,
IQ_SHARED_TEMP, or SYSTEM.
- RENAME clause – renames the pathname of the dbspace that contains a single file. It is
semantically equivalent to the ALTER FILE RENAME PATH
clause. An error is returned if the dbspace contains more than one
file.
- READONLY clause – changes any dbspace except IQ_SYSTEM_MAIN,
IQ_SYSTEM_TEMP, IQ_SYSTEM_MSG,
IQ_SHARED_TEMP, and SYSTEM to
read-only. Disallows DML modifications to any object currently assigned to
the dbspace. Can only be used for dbspaces in the IQ main store.
- READWRITE clause – changes the dbspace to read-write. The dbspace must be online. Can only be
used for dbspaces in the IQ main store.
- ONLINE clause – puts an offline dbspace and all associated files online. Can only be used
for dbspaces in the IQ main store.
- OFFLINE clause – puts an online read-only dbspace and all associated files offline. (Returns
an error if the dbspace is read-write, offline already, or not of the IQ
main store.) Can only be used for dbspaces in the IQ main store.
- STRIPING clause – changes the disk striping on the dbspace as specified. When disk striping is
set ON, data is allocated from each file within the dbspace in a round-robin
fashion. For example, the first database page written goes to the first
file, the second page written goes to the next file within given dbspace,
and so on. Read-only dbspaces are skipped.
- STRIPESIZEKB clause – specifies the number of kilobytes (KB) to write to each file before the disk
striping algorithm moves to the next stripe for the specified dbspace.
- ALTER FILE READONLY – changes the specified file to read-only. The file must be associated with an
IQ main dbspace. You cannot change files in IQ_SHARED_TEMP to READONLY
status.
- ALTER FILE READWRITE – changes specified IQ main or temporary store dbfile to read-write. The file
must be associated with an IQ main or temporary dbspace.
- ALTER FILE FORCE READWRITE – changes the status of the specified shared temporary store dbfile to
read-write, although there may be known file status problems on secondary
nodes. The file may be associated with an IQ main, shared temporary, or
temporary dbspace, but because new dbfiles in
IQ_SYSTEM_MAIN and user main are created read-write,
this clause only affects shared temporary dbspaces.
- ALTER FILE SIZE clause – specifies the new size of the file in units of kilobytes (KB), megabytes
(MB), gigabytes (GB), or terabytes (TB). The default is megabytes. You can
increase the size of the dbspace only if the free list (an allocation map)
has sufficient room and if the dbspace has sufficient reserved space. You
can decrease the size of the dbspace only if the portion to be truncated is
not in use.
- ALTER FILE ADD clause – extends the size of the file in units of pages, kilobytes (KB), megabytes
(MB), gigabytes (GB), or terabytes (TB). The default is MB. You can ADD only
if the free list (an allocation map) has sufficient room and if the dbspace
has sufficient reserved space.
- ALTER FILE RENAME PATH clause – renames the file pathname associated with the specified file. This clause
merely associates the file with the new file path instead of the old path.
The clause does not actually change the operating system file name. You must
change the file name through your operating system. The dbspace must be
offline to rename the file path. The new path is used when the dbspace is
altered online or when the database is restarted.
You may not rename the path of a file in IQ_SYSTEM_MAIN, because if the new path were not accessible, the
database would be unable to start. If you need to rename the path of a file in
IQ_SYSTEM_MAIN, make the file read-only, empty
the file, drop the file, and add the file again with the new file path name.
- ALTER FILE RENAME TO clause – renames the specified file’s logical name to a new name. The new name must
be unique in the database.