ALTER DBSPACE statement

ALTER DBSPACE { dbspace-name | TRANSLOG | TEMPORARY }
 { ADD number [ add-unit ]
   | RENAME filename }

add-unit : 
PAGES 
| KB 
| MB 
| GB 
| TB

TRANSLOG clause You supply the special dbspace name TRANSLOG to preallocate 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 syntax ALTER DBSPACE dbspace-name TRANSLOG RENAME filename is not supported.
TEMPORARY clause You supply the special dbspace name TEMPORARY to add space to temporary dbspaces. When space is added to a temporary dbspace, the additional space materializes in the corresponding temporary file immediately. Pre-allocating space to the temporary dbspace of a database can improve performance during execution complex queries that use large work tables.
ADD clause An ALTER DBSPACE statement with the ADD clause preallocates disk space for a dbspace. It extends the corresponding database file by the specified size, in units of pages, kilobytes (KB), megabytes (MB), gigabytes (GB), or terabytes (TB). If you do not specify a unit, PAGES is the default. The page size of a database is fixed when the database is created.
If space is not preallocated, database files are extended by about 256 KB at a time for page sizes of 2 KB, 4 KB, and 8 KB, and by about 32 pages for other page sizes, when the space is needed. Pre-allocating space can improve performance for loading large amounts of data and also serves to keep the database files more contiguous within the file system.

You can use this clause to add space to any of the predefined dbspaces (system, temporary, temp, translog, and translogmirror).

RENAME clause If you rename or move a database file other than the main file to a different directory or device, you can use ALTER DBSPACE with the RENAME clause to ensure that SQL Anywhere finds the new file when the database is started. The filename parameter can be a string literal, or a variable.

Cloud note: For tenant databases in a cloud, when you specify the location of a dbspace, you can specify only a file name. You cannot specify a directory path.

The name change takes effect as follows:

If the dbspace was already open before the statement was executed (that is, you have not yet renamed the actual file), it remains accessible; however, the name stored in the catalog is updated. After the database is stopped, you must rename the file to match what you provided using the RENAME clause, otherwise the file name won't match the dbspace name in the catalog and the database server is unable to open the dbspace the next time the database is started.

If the dbspace was not open when the statement was executed, the database server attempts to open it after updating the catalog. If the dbspace can be opened, it becomes accessible. No error is returned if the dbspace cannot be opened.

To determine if a dbspace is open, execute the statement below. If the result is NULL, the dbspace is not open.

SELECT DB_EXTENDED_PROPERTY('FileSize','dbspace-name');

Using ALTER DBSPACE with RENAME on the main dbspace, system, has no effect. The RENAME clause is not supported for changing the name of the transaction log file. You can use the BACKUP DATABASE statement to rename the transaction log for a running database. For example:

BACKUP DATABASE DIRECTORY 'directory-name'
TRANSACTION LOG ONLY
TRANSACTION LOG RENAME;

Each database is held in one or more files. A dbspace is an additional file with a logical name associated with each database file, and used to hold more data than can be held in the main database file alone. ALTER DBSPACE modifies the main dbspace (also called the root file) or an additional dbspace. The dbspace names for a database are held in the SYSDBSPACE system view. The main database file has a dbspace name of system.

When a multi-file database is started, the start line or ODBC data source description tells SQL Anywhere where to find the main database file. The main database file holds the system tables. SQL Anywhere looks in these system tables to find the location of the other dbspaces, and then opens each of the other dbspaces. You can specify which dbspace new tables are created in by setting the default_dbspace option.

DBA authority and be the only connection to the database.

Automatic commit.

SQL/2008 Vendor extension.

The following example increases the size of the system dbspace by 200 pages:

ALTER DBSPACE system
ADD 200;

The following example increases the size of the system dbspace by 400 MB:

ALTER DBSPACE system
ADD 400 MB;

The following example changes the file name associated with the system_2 dbspace:

ALTER DBSPACE system_2
RENAME 'e:\db\dbspace2.db';

ALTER DBSPACE statement

Syntax

Parameters

Remarks

Permissions

Side effects

See also

Standards and compatibility

Example