Wednesday, July 28, 2021

USER_INFO_BASIC faster way to get data for user profiles

user_info_basic smaller faster user_info

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.

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.