CREATE DBSPACE statement

Description

Creates a new dbspace and the associated dbfiles for the IQ main store or catalog store.

Syntax

Syntax 1

Use for catalog store dbspaces only (SA dbspaces).

CREATE DBSPACE dbspace-name AS file-path CATALOG STORE

Syntax 2

Use for IQ dbspaces.

CREATE DBSPACE dbspace-name USING file-specification 
[ IQ STORE ] iq-dbspace-opts

Parameters

Examples

Example 1

Creates a dbspace called DspHist for the IQ main store with two files on a UNIX system. Each file has 1GB and can grow 500MB:

CREATE DBSPACE DspHist USING FILE
FileHist1 '/History1/data/file1'
SIZE 1000 RESERVE 500,
FILE FileHist2 '/History1/data/file2'
SIZE 1000 RESERVE 500;

Example 2

Creates a second catalog dbspace called DspCat2:

CREATE DBSPACE DspCat2 AS 
'catalog_file2'
CATALOG STORE;

Example 3

Creates an IQ main dbspace called EmpStore1 for the IQ store (three alternate syntax examples):

CREATE DBSPACE EmpStore1
USING FILE EmpStore1
'EmpStore1.IQ' SIZE 8 MB IQ STORE;

CREATE DBSPACE EmpStore1
USING FILE EmpStore1
'EmpStore1.IQ' 8 IQ STORE;

CREATE DBSPACE EmpStore1
USING FILE EmpStore1
'EmpStore1.IQ' 8;

Usage

CREATE DBSPACE creates a new dbspace for the IQ main store or the catalog store. The dbspace you add can be on a different disk device than the initial dbspace, allowing the creation of stores larger than one physical device.

Syntax 1 creates a dbspace for the catalog store, where both dbspace and dbfile have the same logical name. Each dbspace in the catalog store has a single file.

new-file-spec creates a dbspace for the IQ main store. One or more dbfiles can be specified for the IQ main store. The dbfile name and physical file path are required for each file and must be unique.

The dbspace name and dbfile names are always case insensitive. The physical file paths have the case sensitivity of the operating system, if the database is CASE RESPECT and are case insensitive, if the database is CASE IGNORE.

You cannot create a dbspace for an IQ temporary store. A single temporary dbspace, IQ_SYSTEM_TEMP, is created when you create a new database or upgrade one created prior to Sybase IQ 15.1. You can add additional files to the IQ_SYSTEM_TEMP dbspace using the ALTER DBSPACE ADD FILE syntax.

If you do not specify striping or stripe size, the default values of the options DEFAULT_DISK_STRIPING and DEFAULT_KB_PER_STRIPE apply.

When RESERVE is specified, the database uses more space for internal (free list) structures. If reserve size is too large, the space needed for the internal structures can be larger than the specified size, which results in an error.

See CREATE DATABASE statement for the names and types of files created by default.

For information on creating dbspaces for a multiplex database, see Using Sybase IQ Multiplex.

You can create a unique path in any of these ways:

Specify a different extension for each file (for example, mydb.iq)

Specify a different file name (for example, mydb2.iq)

Specify a different path name (for example, /iqfiles/main/iq) or different raw partitions

WARNING! On UNIX platforms, to maintain database consistency you must specify file names that are links to different files. Sybase IQ cannot detect the target where linked files point. Even if the file names in the command differ, it is your responsibility to make sure they do not point to the same operating system file.

The dbspace-name and dbfile-name are internal names for dbspaces and dbfiles. The filepath is the actual operating system file name of the dbfile, with a preceding path where necessary. A filepath without an explicit directory is created in the same directory as the catalog store of the database. Any relative directory is relative to the catalog store.

A SIZE value of 0 creates a dbspace of minimum size, which is 8Mb for IQ main store.

For raw partitions, do not specify SIZE explicitly. Sybase IQ sets this parameter to the maximum raw partition size automatically, and returns an error if you attempt to specify another size.

A database can have up to (32k - 1) dbspaces, including the initial dbspaces created when you create the database. However, your operating system might limit the number of files per database.

Automatic commit. Automatic checkpoint.

Standards

Permissions

Must have DBA authority.