Removing data from a User Index using SQL

In a previous post I wrote about how to add and change data in a User Index. As I can do that, I also need is to delete data from the User Index too.

In IBM i 7.5 and 7.4TR6 introduces a couple of Table Functions that allow me to remove entries from a User Index:

• REMOVE_USER_INDEX_ENTRY
• REMOVE_USER_INDEX_ENTRY_BINARY

Both work in the same way, and I am going to describe how REMOVE_USER_INDEX_ENTRY works.

I am going to use the same User Index as I did in the earlier post.

--Create user index 01 CL:CALL PGM(MYLIB/CRTUSRIDX); -- Add entries to the User Index 02 CALL QSYS2.ADD_USER_INDEX_ENTRY ('MYUSRIDX','MYLIB','NO','First entry added','0010') ; 03 CALL QSYS2.ADD_USER_INDEX_ENTRY ('MYUSRIDX','MYLIB','NO','Second entry added','0020') ; 04 CALL QSYS2.ADD_USER_INDEX_ENTRY ('MYUSRIDX','MYLIB','NO','Third entry added','0015') ; 05 CALL QSYS2.ADD_USER_INDEX_ENTRY ('MYUSRIDX','MYLIB','NO','Fourth entry added','0030') ; 06 CALL QSYS2.ADD_USER_INDEX_ENTRY ('MYUSRIDX','MYLIB','NO','Fifth entry added','0040') ; 07 CALL QSYS2.ADD_USER_INDEX_ENTRY ('MYUSRIDX','MYLIB','NO','Sixth entry added','0050') ; 08 CALL QSYS2.ADD_USER_INDEX_ENTRY ('MYUSRIDX','MYLIB','NO','Seventh entry added','0055') ; 09 CALL QSYS2.ADD_USER_INDEX_ENTRY ('MYUSRIDX','MYLIB','NO','Eighth entry added','0060') ; 10 CALL QSYS2.ADD_USER_INDEX_ENTRY ('MYUSRIDX','MYLIB','NO','Ninth entry added','0070') ; 11 CALL QSYS2.ADD_USER_INDEX_ENTRY ('MYUSRIDX','MYLIB','NO','Tenth entry added','0100') ;

Line 1: This program creates the User Index in my library. I execute it in Run SQL Scripts using the Call command. The CL: "tells" Run SQL Scripts that this is a CL command rather than a SQL statement.

Lines 2 – 11: These are the SQL statements using the ADD_USER_INDEX_ENTRY procedure to insert entries into the User Index.

I can check that all of the entries I expect are added to the User Index with the following:

-- View contents 01 SELECT ORDINAL_POSITION,KEY,ENTRY 02 FROM TABLE(QSYS2.USER_INDEX_ENTRIES( 03 USER_INDEX_LIBRARY => 'MYLIB', 04 USER_INDEX => 'MYUSRIDX'))

Line 1: I am only interested in these columns. The rest I do not need for this example.

The results are:

ORDINAL_ POSITION KEY ENTRY -------- ---- ------------------- 1 0010 First entry added 2 0015 Third entry added 3 0020 Second entry added 4 0030 Fourth entry added 5 0040 Fifth entry added 6 0050 Sixth entry added 7 0055 Seventh entry added 8 0060 Eighth entry added 9 0070 Ninth entry added 10 0100 Tenth entry added

REMOVE_USER_INDEX_ENTRY has the following parameters:

01 SELECT * FROM TABLE(QSYS2.REMOVE_USER_INDEX_ENTRY( 02 USER_INDEX => 'MYUSRIDX', 03 USER_INDEX_LIBRARY => 'MYLIB', 04 OPERATION => 'EQ', 05 REMOVE_VALUE => '0000', 06 REMOVE_VALUE_END => '0000', 07 MAX_REMOVE => -1))

• USER_INDEX:  User Index name
• USER_INDEX_LIBRARY:  The library the User Index is in
• OPERATION:  I will describe these below
• REMOVE_VALUE:  Either a single key number, or the start of the key number range
• REMOVE_VALUE_END:  If a range operation is used then this is key number range
• MAX_REMOVE:  The maximum number of key entries to remove, will describe when this is used below

Valid Operations are:

• I think these operations are self-explanatory: EQ, GE, GT, LE, LT
  For these I would use the Remove Value parameter only.
• BETWEEN will delete keys in the range given.
  For this the Remove Value and Remove Value End are used.
• FIRST will delete the first x entries.
  The Maximum Remove is used for the number of entries to remove.
• LAST will delete the last x entries.
  The Maximum Remove is used for the number of entries to remove.

In this first example I am going to delete a single entry. In this example I want to delete the entry with the key value of 10.

-- Delete key 10 01 SELECT * FROM TABLE(QSYS2.REMOVE_USER_INDEX_ENTRY( 02 USER_INDEX => 'MYUSRIDX', 03 USER_INDEX_LIBRARY => 'MYLIB', 04 OPERATION => 'EQ', 05 REMOVE_VALUE => '0010'))

Line 4: The operation is 'EQ', for equal.

Line 5: The remove value is the key value I want to remove. As I have used the equal comparison I only provide the 'REMOVE_VALUE' parameter.

The table function returns the following for the removed key:

USER REMOVED ORDINAL_ INDEX_ USER_ _ENTRY_ POSITION LIBRARY INDEX REMOVED_ENTRY BINARY -------- ------- -------- ----------------------- -------- 1 MYLIB MYUSRIDX 0010First entry added F0F0F...

I can check the contents of the User Index.

