Sizing Guidelines for Main and Temporary Stores

The SAP Sybase IQ architecture influences guidelines for data storage.

The IQ_SYSTEM_MAIN dbspace holds all of the database metadata other than IQ table metadata. IQ table metadata is stored in the table's dbspace and the table version (TLV) log. If a node is down, the multiplex must store versions to synchronize them when the node comes back up. These versions may use large amounts of space.
Approximately 20 percent of the IQ_SYSTEM_MAIN dbspace is now used for preallocated free list space and not available for user data.
Because this version of SAP Sybase IQ performs more operations in parallel, it uses more temporary space than earlier versions.

Three factors influence the space required for the IQ_SYSTEM_MAIN store:

Versioning – the volume of versions maintained varies.
Nature of data and indexes.
Dynamic nature of the data – the capacity to load more data at any time.

While documentation can offer general guidelines, the combination of these factors makes each database’s requirements unique. For a development or report server with a total size under 500GB, an IQ_SYSTEM_MAIN file of 10 to 20GB may suffice.

Size Guidelines for IQ_SYSTEM_MAIN and IQ_SYSTEM_TEMP in Production Databases
Task	Guideline	Notes
Loading empty schema from iqunload -n output or for a small test database	10GB main, 5GB temporary	CREATE DATABASE sizes are in MB. The server must be at 12.7 ESD #5 or higher to use iqunload -n.
Creating new production database	If you are migrating a database, and use a raw device for your current IQ_SYSTEM_MAIN, assign a new unused raw device of your standard size. Total size of IQ_SYSTEM_MAIN should be at least 1/100 total database size, with a minimum 100GB main and 100GB reserve. If using raw disks for IQ_SYSTEM_MAIN, use multiple raw disks whenever possible. Multiple raw disks enable SAP Sybase IQ to stripe the data across devices, which improves performance. For IQ dbspaces in production, use a fault-tolerant file system implemented by a high-performance, redundant disk array (for example, RAID 5). For single-server systems, you can use a local file system, but multiplex systems require raw devices, ideally on a Storage Area Network device.	Omit ms_size if specifying a raw device. On a Windows system, only user accounts with Administrator privilege can access raw devices. The rawaccedit utility sets permission for devices for the current session. Always set the main reserve to 20 percent of IQ_SYSTEM_MAIN size. To set the main reserve, use the database option `MAIN_RESERVED_DBSPACE_MB`.
Creating main store for a multiplex	Double the space recommended for a simplex database, or at least 200GB main and 200GB reserve dbspace.

Example 1

In CREATE DATABASE syntax, default size units are in MB, not GB.

The following statement creates a database with 100GB IQ_SYSTEM_MAIN with 100GB reserve (for future expansion):

CREATE DATABASE 'test.db'
IQ PATH 'test.iq'
IQ SIZE 100000
IQ RESERVE 100000
TEMPORARY PATH 'test.iqtmp'
TEMPORARY SIZE 5000

Example 2

MAIN_RESERVED_DBSPACE_MB lets you control the amount of space SAP Sybase IQ sets aside in your IQ main store for certain small but critical data structures used during release savepoint, commit, and checkpoint operations.

Set the MAIN_RESERVED_DBSPACE_MB option value to 20 percent of the IQ_SYSTEM_MAIN SIZE. For example, if IQ_SYSTEM_MAIN is 100GB, set it to 20GB, as follows:

SET OPTION PUBLIC.MAIN_RESERVED_DBSPACE_MB = 20000

Example 3

Specify the IQ_SYSTEM_MAIN size in the database migration command.

The -ms_size parameter requires a value in MB, not GB. Omit -ms_size if specifying a raw device. For a raw device, you must specify an unused raw partition.

Create an IQ_SYSTEM_MAIN on a raw device on UNIX:

iqunload -au -ms_filename /dev/rdsk/c1t0d1 -c "UID=DBA;PWD=SQL;DBF=latest.db"

Create an IQ_SYSTEM_MAIN on a raw device on Windows:

iqunload -au -ms_filename \\\\.\\PhysicalDrive1 -c "UID=DBA;PWD=SQL;DBF=latest.db"