sp_iqindexinfo procedure

Displays the number of blocks used per index per main or local dbspace for a given object.

sp_iqindexinfo ‘{ database | local 
| [ table table-name | index index-name ] [...] } 
[ resources resource-percent ]’

DBA authority required.

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.

Note that you cannot specify a join index by name. Use the database or local keyword to display join indexes.

If the specified table-name or index-name is ambiguous or the object cannot be found, an error is returned.

The local keyword is specified as a target to enable the display of objects in the IQ Local Store. By default in a multiplex database, sp_iqindexinfo displays information about the shared IQ Store on a query server. If individual tables or indexes are specified, then the store to display is selected automatically. You cannot specify targets from both the shared IQ Store and IQ Local Stores.

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.

The sp_iqindexinfo stored procedure 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 displayed from the point of view of the version seen by the transaction running the command. Blocks used by other versions are not shown.

The following command displays index information about the table t2:

sp_iqindexinfo 'table t2';

Because you cannot specify targets from both the shared IQ Store and IQ Local Stores, the following command returns an error, if local_tab1 is a local table and main_tab1 is a shared IQ table:

sp_iqindexinfo ‘table local_tab1 table main_tab1’