Wednesday, December 21, 2022

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.

No comments:

Post a Comment

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.