Reference Manual
   CHAPTER 9. SQL Statements     

CREATE DBSPACE statement

Function 

To create a new database file.

Syntax 

CREATE DBSPACE dbspace-name
AS filename

Permissions 

Must have DBA authority.

Side effects 

Automatic commit.

See also 

DROP statement

Using additional dbspaces

Description 

The CREATE DBSPACE statement creates a new database file. When a database is initialized, it is composed of one file. All tables and indexes created are placed in that file. CREATE DBSPACE adds a new file to the database. This file can be on a different disk drive than the root file, which means that the database can be larger than one physical device.

The dbspace-name parameter is an internal name for the database file. The filename parameter is the actual name of the database file, with a path where necessary.

For each database, there is a limit of twelve dbspaces, including the root file.

A filename without an explicit directory is created in the same directory as the main database file. Any relative directory is relative to the main database file. The filename is a filename on the server machine. When you are using the database server for NetWare, the filename should use a volume name (not a drive letter) when an absolute directory is specified.

Each table, including its associated indexes, is contained entirely within one database file. The IN clause of the CREATE TABLE statement specifies the dbspace into which a table is placed. Tables are put into the root database file by default.

Standards and compatibility 

• SQL/92     Vendor extension.

• Sybase     Not supported by Adaptive Server Enterprise.

Example 

• Create a dbspace called library to hold the LibraryBooks table and its indexes.

  CREATE DBSPACE library
  AS 'e:\dbfiles\library.db' ;
  
  CREATE TABLE LibraryBooks (
  title char(100),
  author char(50),
  isbn char(30),
  ) IN library ;