You create a new database file—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 authority to create new dbspaces.
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 only specify SIZE and RESERVE 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 determines its file size automatically and allocates the whole device for use as an IQ store. This may have unpredictable results if the device is actually a file device and Sybase does not recommend this practice.
If you indicate that the device is not raw, then the wizard enables the File size field where you can specify the file size. The wizard also verifies that the given path exists before moving to the next page.
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. However, you should never allow a situation where you come close to the maximum. Increasing the number of dbspaces has no real impact on memory use or performance.
On HP and AIX platforms, your use of overlapped I/O
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. In any case, your stripe size should be several times your IQ page size. IQ can also be configured 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 any more dbspaces you may want to estimate your space requirements. See “Estimating space and dbspaces required” for details of how to estimate space. 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 in the file 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, it's a good idea to issue a CHECKPOINT. In the event system recovery is needed, it begins after the most recent checkpoint.
