USER_INDEX_ENTRIES table function

Edit online

The USER_INDEX_ENTRIES table function returns the entries of the specified user index (*USRIDX). The data is returned as character and binary data.

The values returned for the result columns of the table function are closely related to the values returned by the Retrieve User Index Entries (QUSRTVUI) API.

Authorization: The caller must have:

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

The schema is QSYS2.

user-index

A character or graphic string containing the name of the user index.

user-index-library

A character or graphic string containing the name of the library containing the user index. Can be one of the following special values:

*CURLIB: The job's current library is used.
*LIBL: The library list is used. This is the default.

The result of the function is a table containing one or more rows with the format shown in the following table. All the columns are nullable.

Table 1. USER_INDEX_ENTRIES table function
Column Name	Data Type	Description
ORDINAL_POSITION	INTEGER	The relative position of this row in the result data set.
USER_INDEX_LIBRARY	VARCHAR(10)	The library in which the user index was found.
USER_INDEX	VARCHAR(10)	The name of the user index.
KEY	VARCHAR(2000)	The key for the index entry in character form. Contains the null value if the user index is not keyed.
KEY_BINARY	VARBINARY(2000)	The key for the index entry in binary form. This is the raw form of the data. Contains the null value if the user index is not keyed.
ENTRY	VARCHAR(2000)	The data for the index entry in character form. Contains the null value if the entry contains only a key value.
ENTRY_BINARY	VARBINARY(2000)	The data for the index entry in binary form. This is the raw form of the data. Contains the null value if the entry contains only a key value.

Example

Look at all the entries in user index IX1 in TESTLIB.

SELECT * FROM TABLE(QSYS2.USER_INDEX_ENTRIES(
                                             USER_INDEX => 'IX1', 
                                             USER_INDEX_LIBRARY => 'TESTLIB'))
  ORDER BY ORDINAL_POSITION;