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, SAP SQL Anywhere tables, and function definitions. | One | One or more |
| Other catalog dbspaces | SAP SQL Anywhere tables. | One | One or more |
| IQ_SYSTEM_MAIN | Database structures including 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 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) | Database 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) |