CREATE DBSPACE Statement

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 
    IQ RLV STORE 

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 ]

Parameters

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.

RESERVE – 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.

dbspace-name and dbfile-name – internal names for dbspaces and dbfiles. 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 dbfiles per database.

file-path – the actual operating system file name of the dbfile, with a preceding path where necessary. file-path 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 – specifies the size, from 0 to 4 terabytes, of the operating system file specified in file-path. 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 – 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.

Examples

Example 1 – creates a dbspace called DspHist for the IQ main store with two dbfiles on a UNIX system. Each dbfile 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 – 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;

Example 4 – creates a RLV store dbspace called d1:
```
CREATE DBSPACE d1  
USING FILE f1  
'f1.iq' SIZE 100 IQ 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.

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.

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.

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.