sp_iqestdbspaces Procedure

Estimates the number and size of dbspaces needed for a given total index size.

Syntax

sp_iqestdbspacesdb_size_in_bytes, iq_page_size,
min_#_of_bytes, max_#_of_bytes )

Permissions

DBA authority required. Users without DBA authority must be granted EXECUTE permission to run the stored procedure.

Description

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 (or else the estimate is incorrect).

sp_iqestdbspaces parameters

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).

Usage

sp_iqestdbspaces displays four types of recommendations, depending on how much of the data is unique:

Recommendations

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.