Reference Manual
CHAPTER 9. SQL Statements
To modify the characteristics of the main database file or an extra dbspace. To preallocate space for a database or for the transaction log.
ALTER DBSPACE { dbspace-name | TRANSLOG }
...
ADD number
| RENAME filename
Must have DBA authority. Must be the only connection to the database.
Automatic commit.
Each database is held in one or more files. A dbspace is an internal name that is associated with each database file. ALTER DBSPACE modifies the main database file (also called the root file) or an extra dbspace. The dbspace names for a database are held in the SYSFILE system table. The default dbspace name for the root file of a database is SYSTEM.
An ALTER DBSPACE with the ADD clause is used to preallocate disk space to a dbspace. It extends the size of a dbspace by the number of pages given by number. The page size of a database is defined when the database is created.
The ALTER DBSPACE statement with the ADD clause allows database files to be extended in large amounts before the space is required, rather than the normal 32 pages at a time when the space is needed. This can improve performance for loading large amounts of data and also serves to keep the dbspace files more contiguous within the file system.
The ALTER DBSPACE statement with the special dbspace name TRANSLOG preallocates disk space for the transaction log. Preallocation improves performance if the transaction log is expected to grow quickly. You may want to use this feature if, for example, you are handling many binary large objects (blobs), such as bitmaps.
The preallocation is carried out by altering the special dbspace name TRANSLOG, as follows:
ALTER DBSPACE TRANSLOG ADD number
This extends the size of the transaction log by the number of pages specified.
If you move a database file other than the root file to a different filename, directory, or device, use the ALTER DBSPACE statement with RENAME to ensure that Adaptive Server Anywhere can find the file when the database is started.
When a multi-file database is started, the start line or ODBC data source description tells Adaptive Server Anywhere where to find the root database file. The root database file (which has the default dbspace name SYSTEM) holds the system tables. Adaptive Server Anywhere looks in these system tables to find the location of the other dbspaces, and Adaptive Server Anywhere then opens each of the other dbspaces.
Using ALTER DBSPACE with RENAME on a root file has no effect.
SQL/92 Vendor extension
Sybase Not supported by Adaptive Server Enterprise.
Increase the size of the SYSTEM dbspace by 200 pages.
ALTER DBSPACE system ADD 200
Rename the file for dbspace SYSTEM_2 to dbspace2.
ALTER DBSPACE system_2 RENAME 'e:\db\dbspace2.db'