User index information by SQL

As part of the latest Technology Refreshes, IBM i 7.4 TR5 and 7.3 TR11, we received a SQL View and Table function to use to get information about User Indexes.

I do not recall ever using a User Index, but some of the ERP I have worked with through the years had them. The IBM documentation describes them as:

A user index is an object that allows search functions for data in the index and automatically sorts data based on the value of the data...
They have an object type of *USRIDX and a maximum size of 1 terabyte. They help streamline table searching, cross-referencing, and ordering of data.

Until these TRs I would need to use APIs, ILE C, or MI instructions to access a User Index and the information contained within. Now IBM has provided us with the following:

• USER_INDEX_INFO View
• USER_INDEX_ENTRIES Table function

 

USER_INDEX_INFO View

In its simplest form this View gives me a list of every User Index in the IBM i partition:

SELECT * FROM QSYS2.USER_INDEX_INFO

On the partition I use for writing these posts there are only eight User Indexes. I am going to show the columns returned from this view in two SQL statements. My first statement looks like:

01 SELECT USER_INDEX_LIBRARY AS "Library", 02 USER_INDEX AS "U indx", 03 ENTRY_TYPE AS "Type", 04 ENTRY_LENGTH AS "Len", 05 MAXIMUM_ENTRY_LENGTH AS "Max", 06 INDEX_SIZE AS "Idx size", 07 IMMEDIATE_UPDATE AS "Immed upd", 08 OPTIMIZATION AS "Optmiz" 09 FROM QSYS2.USER_INDEX_INFO 10 ORDER BY 1,2 ;

Lines 1 – 8: The names of the columns do a good job to describe their contents. I have given them all short column headings so that the results will fit the width of this post. Some differences you may see with User Indexes in your IBM i partitions are:

• ENTRY_TYPE:  FIXED = fixed length entries, VARIABLE = variable length entries
• ENTRY_LENGTH:  If fixed length this is the length of each entry. For variable length the length of the longest entry is shown
• MAXIMUM_ENTRY_LENGTH:  The maximum length any entry can have
• INDEX_SIZE:  Maximum size of the index, either 4 GB or 1 TB
• OPTIMIZATION:  Optimization method used for User Index maintenance, RANDOM or SEQUENTIAL

Line 10: I have used the short cut ORDER BY 1,2 to sort the results by the first and second columns. I find this easier to type than the long names of these columns.

The results are:

Library U indx Type Len Max Idx size Immed upd Optmiz ------- ---------- ----- ---- ---- -------- --------- ---------- QHTTP QTMHPID FIXED 52 52 4 GB YES RANDOM QHTTP QZSRPERF FIXED 21 21 4 GB YES SEQUENTIAL QSVMSS QCQAPSAI FIXED 218 218 4 GB YES RANDOM QUSRSYS QCQAPSAI FIXED 218 218 4 GB YES RANDOM QUSRSYS QCSSMREG FIXED 2000 2000 4 GB YES RANDOM QUSRSYS QEZPWRCLN FIXED 57 57 4 GB YES RANDOM QUSRSYS QYCDCERTI FIXED 2000 2000 4 GB YES RANDOM QUSRSYS QYSMSVRE FIXED 587 587 4 GB YES RANDOM

The second statement takes care of all of the rest of the columns in the view, except the one for object description, TEXT_DESCRIPTION:

01 SELECT USER_INDEX_LIBRARY AS "Library", 02 USER_INDEX AS "U indx", 03 KEY_INSERTION AS "Key ins", 04 KEY_LENGTH AS "K len", 05 ENTRY_TOTAL AS "Entries", 06 ENTRIES_ADDED AS "Add", 07 ENTRIES_REMOVED AS "Dlt", 08 OBJECT_DOMAIN AS "Obj dom" 09 FROM QSYS2.USER_INDEX_INFO 10 ORDER BY 1, 2

Lines 1 – 8: As I did in my previous statement I have changed the column headings to ensure that these results fit on this page. The fields in these results are:

