Types of Dbspaces

Each type of dbspace stores a particular type of SAP Sybase IQ data.

Dbspace Type	Data Stored	Files Contained by Dbspace	Number of Dbspaces
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 and rollback data for each committed transaction and each active checkpointed transaction, the incremental backup metadata, and database space and identity metadata. You may store IQ user objects here, but it is better to store them in other main dbspaces.	One or more	One
Other main dbspaces (also called user dbspaces)	IQ objects such as tables, indexes, and table metadata.	One or more	One or more
RLV_STORE	Real-time in-memory data from row-level versioning (RLV)-enabled tables.	One	One
IQ_SYSTEM_TEMP	Set of 1 to n temporary dbfiles that defines a single temporary dbspace for a standalone database or multiplex node. See the following table for details.	One or more	One
IQ_SHARED_TEMP	Set of 1 to n temporary dbfiles that define a single temporary dbspace shared by all multiplex nodes. See the following table for details.	One or more (initially has no files)	One
IQ_SYSTEM_MSG	External file that logs messages about database activity.	One per multiplex node	One

The dbspace of a table is implicitly or explicitly specified. For base tables, the value of the DEFAULT_DBSPACE option implicitly determines the dbspace location, or you can explicitly specify the location using the CREATE TABLE IN dbspace_name clause. Typically, create base tables in a dbspace in the IQ main store, but you can also create them without IQ indexes in a dbspace in the catalog store.

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

Always Stored in IQ_SYSTEM_TEMP	Always Stored in IQ_SHARED_TEMP	May Be Stored in Either IQ_SHARED_TEMP or IQ_SYSTEM_TEMP
Scratch space for recovery from server failure	Distributed temporary objects for distributed query processing	IQ temporary user objects such as IQ temporary tables and indexes
Local bitmaps for shared storage space management		Scratch space for local, nonversioning temporary objects
Temporary storage transaction state (savepoint roll forward and roll back)

Catalog Store
The catalog store contains the metadata for the IQ database.
IQ_SYSTEM_MAIN Dbspace
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.
Other User Main Dbspaces
Create user main dbspaces so that users do not place user tables or indexes in IQ_SYSTEM_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.
IQ Shared Temporary Dbspace
A single dbspace for the IQ shared temporary store, IQ_SHARED_TEMP, is created when you create a database or upgrade a 12.7 ESD #5 or higher IQ database to SAP Sybase IQ 16.0. This dbspace stores temporary structures that are shared among nodes for distributed query processing.
IQ Message File Dbspace
IQ_SYSTEM_MSG is a system dbspace that points to the file path of the database IQ message log file.

Parent topic: Manage Data Storage