Displays the number of blocks used per index per main dbspace for a given object. If the object resides on several dbspaces, sp_iqindexinfo returns the space used in all dbspaces, as shown in the example.
sp_iqindexinfo ‘{ database | [ table table-name | index index-name ] [...] } [ resources resource-percent ]’
Simplex and multiplex.
You must have EXECUTE privilege on the system procedure, as well as the MANAGE ANY DBSPACE system privilege.
You can request index information for the entire database, or you can specify any number of table or index parameters. If a table name is specified, sp_iqindexinfo returns information on all indexes in the table. If an index name is specified, only the information on that index is returned.
If the specified table-name or index-name is ambiguous or the object cannot be found, an error is returned.
By default in a multiplex database, sp_iqindexinfo displays information about the shared IQ store on a secondary node. If individual tables or indexes are specified, the store to display is automatically selected.
resource-percent must be an integer greater than 0. The resources percentage allows you to limit the CPU utilization of the sp_iqindexinfo procedure by specifying the percent of total CPUs to use.
sp_iqindexinfo shows the DBA on which dbspaces a given object resides. The DBA can use this information to determine which dbspaces must be given relocate mode to relocate the object.
The results of sp_iqindexinfo are 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 |
|---|---|
| Object | Table or index name |
| Dbspace_name | Name of the dbspace |
| ObjSize | Size of data for this object on this dbspace |
| DBSpPct | Percent of dbspace used by this object |
| 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 |
Displays information about indexes in the Departments table:
sp_iqindexinfo 'table GROUPO.Departments';
| Object | DbspaceName | ObjSize | DBSpPct | MinBlk | MaxBlk |
|---|---|---|---|---|---|
| GROUPO.Departments | iq_main | 288K | 1 | 1,045,496.00 | 1,048,891.00 |
| GROUPO.Departments.ASIQ_IDX_T779_C1_FP | iq_main | 176K | 1 | 1,047,197.00 | 1,047,328.00 |
| GROUPO.Departments.ASIQ_IDX_T779_C2_FP | iq_main | 160K | 1 | 1,047,213.00 | 1,047,324.00 |
| GROUPO.Departments.ASIQ_IDX_T779_C3_FP | iq_main | 184K | 1 | 1,047,229.00 | 1,047,317.00 |
| GROUPO.Departments.ASIQ_IDX_T779_C3_HG | iq_main | 440K | 1 | 1,048,421.00 | 1,048,796.00 |
| GROUPO.Departments.ASIQ_IDX_T779_I4_HG | iq_main | 288K | 1 | 1,047,261.00 | 1,047,306.00 |