USER_INDEX_INFO view

The USER_INDEX_INFO view returns the attributes of user indexes.

The values returned for the columns in the view are closely related to the values returned by the Retrieve User Index Attributes (QUSRUIAT) API.

Authorization: The caller must have:

*EXECUTE authority to the library containing the user index, and
*USE authority to the user index.

The following table describes the columns in the view. The system name is USRIDX_INF. The schema is QSYS2.

Table 1. USER_INDEX_INFO view
Column Name	System Column Name	Data Type	Description
USER_INDEX_LIBRARY	USRIDX_LIB	VARCHAR(10)	Library containing the user index.
USER_INDEX	USRIDX	VARCHAR(10)	Name of the user index.
ENTRY_TYPE	TYPE	VARCHAR(8)	The type of entries in the user index. FIXED Fixed-length entries VARIABLE Variable-length entries
ENTRY_LENGTH	LENGTH	INTEGER	When ENTRY_TYPE is FIXED, the length of each index entry. When ENTRY_TYPE is VARIABLE, the length of the longest entry that has ever been inserted into the index. Valid values are from 1 through 2000.
MAXIMUM_ENTRY_LENGTH	MAX_LENGTH	INTEGER	The maximum entry length any user index entry can have.
INDEX_SIZE	SIZE	CHAR(4)	The maximum size of the user index. 4 GB The maximum size of the user index is 4 gigabytes. 1 TB The maximum size of the user index is 1 terabyte.
IMMEDIATE_UPDATE	IMMEDIATE	VARCHAR(3)	Whether updates to the index are written to auxiliary storage on each update to the index. NO No immediate update YES Immediate update
OPTIMIZATION	OPTIMIZE	VARCHAR(10)	The optimization method used for user index maintenance. RANDOM Random references SEQUENTIAL Sequential references
KEY_INSERTION	KEYED	VARCHAR(3)	Whether inserts into the index are by key. NO No insertion by key YES Insertion by key
KEY_LENGTH	KEY_LENGTH	INTEGER Nullable	The length of the key. Contains the null value when KEY_INSERTION is NO.
ENTRY_TOTAL	TOTAL	INTEGER	The number of entries currently in the index.
ENTRIES_ADDED	ADDED	INTEGER	The number of entries added to the user index.
ENTRIES_REMOVED	REMOVED	INTEGER	The number of entries removed from the user index.
OBJECT_DOMAIN	DOMAIN	VARCHAR(7)	The domain of the object. SYSTEM The object is in the system domain. USER The object is in the user domain.
TEXT_DESCRIPTION	TEXT	VARCHAR(50) Nullable	The text description of the user index. Contains the null value if the user index has no description.

Example

Return a list of attributes for all user indexes in MYLIB.

SELECT * FROM QSYS2.USER_INDEX_INFO 
  WHERE USER_INDEX_LIBRARY = 'MYLIB';