• KEY_INSERTION:  YES = Insert by key, NO = Not inserted by key
• KEY_LENGTH:  Length of key, or null if KEY_INSERTION is no
• ENTRY_TOTAL:  Number of entries in the User Index
• ENTRIES_ADDED:  Number of entries added to the User Index
• ENTRIES_REMOVED:  Number of entries removed from the User Index
• OBJECT_DOMAIN:  *SYSTEM = User Index is in the system domain, *USER = User Index is in the user domain

My results look like:

Library U indx Key ins K len Entries Add Dlt Obj dom ------- ---------- ------- ----- ------- --- --- ------- QHTTP QTMHPID YES 10 0 1 1 *SYSTEM QHTTP QZSRPERF YES 10 1 1 0 *SYSTEM QSVMSS QCQAPSAI YES 8 1 1 0 *USER QUSRSYS QCQAPSAI YES 8 1 1 0 *USER QUSRSYS QCSSMREG YES 554 0 0 0 *SYSTEM QUSRSYS QEZPWRCLN YES 7 0 2 2 *SYSTEM QUSRSYS QYCDCERTI YES 104 43 43 0 *SYSTEM QUSRSYS QYSMSVRE YES 34 32 32 0 *SYSTEM

 

USER_INDEX_ENTRIES Table function

Having discovered all of the User Indexes on this partition, now I can see what is in one of them. I am going to choose QYSMSVRE.

Prior to this Table function I would have had to use the QUSRTVUI API to peek into the User Index. Now I can use the USER_INDEX_ENTRIES Table function, which is a lot easier to use.

The Table function has two parameters:

1. User Index name
2. User Index library name

I can use the Table function with the parameter names:

01 SELECT * 02 FROM TABLE(QSYS2.USER_INDEX_ENTRIES( 03 USER_INDEX => 'QYSMSVRE', 04 USER_INDEX_LIBRARY => 'QUSRSYS'))

As I have used the parameter names they can be reversed too:

01 SELECT * 02 FROM TABLE(QSYS2.USER_INDEX_ENTRIES( 03 USER_INDEX_LIBRARY => 'QUSRSYS', 04 USER_INDEX => 'QYSMSVRE'))

If I do not use the parameter names the User Index name must come first:

01 SELECT * 02 FROM TABLE(QSYS2.USER_INDEX_ENTRIES('QYSMSVRE','QUSRSYS'))

My example statement is not going to return the binary version of the key and entry columns.

01 SELECT USER_INDEX_LIBRARY AS "Library", 02 USER_INDEX AS "U indx", 03 KEY,ENTRY 04 FROM TABLE(QSYS2.USER_INDEX_ENTRIES('QYSMSVRE','QUSRSYS')) 05 ORDER BY ORDINAL_POSITION 06 LIMIT 6

Line 3: These columns contain the following:

• KEY:  Key used by the User Index. Would be null if the User Index is not keyed
• ENTRY:  User Index entry data

Line 5: I am sorting the results by the ordinal position, which is generated as part of the results, but I do not care to display it in my results.

Line 6: For this example I only want to show the first six results, which I do using the LIMIT.

The results are as follows:

Library U indx KEY ENTRY ------- --------- ---------------------- -------- QUSRSYS QYSMSVRE QIBM_DEFAULT_IPADDRESS 0 QUSRSYS QYSMSVRE QIBM_DEFAULT_IPADDRESS 1QUSRWRK QUSRSYS QYSMSVRE QIBM_NETDRIVE 0 QUSRSYS QYSMSVRE QIBM_NETDRIVE 1QSERVER QUSRSYS QYSMSVRE QIBM_NETSERVER 0 QUSRSYS QYSMSVRE QIBM_NETSERVER 1QSERVER

 

Another great View and Table function making it easier to get to information that previously was only available by API.

 

You can learn more about this from the IBM website:

• User Index APIs
• User Index considerations
• USER_INDEX_INFO View
• USER_INDEX_ENTRIES Table function

 

This article was written for IBM i 7.4 TR5 and 7.3 TR11.