REMOVE_USER_INDEX_ENTRY and REMOVE_USER_INDEX_ENTRY_BINARY table functions
The REMOVE_USER_INDEX_ENTRY and REMOVE_USER_INDEX_ENTRY_BINARY table functions remove one or more entries from a user index (*USRIDX).
The values used by the table functions are closely related to the values handled by the Remove User Index Entries (QUSRMVUI) API.
Authorization: The caller must have:
- *EXECUTE authority to the library containing the user index, and
- *CHANGE authority to the user index.
The schema is QSYS2.
- user-index
- A character string containing the name of the user index for the removal of entries.
- user-index-library
- A character string containing the name of the library where the user index is located. Can be
one of the following special values:
- *CURLIB
- The current library is used.
- *LIBL
- The library list is used.
- operation
- The operation to be used for comparing remove-value and remove-value-end and the index value.
- remove-value
- A string that specifies the value to compare with the index value to determine whether an index entry is to be removed.
- remove-value-end
- A string that specifies the value to compare with the index value as the upper bound for BETWEEN. The lengths of remove-value and remove-value-end must be the same.
- max-remove
- An integer value that specifies the maximum number of user index entries satisfying the remove comparison that should be removed. A value of -1 can be used to remove all index entries that satisfy the remove comparison. The default is -1.
The result of the function is a table containing one row for each index entry that was removed with the format shown in the following table. All columns are nullable.
Column Name | Data Type | Description |
---|---|---|
ORDINAL_POSITION | INTEGER | The relative position of this row in the set of removed index entries. |
USER_INDEX_LIBRARY | VARCHAR(10) | The name of the library where the user index was found. |
USER_INDEX | VARCHAR(10) | The name of the user index. |
REMOVED_ENTRY | VARCHAR(2000) | The data for the index entry that was removed, in character form. The value includes both the key and the entry data. |
REMOVED_ENTRY_BINARY | VARBINARY(2000) | The data for the index entry that was removed, in binary form. This is the raw bytes of data. The value includes both the key and the entry data. |
Example
- Remove all index entries with a key value less than
'00173'.
SELECT * FROM TABLE(QSYS2.REMOVE_USER_INDEX_ENTRY(USER_INDEX => 'USRIX1', USER_INDEX_LIBRARY => 'APPLIB', OPERATION => 'LT', REMOVE_VALUE => '00173'));