sp_iqindexfragmentation Procedure

Reports information about the percentage of page space taken up within the B-trees, garrays, and bitmap structures in SAP Sybase IQ indexes.

For garrays, the fill percentage calculation does not take into account the reserved space within the garray groups, which is controlled by the GARRAY_FILL_FACTOR_PERCENT option.

Syntax

dbo.sp_iqindexfragmentation ( ‘target ‘ )

target: table table-name | index index-name [...]

Privileges

Requires the MANAGE ANY DBASPACE system privilege. Users without the MANAGE ANY DBSPACE system privilege must be granted EXECUTE permission to run the stored procedure.

Usage

Parameter	Description
table-name	Target table `table-name` reports on all nondefault indexes in the named table.
index-name	Target index `index-name` reports on the named index. Each index-name is a qualified index name. You can specify multiple indexes within the table, but you must repeat the index keyword with each index specified.

Example

Reports the internal index fragmentation for the unique HG index DBA.prop_nu.prop_nu_a table:.

Index	IndexType	Btree_Node_pages	GARRAY_FILL_FACTOR_PERCENT
DBA.prop_nu.prop_nu_a	HG	8	25
SQLCODE:	0
Fill Percent	btree pages	garray pages	bitmap pages
0-10%	13	2	8
11-20%	1	8	0
21-30%	0	4	0
31-40%	3	20	0
41-50%	4	116	0
51-60%	6	4	0
61-70%	3	3	0
71-80%	4	1	0
81-90%	1	1	0
91-100%	192	276	0

Note: All percentages are truncated to the nearest percentage point. HG indexes also display the value of option GARRAY_FILL_FACTOR_PERCENT. Index types that use a B-tree also display the number of node (nonleaf) pages. These are HG, LF, WD, DATE, and DTTM.

If an error occurrs during execution of this stored procedure, the SQLCODE would be nonzero.