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, creates a dbspace called mydb_tmp_2 to add 200MB to the IQ Temporary Store of the current Sybase IQ database (mydb):
CREATE DBSPACE mydb_tmp_2 AS 'e:\\s2\\data\\mydb_2.iqtmp' IQ TEMPORARY STORE SIZE 200 ;
Adds a dbspace on a Windows raw device to a database:
CREATE DBSPACE main2 AS '\\\\.\\H:' IQ STORE
Always double the backslashes when naming raw devices on Windows in SQL statements.
CREATE DBSPACE 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 is named SYSTEM.
db-name.iq is the main data dbspace containing the IQ tables and indexes. It is known as the IQ Store, and is named 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 is called 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, 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 file name for these two stores, you receive an error. You can create 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 path name (for example, /iqfiles/main/iq and /iqfiles/temp/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 file.
The dbspace-name is an internal name for the dbspace. The filename is the actual file name 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 and an important note about initializing raw devices on Sun Solaris. 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 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 might limit the number of files per database.
Automatic commit. Automatic checkpoint.
Must have DBA authority.
Chapter 5, “Working with Database Objects,” in the Sybase IQ System Administration Guide.
