Wednesday, November 3, 2021

User index information by SQL

user index viw and table function

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

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_TYPEFIXED = 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_INSERTIONYES = 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:

 

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

1 comment:

  1. Reynaldo Dandreb MedillaJuly 6, 2022 at 4:10 AM

    agree with you Simon, it's a lot easier in SQL than using system API, thanks

    ReplyDelete

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.