ASA SQL Reference
SQL Statements
Use this statement to pre-allocate space for a dbspace or for the transaction log, or when a database file is renamed or moved.
ALTER DBSPACE { dbspace-name | TRANSLOG | TEMPORARY }
{ ADD number [ PAGES | KB | MB | GB | TB ]
| RENAME filename-string }
TRANSLOG 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 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 256K at a time for page sizes of 1K, 2K, 4K and 8K, 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.
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 Adaptive Server Anywhere finds the new file when the database is started.
Using ALTER DBSPACE with RENAME on the main dbspace, SYSTEM, has no effect.
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 SYSFILE 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 Adaptive Server Anywhere where to find the main database file. The main database file holds the system tables. Adaptive Server Anywhere looks in these system tables to find the location of the other dbspaces, and then opens each of the other dbspaces.
Must have DBA authority. Must be the only connection to the database.
Automatic commit.
SQL/92 Vendor extension.
SQL/99 Vendor extension.
Sybase Not supported by Adaptive Server Enterprise.
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 megabytes:
ALTER DBSPACE system ADD 400 MB
The following example changes the filename associated with the system_2 dbspace:
ALTER DBSPACE system_2 RENAME 'e:\db\dbspace2.db'