To avoid difficulties when a database or a particular dbspace is full, you should estimate your dbspace requirements before you create the database and the objects in it. You can run Sybase IQ stored procedures to estimate how much space and how many dbspaces your databases will require. See Chapter 7, “System Procedures,” in Reference: Building Blocks, Tables, and Procedures 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.
Run 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”.
Add totals under “RAW DATA index_size” for all tables together.
Run 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.
Each time you run sp_iqestjoin, select one of the suggested index sizes. If you know you will always join the tables with exact one-to-one matches, use the “Min Case index_size”. If you anticipate occasional one-to-many joins, use the “Avg Case index_size”. If you anticipate using numerous one-to-many joins, use the “Max Case index_size”.
Total the index_sizes you selected for all table pairs.
Add the join space total from step number 4 to the table space total from step number 2, doing a separate calculation for minimum and maximum join space.
Run the stored procedure sp_iqestdbspaces to determine how many dbspace files to create from the given space and what size they should be. Use the minimum and maximum total index sizes calculated in step number 5 as the minsize and maxsize parameters for this procedure, as follows:
sp_iqestdbspaces (dbsize [,iqpagesize] [,minsize] [,maxsize] ...
All these calculations are estimates. Results vary based on the columns and indexes you create for your database. For more information on these stored procedures, see Chapter 7, “System Procedures” in Reference: Building Blocks, Tables, and Procedures.
