USER_INFO_BASIC faster way to get data for user profiles

This is going to be a short post about a new SQL View, USER_PROFILE_BASIC, that was released as part of the latest round of Technology Refreshes. As the name suggests this new View contains a subset of the columns found in the USER_INFO View.

By having less columns USER_INFO_BASIC fetches and calculates its results faster than USER_INFO.

The syntax for this statement is simple:

SELECT * FROM QSYS2.USER_INFO_BASIC ;

The column names are the same in USER_INFO_BASIC as they are in USER_INFO.

If I wanted to retrieve some basic information about my user profile I could use the following statement:

01 SELECT AUTHORIZATION_NAME,STATUS,USER_CLASS_NAME, 02 TEXT_DESCRIPTION 03 FROM QSYS2.USER_INFO_BASIC 04 WHERE AUTHORIZATION_NAME = 'SIMON' ;

My results are the same as if I had used the USER_INFO View.

AUTHORIZATION USER_CLASS _NAME STATUS _NAME TEXT_DESCRIPTION ------------- -------- ---------- ---------------- SIMON *ENABLED *PGMR Simon Hutchinson

Which columns are omitted from USER_INFO_BASIC?

1. USER_OWNER
2. USER_CREATOR
3. SIZE
4. CREATION_TIMESTAMP
5. LAST_USED_TIMESTAMP
6. DAYS_USED_COUNT
7. LAST_RESET_TIMESTAMP

If I want to see what these omitted columns contain for my profile I can run the following statement using the USER_INFO View:

01 SELECT AUTHORIZATION_NAME,USER_OWNER,USER_CREATOR,SIZE, 02 CREATION_TIMESTAMP,LAST_USED_TIMESTAMP, 03 DAYS_USED_COUNT,LAST_RESET_TIMESTAMP 04 FROM QSYS2.USER_INFO 05 WHERE AUTHORIZATION_NAME = 'SIMON' ;

The results are:

AUTHORIZATION USER_ USER_ _NAME OWNER CREATOR SIZE CREATION_TIMESTAMP ------------- ------- ------- ------ -------------------------- SIMON QSECOFR QSECOFR 503808 2019-08-18 08:23:33.000000 DAYS_USED LAST_USED_TIMESTAMP _COUNT LAST_RESET_TIMESTAMP -------------------------- --------- -------------------------- 2021-07-15 00:00:00.000000 339 <NULL>

The only omitted column I use a lot is the LAST_USED_TIMESTAMP. I will keep using the USER_INFO when I need the results for that column.

I am all for making my SQL statements execute faster, so I welcome this new View and look forward to using it.

 

You can learn more about the changes to the USER_INFO_BASIC SQL View from the IBM website here.

 

This article was written for IBM i 7.4 TR4 and 7.3 TR10.