Sybase 12.4.2 manual Adding dbspaces, 114

Working with databases

Adding dbspaces

How the number of dbspaces affects resource use and performance

When you create a database, it has only one file for storing permanent IQ data, one file for storing Catalog data, and one file each for the IQ message log and the Temporary Store. Each of these files is a dbspace, as described in “Creating a database”. Initially, the definitions of all IQ database objects go into the SYSTEM dbspace (the Catalog Store), and all IQ data is placed in the IQ_SYSTEM_MAIN dbspace (the IQ Store). Each dbspace has a maximum size of 128GB, depending on file size limits of your operating system and version. On some platforms you must enable large file system files to reach this maximum.

You can only specify SIZE for the IQ Store and IQ Temporary Store, not for the Catalog Store.

In the large databases typical of a data warehouse, you will need to add dbspaces to any database. You create a new database file—a dbspace—using the CREATE DBSPACE statement, or the Sybase Central Add 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.

When you create a new dbspace, it has no contents. When you create tables and indexes and load them, Adaptive Server IQ distributes the data as equally as possible among any existing dbspaces that are not already full. This technique optimizes performance.

Because Adaptive Server IQ fills dbspaces in this way, you cannot specify that a particular IQ table be loaded into a particular dbspace. You can only indicate the IQ Store as the dbspace IQ_SYSTEM_MAIN, and the Temporary Store as the dbspace IQ_SYSTEM_TEMP. The only way to control the location of a table within the IQ Store is to completely fill any existing IQ Store dbspaces, then define a new dbspace, and create and load the tables you want in it.

Note This behavior differs from that of Adaptive Server Anywhere, which allows you to place tables in a particular dbspace.

There is an absolute maximum of 2,048 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.

Note On HP and AIX platforms, your use of overlapped I/O improves when you divide data among more dbspaces.

114