CREATE DBSPACE Statement

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

Syntax

Syntax 1

Use for catalog store dbspaces only (SQL Anywhere (SA) dbspaces).

CREATE DBSPACE dbspace-name AS file-path CATALOG STORE

Syntax 2

Use for IQ main store dbspaces.

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

Syntax 3

Use for RLV dbspaces.

CREATE DBSPACE dbspace-name USING file-specification 
RLV STORE

Parameters

file-specification – { single-path-spec | new-file-spec [, ...] }
single-path-spec – 'file-path' | iq-file-opts
new-file-spec – FILE logical-file-name | 'file-path' iq-file-opts
iq-file-opts – [ [ SIZE ] file-size ] …[ KB | MB | GB | TB ] ] [ RESERVE size …[ KB | MB | GB | TB ] ]
iq-dbspace-opts – [ STRIPING ] {ON | OFF} ] …[ STRIPESIZEKB sizeKB ]

Examples

Example 1 – Create a dbspace called DspHist for the IQ main store with two files on a UNIX system. Each file is 1GB in size 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 – Create 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;

Example 4 – Creates a RLV store dbspace called d1:

CREATE DBSPACE d1  
USING FILE f1  
'f1.iq' SIZE 100 RLV STORE;

Usage

CREATE DBSPACE creates a new dbspace for the IQ main store, catalog store, or RLV store. The dbspace you add can be on a different disk device than the initial dbspace, allowing you to create stores that are 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. You can specify one or more dbfiles 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 that was created in a version earlier than SAP Sybase IQ 15.3. You can add additional files to the IQ_SYSTEM_TEMP dbspace using the ALTER DBSPACE ADD FILE syntax.

Note: Creating a RLV dbspace containing a minimum of one file is a prerequisite for RLV storage. Before enabling RLV storage on a simplex server, check that the RLV dbspace exists.

RESERVE clause—Specifies the size in kilobytes (KB), megabytes (MB), gigabytes (GB), or terabytes (TB) of space to reserve, so that the dbspace can be increased in size in the future. The size parameter can be any number greater than 0; megabytes is the default. You cannot change the reserve after the dbspace dbfile is created.

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.

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, specify file names that are links to different files. SAP Sybase IQ cannot detect the target where linked files point. Even if the file names in the command differ, make sure they do not point to the same operating system file.

dbspace-name and dbfile-name are internal names for dbspaces and dbfiles. filepath is the actual operating system file name of the dbfile, with a preceding path where necessary. 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.

SIZE clause—Specifies the size, from 0 to 4 terabytes, of the operating system file specified in filepath. The default depends on the store type and block size. For the IQ main store, the default number of bytes equals 1000* the block size. You cannot specify the SIZE clause for the catalog store.

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

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

STRIPESIZEKB clause—Specifies the number of kilobytes (KB) to write to each file before the disk striping algorithm moves to the next stripe for the specified dbspace.

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

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

Side effects:

Automatic commit
Automatic checkpoint.

Standards

SQL—Vendor extension to ISO/ANSI SQL grammar.
Sybase—Not supported by Adaptive Server Enterprise.

Permissions

Requires the MANAGE ANY DBSPACE system privilege.