Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

Using additional dbspaces

When you initialize a database, it contains one database file. This first database file is called the main file. All database objects and all data are placed, by default, in the main file.

A dbspace is an additional database file that creates more space for data. A database can be held in up to 13 separate files (an initial file and 12 dbspaces). Each table, together with its indexes, must be contained in a single database file. The SQL command CREATE DBSPACE adds a new file to the database.

Each database file has a maximum allowable size of 2²⁸ (approximately 268 million) database pages. For example, a database file created with a database page size of 4 KB can grow to a maximum size of one terabyte (2²⁸*4 KB). However, in practice, the maximum file size allowed by the physical file system in which the file is created affects the maximum allowable size significantly.

While many commonly-employed file systems restrict file size to a maximum of 2 GB, some, such as the Windows file system using the NTFS file system, allow you to exploit the full database file size. In scenarios where the amount of data placed in the database exceeds the maximum file size, it is necessary to divide the data into more than one database file. As well, you may want to create multiple dbspaces for reasons other than size limitations, for example, to cluster related objects.

For information about the maximum file size allowed on the supported operating systems, see SQL Anywhere size and number limitations.

You can use the sa_disk_free system procedure to obtain information about space available for a dbspace. See sa_disk_free_space system procedure.

The SYSFILE system view contains information about all the dbspaces for a database, except the TEMPORARY, TRANSLOG, and TRANSLOGMIRROR dbspaces. See SYSFILE system view.

If you want to split existing database objects among multiple dbspaces, you must unload your database and modify the generated command file (named reload.sql by default) for rebuilding the database. In the reload.sql file, add IN clauses to the CREATE TABLE statements to specify the dbspace for each table you do not want to place in the main file.

• CREATE DBSPACE statement
• DB_EXTENDED_PROPERTY function [System]
• CREATE TABLE statement
• UNLOAD TABLE statement

Creating a dbspace
Pre-allocating space for database files
Deleting a dbspace