Displays the number of blocks used per index per main or local dbspace for one or all dbspaces.
sp_iqdbspaceinfo [ ‘dbspace-name-pattern’ ] [,’local’]
DBA authority required.
“sp_iqdbspace procedure”, “sp_iqindexinfo procedure”, “sp_iqspaceinfo procedure”, and “sp_iqrelocate procedure”
Chapter 5, “Working with Database Objects” in the Sybase IQ System Administration Guide
dbspace-name-pattern If specified, sp_iqdbspaceinfo displays output only for dbspaces that match LIKE pattern. sp_iqdbspaceinfo displays all dbspace names, if dbspace-name-pattern is not specified.
local The local keyword is specified to enable the display of objects in the multiplex local IQ store. By default on a query server, sp_iqdbspaceinfo displays information about the shared main IQ store on a query server.
The sp_iqdbspaceinfo stored procedure shows the DBA which objects reside on each dbspace. The DBA can use this information to determine which objects must be relocated before a dbspace can be dropped.
The results of sp_iqdbspaceinfo are displayed from the point of view of the version seen by the transaction running the command. Blocks used by other versions are not shown.
Column name |
Description |
|---|---|
dbspace_name |
Name of the dbspace |
Object |
Table, index, or join index name |
MinBlk |
First block used by this object on this dbspace |
MaxBlk |
Last block used by this object on this dbspace; useful for determining which objects must be relocated before the dbspace is resized to a smaller size |
ObjSize |
Size of data for this object on this dbspace |
DBSpSz |
Size of the dbspace |
The following output displays information about all main dbspaces.
sp_iqdbspaceinfo;
dbspace_name |
Object |
MinBlk |
MaxBlk |
ObjSize |
DBSpSz |
|---|---|---|---|---|---|
dbspacedb2 |
t2 |
1045495 |
1045495 |
8K |
10M |
dbspacedb2 |
t2.DBA.t2c1hng |
1045537 |
1045553 |
136K |
10M |
dbspacedb3 |
t1 |
2090913 |
2091321 |
200K |
10M |
dbspacedb3 |
t1.DBA.ASIQ_IDX_T429_C1_FP |
2090914 |
2091316 |
288K |
10M |
dbspacedb3 |
t1.DBA.t1c1hg |
2090931 |
2091280 |
304K |
10M |
dbspacedb3 |
t2 |
2090930 |
2091261 |
192K |
10M |
dbspacedb3 |
t2.DBA.ASIQ_IDX_T430_C1_FP |
2091027 |
2091277 |
288K |
10M |
The following output displays information about a specific dbspace in the database:
sp_iqdbspaceinfo IQ_SYSTEM_MAIN;
dbspace_name |
Object |
MinBlk |
MaxBlk |
ObjSize |
DBSpSz |
|---|---|---|---|---|---|
IQ_SYSTEM_MAIN |
t1 |
82 |
125 |
40K |
10M |
IQ_SYSTEM_MAIN |
t1.DBA.ASIQ_IDX_T429_C1_FP |
109 |
322 |
136K |
10M |
IQ_SYSTEM_MAIN |
t1.DBA.t1c1hg |
127 |
305 |
152K |
10M |
IQ_SYSTEM_MAIN |
t2 |
84 |
107 |
32K |
10M |
IQ_SYSTEM_MAIN |
t2.DBA.ASIQ_IDX_T430_C1_FP |
126 |
321 |
136K |
10M |
