Changes the read/write mode, changes the size, or extends an existing dbspace.
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 | READWRITE }
| SIZE file-size [ KB | MB | GB | TB | PAGES ]
| ADD file-size [ KB | MB | GB | TB | PAGES ] }
SERVER ”server-name” ] RENAME PATH 'new-file-pathname'
RENAME TO newname
FILE logical-file-name 'file-path' iq-file-opts
[ [ SIZE ] file-size ] …[ KB | MB | GB | TB ] ] [ RESERVE reserve-size [ KB | MB | GB | TB ] ]
Change the mode of a dbspace called DspHist to READONLY.
ALTER DBSPACE DspHist READONLY
Add 500MB to the dbspace DspHist by adding the file FileHist3 of size 500MB.
ALTER DBSPACE DspHist ALTER FILE FileHist3 ADD 500MB
On Solaris, add two 500MB files to the dbspace DspHist.
ALTER DBSPACE DspHist ADD FILE FileHist3 ‘/History1/data/file3’ SIZE 500MB FILE FileHist3 ‘/History1/data/file4’ SIZE 500
Increase the size of the dbspace IQ_SYSTEM_TEMP by 2GB.
ALTER DBSPACE IQ_SYSTEM_TEMP ADD 2 GB
Remove two files from dbspace DspHist. Both files must be empty.
ALTER DBSPACE DspHist DROP FILE FileHist2, FILE FileHist4
Increase the size of the dbspace IQ_SYSTEM_MAIN by 1000 pages. (ADD defaults to pages.)
ALTER DBSPACE IQ_SYSTEM_MAIN ADD 1000
The ALTER DBSPACE statement 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.
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 dbspaces of IQ main or IQ temporary dbspaces. You may add a file to a read-only dbspace, but the dbspace remains read-only.
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 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, 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.
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 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.
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.
Must have SPACE ADMIN or DBA authority.
sp_iqdbspace procedure in Chapter 7, “System Procedures” in Reference: Building Blocks, Tables, and Procedures
“Working with dbspaces” in Chapter 5, “Working with Database Objects,” of the System Administration Guide: Volume 1
