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>
new-file-spec - (back to Syntax)
FILE <logical-file-name> <'file-path'> iq-file-opts
IQ_SYSTEM_LOG RENAME ' <new-directory-specification> ' / ' <file-prefix> '
iq-file-opts - (back to Syntax)
[ [ SIZE ] <file-size> KB | MB | GB | TB ] ]
[ RESERVE <reserve-size> [ KB | MB | GB | TB ] ]
Parameters
(back to top)
- ADD 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,
IQ temporary, or cache 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.
For a cache dbspace,
use ADD FILE on multiplex or simplex servers.
When used in the
ALTER FILE 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.
- DROP FILE removes the specified file from a 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 when used with the DROP FILE clause, renames
the pathname of the dbspace that contains a single file. It is semantically
equivalent to the RENAME PATH clause. An error is returned if the dbspace
contains more than one file. You cannot rename IQ_SYSTEM_MAIN, IQ_SYSTEM_MSG, IQ_SYSTEM_TEMP, IQ_SHARED_TEMP, or SYSTEM.
When used with the ALTER FILE clause, renames the
specified file’s logical name to a new name. The new name must be unique
in the database.
- READONLY when used with the DROP 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 the
cache dbspace, and dbspaces in the IQ main store.
When
used with the ALTER FILE clause, 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.
- READWRITE when used with the DROP FILE clause, changes
the dbspace to read-write. The dbspace must be online. Can only be used
for the cache dbspace, and dbspaces in the
IQ main store.
When used with the ALTER FILE clause,
changes the specified cache dbspace, IQ
main, or temporary store dbfile to read-write. The file must be
associated with a cache dbspace, IQ
main, or temporary dbspace.
- ONLINE puts an offline dbspace and all associated files online. Can
only be used for dbspaces in the cache
dbspace and IQ main store.
- OFFLINE 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 cache
dbspace or IQ main store.) Can only be used for dbspaces in
the cache dbspace or IQ main
store.
- STRIPING 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 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.
- FORCE READWRITE when used with the ALTER FILE clause, 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.
- SIZE 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.
- RENAME PATH when used with the ALTER FILE 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. Enclose the physical file path to the
dbfile in single quotation marks.
- IQ_SYSTEM_LOG RENAME
<new-directory-specification> /
<file-prefix>
by default, SAP IQ saves the point-in-time recovery log in the same
directory as the .db file. To save the log in another directory, use:
ALTER DBSPACE IQ_SYSTEM_LOG RENAME <new-directory-specification> / <file-prefix>
The
<new-directory-specification> must point to an
existing directory. If point in time recovery logging is active, using
this command with a
<new-directory-specification>,
opens a new log at the new location. For multiplex servers, this
directory must reside on a shared file system and be writable by all
multiplex nodes.
ALTER DBSPACE assumes that any string terminated by a slash (Windows) or
backslash (UNIX) in the <new-directory-specification>
is a directory name. A terminating string without a slash or backslash
is prefixed to the log name. All logs include an
.iqlog extension.
Examples
(back to top)
- Example 1 changes the mode of a dbspace called DspHist to READONLY:
ALTER DBSPACE DspHist READONLY
- Example 2 adds 500MB to the dbspace DspHist by adding the file FileHist3 of size 500MB:
ALTER DBSPACE DspHist
ALTER FILE FileHist3 ADD 500MB
- Example 3 on a UNIX system, adds two 500MB files to the
dbspace DspHist:
ALTER DBSPACE DspHist ADD
FILE FileHist3 '/History1/data/file3' SIZE 500MB,
FILE FileHist4 '/History1/data/file4' SIZE 500
- Example 4 increases the size of the dbspace IQ_SYSTEM_TEMP by 2GB:
ALTER DBSPACE IQ_SYSTEM_TEMP ADD 2 GB
- Example 5 removes two files from dbspace DspHist. Both files must be empty:
ALTER DBSPACE DspHist
DROP FILE FileHist2, FILE FileHist4
- Example 6 increases the size of the dbspace IQ_SYSTEM_MAIN by 1000 pages. (ADD clause
defaults to pages):
ALTER DBSPACE IQ_SYSTEM_MAIN ADD 1000
- Example 7 adds a file to the cache dbspace myDAS:
ALTER DBSPACE myDAS ADD FILE iqdas2 'sampledb.iqcache' size 1024
- Example 8 removes dbfile iqdas2 from the cache dbspace myDAS:
ALTER DBSPACE myDAS DROP FILE iqdas2
- Example 9 disables the cache dbspace myDAS:
ALTER DBSPACE myDAS OFFLINE
- Example 10 makes the myDAS cache dbspace dbfile iqdas2 read-only:
ALTER DBSPACE myDAS ALTER FILE iqdas2 READONLY
Usage
(back to top)
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.
You may optionally delimit dbspace and dbfile names with double
quotation marks.
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.
Side effects:
- Automatic commit
- Automatic checkpoint
- A mode change to READONLY causes immediate relocation of
the internal database structures on the dbspace to one of the read-write
dbspaces.
Standards
(back to top)
- SQL—Vendor extension to ISO/ANSI SQL grammar.
- SAP Database products—Not
supported by SAP ASE.