User's Guide
   PART 4. Database Administration and Advanced Use
     CHAPTER 23. Working with Database Files       

Using additional dbspaces

This section describes how to use additional database files, named dbspaces.

When a database is initialized, it is composed of one database file. This first database file is called the root file. All database objects and all data are placed in the root file.

Each database file has a maximum size of two Gb, so you may wish to divide large databases among more than one file. On Windows NT drives using the NTFS file system, this limitation is removed and files can be up to one terabyte.

You create a new database file, or dbspace, from Sybase Central, or using the CREATE DBSPACE statement. A new dbspace may be on the same disk drive as the root file or on another disk drive. DBA authority is required to create database files.

     For more information, see CREATE DBSPACE statement.

For each database, you can create up to twelve dbspaces, including the root file.

Placing tables in dbspaces 

    When created, a new dbspace has no contents. When you create a new table you can place it in a specific dbspace with an IN clause in the CREATE TABLE statement. If no IN clause is used, the table is placed in the root file.

Each table must be contained in a single dbspace. By default, indexes are placed in the same dbspace as their table, but they can be placed in a separate dbspace by supplying an IN clause.

     For information on creating tables, see Creating tables.

Example 

The following command creates a new dbspace called library in the file library.db in the same directory as the root file:

CREATE DBSPACE library
AS 'library.db'

To create a table and place it in the library dbspace, you can use the following command:

CREATE TABLE Library_Books (
title char(100),
author char(50),
isbn char(30)
) IN library

Splitting existing databases 

If you wish to split existing database objects among several dbspaces, you need to unload your database and modify the command file for rebuilding the database. To do so, add IN clauses to specify the dbspace for each table you do not wish to place in the root file.

Creating a dbspace in Sybase Central 

  To create a dbspace in Sybase Central:

1. Connect to the database.

2. Click the DB Spaces folder for that database.

3. Double-click Add DB Space in the right panel.

4. Enter the dbspace name and filename.

5. Click OK to create the dbspace.

  Preallocating space for database files

Adaptive Server Anywhere automatically takes new disk space for database files as needed. Unless you are working with a large database with a high rate of inserts and deletes, you do not need to worry about explicitly allocating space for database files.

Rapidly changing database files could lead to excessive file fragmentation on the disk and possible performance problems. You may pre-allocate disk space for database files or for transaction logs in order to prevent this. You do this using the ALTER DBSPACE statement.

For example, the following statement adds 200 pages to the database file with dbspace name library. The database page size is fixed when the database is created.

ALTER DBSPACE library
ADD 200

     For more information on this statement, see ALTER DBSPACE statement.

Running a disk defragmentation utility after pre-allocating disk space helps ensure that the database file is not fragmented over many disjoint areas of the disk drive. Performance can suffer if there is excessive fragmentation of database files.

Preallocating disk space in Sybase Central 

  To preallocate disk space for a dbspace in Sybase Central:

1. Connect to the database.

2. Click the DB Spaces folder for that database.

3. Double-click the dbspace in the right panel.

4. Click Add Pages, and enter the number of database pages to preallocate.

5. Click OK.