sp_iqindexinfo procedure

Function

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.

Syntax

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

Permissions

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

Usage

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.

You cannot specify a join index by name. Use the database 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.

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.

Description

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.

Table 7-34: sp_iqindexinfo columns

Column name

Description

Object

Table, index, or join 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

Examples

Displays index information about the table t2:

sp_iqindexinfo 'table t2';

Object

dbspace_name

ObjSize

DBSpPct

MinBlk

MaxBlk

t2

IQ_SYSTEM_MAIN

32K

               1

         84

        107

t2

dbspacedb2

160K

               2

1045495

1045556

t2

dbspacedb3

8K

               1

2090930

2090930

t2.DBA.ASIQ_IDX_T430_C1_FP

IQ_SYSTEM_MAIN

136K

               2

        126

        321

t2.DBA.ASIQ_IDX_T430_C1_FP

dbspacedb3

152K

               2

2091032

2091053

t2.DBA.t2c1hng

dbspacedb2

136K

               2

1045537

1045553

See also

“sp_iqdbspace procedure”, “sp_iqdbspaceinfo procedure”, “sp_iqspaceinfo procedure”.

Chapter 5, “Working with Database Objects” in the System Administration Guide: Volume 1

Appendix A, “Multiplex Reference” in Using Sybase IQ Multiplex.