ALTER DBSPACE statement

Use this statement to pre-allocate space for a dbspace or for the transaction log, or to update the catalog when a dbspace file is renamed or moved.

Syntax

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

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

Parameters

TRANSLOG clause You supply the special dbspace name TRANSLOG to pre-allocate disk space for the transaction log. Pre-allocation 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.
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 with the ADD clause pre-allocates 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 pre-allocated, 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 pre-defined dbspaces (system, temporary, temp, translog, and translogmirror). See Pre-defined dbspaces.

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.

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.

Remarks

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 ISYSFILE system table. 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.

Permissions

Must have DBA authority. Must be the only connection to the database.

Side effects

Automatic commit.

Standards and compatibility

SQL/2003 Vendor extension.

Example

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';

Send feedback about this page via email or DocCommentXchange