Creates a new dbspace and the associated database file. This file can be on a different device than the initial dbspace.
CREATE DBSPACE dbspace-name AS filename
... [ { IQ STORE | IQ TEMPORARY STORE
| IQ LOCAL STORE | CATALOG STORE } ]
... [ [SIZE] file-size ]
... [ RESERVE sizeMB ]
On Windows, create a dbspace called mydb_tmp_2 to add 200 MB to the IQ Temporary Store of the current IQ database (mydb).
CREATE DBSPACE mydb_tmp_2 AS 'e:\\s2\\data\\mydb_2.iqtmp' IQ TEMPORARY STORE SIZE 200 ;
The CREATE DBSPACE statement creates a new database file called a dbspace. When a database is first initialized using CREATE DATABASE, it creates several database files by default, including:
db-name.db is the catalog dbspace containing the system tables and stored procedures describing the database and any standard Adaptive Server Anywhere database objects you add. It is known as the Catalog Store, and has the dbspace name SYSTEM.
db-name.iq is the main data dbspace containing the IQ tables and indexes. It is known as the IQ Store, and has the dbspace name IQ_SYSTEM_MAIN.
db-name.iqtmp is the initial temporary dbspace containing the temporary tables generated by certain queries. It is known as the IQ Temporary Store, and has the dbspace name IQ_SYSTEM_TEMP.
CREATE DBSPACE adds a new dbspace to one of these stores. The default is the IQ 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.
Multiplex databases have a shared IQ Store, where they
share all dbspaces, and a local IQ Store. The local IQ Store consists
of dbspaces that are managed by only one query server and are not
visible to any other query server. To create dbspaces for a multiplex
database, Sybase recommends using Sybase Central rather than the CREATE
DBSPACE statement. See Chapter 5, “Working with
Database Objects” in the Sybase IQ System Administration Guide for details.
When you create a database or a dbspace, the path for the Temporary Store must be unique. If your CREATE DBSPACE command specifies the identical path and filename for these two stores, you receive an error. You can cause a unique path in any of these ways:
Specify a different extension for each file (for example, mydb.iq and mydb.iqtmp)
Specify a different file name (for example, mydb.iq and mytmp.iq)
Specify a different pathname (for example, /iqfiles/main/iq and /iqfiles/temp/iq) or different raw partitions
WARNING! On UNIX platforms, to maintain database consistency you must be careful to specify filenames that are links to different files. IQ cannot detect the target where linked files point. Even if the filenames in the command differ, it is your responsibility to make sure they do not point to the same file.
The dbspace-name is an internal name for the dbspace. The filename is the actual filename of the dbspace, with a path where necessary. A filename without an explicit directory is created in the same directory as the initial dbspace of that store. Any relative directory is relative to that initial dbspace. Each dbspace-name must be unique in a database. Dbspace names are case sensitive for databases created with CASE RESPECT.
SIZE clause For operating system files, specifies the size in MB, from 0 to 4194304 (0 to 4 terabytes), of the file you specify in filename. See Chapter 8, “Physical Limitations” for platform-specific limits. 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. For the IQ Temporary Store the default number of bytes equals 100 * 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 1000 blocks for IQ Main Store and 100 blocks for IQ Temporary 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.
RESERVE clause Specifies the size in megabytes of free list space to reserve, so that the dbspace can be increased in size in the future. The sizeMB parameter can be any number greater than 0. The reserve cannot be changed after the dbspace 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.
Reserve and mode for temporary dbspaces are lost if
the database is restored from a backup.
A database can have up to 2047 dbspaces, including the initial dbspaces created when you create the database. However, your operating system may limit the number of files per database.
Automatic commit. Automatic checkpoint.
Must have DBA authority.
“Working with database objects” and “Working with dbspaces” in Chapter 5, “Working with Database Objects” of the Sybase IQ System Administration Guide
