Types of dbspaces

There are six types of dbspaces, each designed to store a particular type of Sybase IQ data:

Dbspace type	Data stored	Files contained by dbspace	Number of dbspaces
The SYSTEM dbspace	System tables, views, stored procedures, SQL Anywhere tables, and function definitions	One	One or more
Other catalog dbspaces	SQL Anywhere tables	One	One or more
IQ_SYSTEM_MAIN	IQ database structures including IQ rollforward/rollback data for each committed transaction and each active checkpointed transaction, the incremental backup metadata, and database space and identity metadata. IQ user objects may be stored here but Sybase recommends that you put them in other main dbspaces.	One or more	One or more
Other main dbspaces (also called user dbspaces)	IQ objects such as tables, indexes, join indexes, and table metadata.	One or more	One or more
IQ_SYSTEM_TEMP	Set of 1 to n temporary dbfiles that define a single temporary dbspace for a standalone database or multiplex node	One or more	One
IQ_SYSTEM_MSG	External file that logs messages about database activity	One per multiplex node	One

In order to start a database, certain files must be available. See “Database startup guidelines”.

The dbspace of a table or join index is implicitly or explicitly specified. For base tables and join indexes, the value of the DEFAULT_DBSPACE option implicitly determines the dbspace location, or the location may be specified explicitly using the CREATE TABLE IN dbspace_name clause or CREATE JOIN INDEX IN dbspace_name clause. Base tables are typically created in a dbspace in the IQ main store, but may also be created without IQ indexes in a dbspace in the catalog store.

For global temporary tables, specify the IN SYSTEM clause to explicitly create a SA global temporary table. IQ temporary tables are created in IQ_SYSTEM_TEMP by default.

Catalog store

These tables contain the metadata for the IQ database. Metadata describes the layout of the IQ tables, columns, and indexes. The catalog store is sometimes referred to simply as the catalog.

The SYSTEM dbspace The IQ catalog dbspace named SYSTEM contains metadata for your IQ database, stored in the same format as tables in a SQL Anywhere relational database system. SQL Anywhere is a relational database system that can exist with or without IQ. You may have SQL Anywhere-style tables in your catalog store along with your IQ tables, or you may have a separate SQL Anywhere database. Each catalog dbspace contains exactly one file.

Other catalog dbspaces You may create SQL Anywhere tables in a separate dbspace from the SYSTEM dbspace.

IQ_SYSTEM_MAIN dbspace

The IQ_SYSTEM_MAIN dbspace is created at database creation or when you upgrade an older IQ database to Sybase IQ 15.1. IQ_SYSTEM_MAIN is a special dbspace that contains structures necessary for the database to open: the IQ checkpoint log, IQ rollforward/rollback data for each committed transaction and each active checkpointed transaction, the incremental backup metadata, and database space and identity metadata. IQ_SYSTEM_MAIN is always online when the database is open.

For guidelines for the sizing of IQ_SYSTEM_MAIN, see Table 5-1.

Other user main dbspaces

The best practice is to avoid placing user tables or indexes in IQ_SYSTEM_MAIN. The administrator may allow user tables to be created in IQ_SYSTEM_MAIN, especially if these tables are small, very important tables. However, the recommended method is that immediately after creating the database, the administrator creates a second main dbspace (a user main dbspace), revokes CREATE privilege in dbspace IQ_SYSTEM_MAIN from PUBLIC, grants CREATE privilege for the new main dbspace to selected users or PUBLIC, and sets PUBLIC.DEFAULT_DBSPACE to the new user main dbspace.

For example:

CREATE DBSPACE user_main USING FILE user_main
'user_main1' SIZE 10000;
GRANT CREATE ON user_main TO PUBLIC;
REVOKE CREATE ON IQ_SYSTEM_MAIN FROM PUBLIC;
SET OPTION PUBLIC.DEFAULT_DBSPACE = 'user_main';

IQ temporary dbspace

A single dbspace for the IQ temporary store, IQ_SYSTEM_TEMP, is created when you create a database or upgrade an older IQ database Sybase IQ 15.1

Each IQ dbspace may contain any number of files. The only limit is that the total number of IQ files is 16384.

IQ message file dbspace

IQ_SYSTEM_MSG is a system dbspace that points to the file path of the database IQ message log file. IQ_SYSTEM_MSG is not considered a store because it doesn’t store any data.

The IQ_SYSTEM_MSG dbspace has one file per multiplex node. By default, the physical file name for the message file on a simplex server or a coordinator of a multiplex is <dbname>.iqmsg. The physical file name for the IQ message file on a secondary node in a multiplex is <servername>.iqmsg.

IQ_SYSTEM_MSG is not an IQ store dbspace, so ALTER commands such as READONLY and OFFLINE do not apply to IQ_SYSTEM_MSG.