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

SQL Anywhere 17 » SQL Anywhere Server - Database Administration » Database creation » Database file types

Additional dbspaces considerations

Additional database files allow you to cluster related information in separate files.

Note For most databases, a single database file is enough. However, for users of large databases, additional database files are sometimes necessary.

When you initialize a database, it contains one database file. This first database file is called the main file or the system dbspace. By default, all database objects and all data are placed 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 (the main file and 12 dbspaces). Each table, together with its indexes, must be contained in a single database file. The SQL statement CREATE DBSPACE adds a new file to the database.

Temporary tables are only created in the temporary dbspace.

There are several ways to specify the dbspace where a base table or other database object is created. In the following lists, the location specified by methods occurring earlier in the list take precedence over those occurring later in the list.

  1. IN DBSPACE clause (if specified)

  2. default_dbspace option (if set)

  3. system dbspace

If a dbspace name contains a period and is not quoted, the database server generates an error for the name.

Each database file has a maximum allowable size of 228 (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 (228*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 some older file systems restrict file size to a maximum of 2 GB, many file systems, such as Windows 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.

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

The SYSDBSPACE system view contains information about all the dbspaces for a database.

Splitting existing databases

To split existing database objects among multiple dbspaces, you must unload your database and modify the generated script 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.