ALTER DBSPACE Statement

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:
   FILE logical-file-name 'file-path' iq-file-opts

iq-file-opts:
   [ [ SIZE  ] file-size ]
   …[ KB | MB  | GB  | TB ] ]
    [ RESERVE reserve-size [ KB | MB | GB | TB ] ]

Parameters

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, 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.

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 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 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.
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 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 dbspaces in the IQ main store.
When used with the ALTER FILE clause, changes specified IQ main or temporary store dbfile to read-write. The file must be associated with an IQ main or temporary dbspace.

ONLINE – puts an offline dbspace and all associated files online. Can only be used for dbspaces in the 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 IQ main store.) Can only be used for dbspaces in the 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 quotes.

Examples

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
```

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.

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

SQL—Vendor extension to ISO/ANSI SQL grammar.
Sybase—Not supported by Adaptive Server Enterprise.

Permissions

Requires the MANAGE ANY DBSPACE system privilege.