ALTER DBSPACE statement

Chapter 43. Watcom-SQL Statements
ALTER DBSPACE statement

Function

To modify the characteristics of the main database file or an extra dbspace. To preallocate space for a database or for the transaction log.

Syntax

     ALTER DBSPACE { dbspace-name | TRANSLOG }
          ...
          ADD number
          | RENAME filename

Usage

Anywhere.

Permissions

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

Side effects

Automatic commit.

Description

Each database is held in one or more files. A dbspace is an internal name 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 to 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 large amounts of 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 SQL 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 SQL Anywhere where to find the root database file. The root database file (which has the default dbspace name SYSTEM) holds the system tables, and SQL Anywhere looks in these system tables to find the location of the other dbspaces. SQL Anywhere then opens each of the other dbspaces.

Using ALTER DBSPACE with RENAME on a root file has no effect.

Examples

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'