ALTER DBSPACE Statement

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

Parameters

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

Examples

Example 1 – Change the mode of a dbspace called DspHist to READONLY.
```
ALTER DBSPACE DspHist READONLY
```
Example 2 – Add 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, add 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 – Increase the size of the dbspace IQ_SYSTEM_TEMP by 2GB.
```
ALTER DBSPACE IQ_SYSTEM_TEMP ADD 2 GB
```
Example 5 – Remove two files from dbspace DspHist. Both files must be empty.
```
ALTER DBSPACE DspHist 
DROP FILE FileHist2, FILE FileHist4
```
Example 6 – Increase the size of the dbspace IQ_SYSTEM_MAIN by 1000 pages. (ADD 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. 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.

See Reference: Building Blocks, Tables, and Procedures > System Procedures > System Stored Procedures > sp_iqdbspace Procedure and System Administration Guide: Volume 1 > Database Object Management > Data Storage.

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). See Using Sybase IQ Multiplex.

A catalog dbspace may contain only one file, so ADD FILE may not be used on catalog dbspaces.

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.

You can also view and change the dbspace mode and size through the Sybase Central Dbspaces window.

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.

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

Must have SPACE ADMIN or DBA authority.