sp_iqindex and sp_iqindex_alt Procedure Examples

Use the example as a reference for sp_iqindex and sp_iqindex_alt usage.

Example 1

The following variations in syntax both return all indexes on columns with the name DepartmentID:

call sp_iqindex (NULL,'DepartmentID')

sp_iqindex column_name='DepartmentID'

table_ name	table_ owner	column_ name	index_ type
Departments	GROUPO	DepartmentID	FP
Departments	GROUPO	DepartmentID	HG
Employees	GROUPO	DepartmentID	FP

(Continued) index_name	unique_ index	location	dbspace_id	remarks
ASIQ_IDX_T201_C1_FP	N	Main	16387	(NULL)
ASIQ_IDX_T201_C1_HG	U	Main	16387	(NULL)
ASIQ_IDX_T202_C5_FP	N	Main	16387	(NULL)

Example 2

The following variations in syntax both return all indexes in the table Departments that is owned by table owner GROUPO:

sp_iqindex Departments,NULL,GROUPO

sp_iqindex table_name='Departments',table_owner='DBA'

table_ name	table_ owner	column_ name	index_ type
Departments	GROUPO	DepartmentHeadID	FP
Departments	GROUPO	DepartmentID	FP
Departments	GROUPO	DepartmentID	HG
Departments	GROUPO	DepartmentName	FP

(Continued) index_name	unique_ index	location	dbspace_id	remarks
ASIQ_IDX_T201_C3_FP	N	Main	16387	(NULL)
ASIQ_IDX_T201_C1_FP	N	Main	16387	(NULL)
ASIQ_IDX_T201_C1_HG	U	Main	16387	(NULL)
ASIQ_IDX_T201_C2_FP	N	Main	16387	(NULL)

Example 3

The following variations in syntax for sp_iqindex_alt both return indexes on the table Employees that contain the column City. The index emp_loc is a multicolumn index on the columns City and State. sp_iqindex_alt displays one row per column for a multicolumn index.

sp_iqindex_alt Employees,City

sp_iqindex_alt table_name='Employees',
               column_name='City'

table_ name	table_ owner	column_ name	index_ type
Employees	GROUPO	City	FP
Employees	GROUPO	City	HG
Employees	GROUPO	State	HG

(Continued) index_name	unique_ index	dbspace_id	remarks
ASIQ_IDX_T452_C7_FP	N	16387	(NULL)
emp_loc	N	16387	(NULL)
emp_loc	N	16387	(NULL)

The output from sp_iqindex for the same table and column is slightly different:

sp_iqindex Employees,City

sp_iqindex table_name='Employee',column_name='City'

table_ name	table_ owner	column_ name	index_ type
Employees	GROUPO	City	FP
Employees	GROUPO	City,State	HG

(Continued) index_name	unique_ index	dbspace_id	location	remarks
ASIQ_IDX_T452_C7_FP	N	16387	Main	(NULL)
emp_loc	N	16387	Main	(NULL)