Estimates the number and size of dbspaces needed for a given total index size.
Requires the MANAGE ANY DBSPACE or ALTER DATABASE system privileges. Users without one of these system privileges must be granted EXECUTE permission.
Displays information about the number and size of dbspace segments based on the size of the database, the IQ page size, and the range of bytes per dbspace segment. This procedure assumes that the database was created with the default block size for the specified IQ page size; otherwise, the returned estimated values are incorrect.
Name |
Datatype |
Description |
|---|---|---|
db_size_in_bytes |
decimal(16) |
Size of the database in bytes. |
iq_page_size |
smallint |
The page size defined for the IQ segment of the database (must be a power of 2 between 65536 and 524288; the default is 131072). |
min_#_of_bytes |
int |
The minimum number of bytes per dbspace segment. The default is 20,000,000 (20MB). |
max_#_of_bytes |
int |
The maximum number of bytes per dbspace segment. The default is 2,146,304,000 (2.146GB). |
sp_iqestdbspaces reports several recommendations, depending on how much of the data is unique:
Recommendation |
Description |
|---|---|
min |
If there is little variation in data, you can choose to create only the dbspace segments of the sizes recommended as min. These recommendations reflect the best possible compression on data with the least possible variation. |
avg |
If your data has an average amount of variation, create the dbspace segments recommended as min, plus additional segments of the sizes recommended as avg. |
max |
If your data has a high degree of variation (many unique values), create the dbspace segments recommended as min, avg, and max. |
spare |
If you are uncertain about the number of unique values in your data, create the dbspace segments recommended as min, avg, max, and spare. You can always delete unused segments after loading your data, but creating too few can cost you some time. |