Estimating Space and Dbspaces Required

To avoid difficulties when a database or a particular dbspace is full, estimate dbspace requirements before you create the database and the objects in it.

1. Run 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. For each table that you plan to create: sp_iqestspace table_name, rows[, iqpagesize]

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

2. Add totals under “RAW DATA index_size” for all tables together.
3. 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”.

4. Add together the index_sizes you selected for all table pairs.
5. Add the join space total from step number 4 to the table space total from step number 2, performing a separate calculation for minimum and maximum join space.
6. Run sp_iqestdbspaces to determine how many dbspace files to create from the given space and what size they should be. Use the total index sizes calculated in step number 5 as the minsize and maxsize parameters:

   sp_iqestdbspaces (dbsize [,iqpagesize] 
     [,minsize] [,maxsize] ...