Filter
Top Products
Working with databases
114
Adding dbspaces
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.
How the number of
dbspaces affects
resource use and
performance
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.