Sybase 12.4.2 manual Estimating space and dbspaces required, 105

CHAPTER 3 Working with Database Objects

Space requirements for IQ Stores

Space requirements for Temporary Stores

The amount of data, and the number and types of indexes you create, determine how much space you need in your IQ database. If you run out of space when loading or inserting into a database, Adaptive Server IQ prompts you to create another dbspace, and then continues the operation after you add the dbspace.

In addition to any temporary tables you define explicitly, Adaptive Server IQ uses the Temporary Store as a temporary result space for sorts, hashes, and bitmaps during loads and deletions. The types of queries issued, the degree of concurrent use, and the size of your data, all determine how much space you need for your Temporary Store.

Estimating space and dbspaces required

To avoid difficulties when a database or a particular dbspace is full, you should estimate the amount of space and dbspaces you need before you create the database and the objects in it. Adaptive Server IQ provides stored procedures that you can run to estimate how much space and how many dbspaces your databases will require. See the Adaptive Server IQ Reference Manual for syntax and usage notes for each procedure.

Running the procedures in the sequence that follows can help you avoid running out of space for your objects.

1Run the stored procedure sp_iqestspace to estimate the amount of space you will need to create a database, based on the number of rows in the underlying database tables. Run the procedure once for each table that you plan to create, as follows:

sp_iqestspace table_name, rows[, iqpagesize]

The amount of space needed by each table is returned as “RAW DATA index_size”.

2Add totals under “RAW DATA index_size” for all tables together.

3Run the stored procedure sp_iqestjoin to estimate the amount of additional space required to create join indexes on tables that you want to join frequently. Run the procedure once for each pair of tables, as follows:

sp_iqestjoin table1, table1rows, table2, table2rows

[,relation] [,iqpagesize] ...

sp_iqestjoin suggests different index sizes depending on your queries.

105