Create a dbspace using the CREATE DBSPACE statement or the Sybase Central Create Dbspace wizard. A new dbspace can be on the same or a different disk drive as the existing dbspaces. You must have DBA or SPACE ADMIN authority.
See Chapter 6, “Physical Limitations,” in Reference: Building Blocks, Tables, and Procedures for the maximum sizes of dbspaces on raw devices and operating system files. On some platforms, you must enable large file system files to reach the maximum size.
You can specify SIZE and RESERVE only for the IQ store and IQ temporary store, not for the catalog store.
Sybase recommends that you create main stores on raw devices.
When you specify a raw device for a new dbspace, Sybase IQ automatically determines its file size and allocates the entire device for use as an IQ store. This may have unpredictable results on a file device.
If you indicate that the device is not raw, you can then specify the file size. The wizard verifies that the given path exists. If the path doesn’t exist, Sybase IQ returns an error.
The maximum number of dbspaces per database is an operating system limit that you can adjust; the maximum is 2,047 dbspaces per IQ database, plus a maximum of 12 dbspaces for the catalog store. Increasing the number of dbspaces has no real impact on memory use or performance; avoid situations where you approach the maximum.
On HP and AIX platforms, overlapped I/O performance improves
when you divide data among more dbspaces.
When data is stored on raw partitions, you can have one dbspace per drive. See Chapter 6, “Physical Limitations,” in Reference: Building Blocks, Tables, and Procedures for dbspace size limits.
When data is stored in a file system, you can take advantage of striping in the storage system. If you use operating system or hardware striping on a multiuser system, your stripe size should be a minimum of 1MB, or the highest size possible. The stripe size should be several times your IQ page size. You can also configure IQ to perform software striping.
For more information on disk striping and use of multiple dbspaces, see “Balancing I/O” in Performance and Tuning Guide.
Before adding dbspaces, you may want to estimate your space requirements. See “Estimating space and dbspaces required”. For the most efficient resource use, make your dbspaces small enough to fit on your backup media, and large enough to fill up the disk.
The following command creates a new dbspace called library which points to an operating system file named library.iq in the same directory as the IQ_SYSTEM_MAIN dbspace:
CREATE DBSPACE library USING FILE library 'library.iq' SIZE 100 MB IQ STORE
To create a dbspace in Sybase Central, see the online help or “Creating dbspaces” in Chapter 6, “Managing Dbspaces,” in Introduction to Sybase IQ.
After you add or drop a dbspace, issue a CHECKPOINT. System recovery begins after the most recent checkpoint.