-- List contents of user index 01 SELECT ORDINAL_POSITION,KEY,ENTRY 02 FROM TABLE(QSYS2.USER_INDEX_ENTRIES( 03 USER_INDEX_LIBRARY => 'MYLIB', 04 USER_INDEX => 'MYUSRIDX')) 05 LIMIT 1

Line 5: The Limit restricts the number of rows returned to just one.

ORDINAL_ POSITION KEY ENTRY -------- ---- ------------------- 1 0015 Third entry added

Key 15 is the first entry returned, which means that entry for key 10 was removed.

In the example below I want to remove all of the entries where the key is greater or equal to 30.

-- Remove all entries where key >= 30 01 SELECT ORDINAL_POSITION,REMOVED_ENTRY 02 FROM TABLE(QSYS2.REMOVE_USER_INDEX_ENTRY( 03 USER_INDEX => 'MYUSRIDX', 04 USER_INDEX_LIBRARY => 'MYLIB', 05 OPERATION => 'GE', 06 REMOVE_VALUE => '0030'))

Line 1: I only want the Ordinal position and Removed Key columns returned from REMOVE_USER_INDEX_ENTRY.

Line 5: Operation is 'GE', greater or equal, than the key in the remove value parameter.

Line 6: The key value is 30.

The Table Function returns the removed entries:

ORDINAL_ POSITION REMOVED_ENTRY -------- ------------------------ 1 0030Fourth entry added 2 0040Fifth entry added 3 0050Sixth entry added 4 0055Seventh entry added 5 0060Eighth entry added 6 0070Ninth entry added 7 0100Tenth entry added

These are the entries that were removed.

Now I am going to delete a range of entries using the 'BETWEEN' operation.

-- Delete keys between 20 - 60 01 SELECT ORDINAL_POSITION,REMOVED_ENTRY 02 FROM TABLE(QSYS2.REMOVE_USER_INDEX_ENTRY( 03 USER_INDEX => 'MYUSRIDX', 04 USER_INDEX_LIBRARY => 'MYLIB', 05 OPERATION => 'BETWEEN', 06 REMOVE_VALUE => '0020', 07 REMOVE_VALUE_END => '0060'))

Line 5: The Operation parameter is 'BETWEEN'.

Line 6: The Remove Value parameter is used for the from key.

Line 7: The Remove Value End is used for the through key value.

The following results are returned for the removed entries.

ORDINAL_ POSITION REMOVED_ENTRY -------- ----------------------- 1 0020Second entry added 2 0030Fourth entry added 3 0040Fifth entry added 4 0050Sixth entry added 5 0055Seventh entry added 6 0060Eighth entry added

Again I use the USER_INDEX_ENTRIES Table Function to list the remaining entries:

-- List contents of user index 01 SELECT ORDINAL_POSITION,KEY,ENTRY 02 FROM TABLE(QSYS2.USER_INDEX_ENTRIES( 03 USER_INDEX_LIBRARY => 'MYLIB', 04 USER_INDEX => 'MYUSRIDX'))

The results show that the entries have been removed from the User Index.

ORDINAL_ POSITION KEY ENTRY -------- ---- ------------------- 1 0010 First entry added 2 0015 Third entry added 3 0070 Ninth entry added 4 0100 Tenth entry added

Next up is the statement to delete the first five entries from the User Index.

-- Delete first 5 keys 01 SELECT ORDINAL_POSITION,REMOVED_ENTRY 02 FROM TABLE(QSYS2.REMOVE_USER_INDEX_ENTRY( 03 USER_INDEX => 'MYUSRIDX', 04 USER_INDEX_LIBRARY => 'MYLIB', 05 OPERATION => 'FIRST', 06 MAX_REMOVE => 5))

Line 5: The Operation is 'FIRST'.

line 6: The Max Remove parameter passes to the Table Function the number of entries to delete.

The Table functions returns the first five entries in its results:

ORDINAL_ POSITION REMOVED_ENTRY -------- ----------------------- 1 0010First entry added 2 0015Third entry added 3 0020Second entry added 4 0030Fourth entry added 5 0040Fifth entry added

The opposite of First is Last. In this example I want to delete the last three entries in the User Index:

-- Delete last 3 keys 01 SELECT ORDINAL_POSITION,REMOVED_ENTRY 02 FROM TABLE(QSYS2.REMOVE_USER_INDEX_ENTRY( 03 USER_INDEX => 'MYUSRIDX', 04 USER_INDEX_LIBRARY => 'MYLIB', 05 OPERATION => 'LAST', 06 MAX_REMOVE => 3))

Line 5: The parameter is 'LAST'.

Line 6: As I want to delete the three entries the Max Remove parameter is 3.

The results of this statement are:

ORDINAL_ POSITION REMOVED_ENTRY -------- ----------------------- 1 0100Tenth entry added 2 0070Ninth entry added 3 0060Eighth entry added

Notice how the results are in descending key order. That is because the entry with the greatest key value was removed first, and then the second greatest, etc.

I tried several ways of removing all the entries from the User Index. The statement I ended up with is:

-- Delete all entries 01 SELECT * FROM TABLE(QSYS2.REMOVE_USER_INDEX_ENTRY( 02 'MYUSRIDX','MYLIB','GE','0000'))

Line 2: In this statement I did not give the various parameter names, just their values.

By selecting everything greater or equal to the key value of zero will remove all the entries.

 

You can learn more about the REMOVE_USER_INDEX_ENTRY SQL Table Function from the IBM website here.

 

This article was written for IBM i 7.5 and 7.4 TR6.