The SAP Sybase IQ architecture influences guidelines for data storage.
Three factors influence the space required for the IQ_SYSTEM_MAIN store:
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.
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. |
Creating new production database |
|
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. |
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
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
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"