Using SQL to Query IBM^® i System Information

When you’re an administrator on IBM^® i (or any platform) you are constantly looking for ways to do things more efficiently. In many cases, a better way of doing a task is sitting right under your nose. Such was the case for me and the DB2 for i – Services available in library QSYS2. These SQL views, procedures and functions allow users to interact with system information much more quickly and precisely than was possible previously. Data can be queried from the system using SQL without creating output files first, as is the case with many of the display commands for objects, user profiles, etc.

There are many views to choose from for gathering information about journals, user profiles, PTFs, system security information, system storage and storage devices, system values and job information.

Let me highlight a few of the views I have found useful in my daily work:

QSYS2.PTF_INFO – allows the user to gather information about PTFs on the system including information about the related product identifier, product option, product release level, the status of the PTF, IPL action information and date and time information. One of my favorite things about using this view is I can quickly see if a PTF is on the system without knowing the Product ID  for it. This information is rarely included when I am asked whether a PTF is on the system yet or not.
Example: select * from qsys2.ptf_info where PTF_ACTION_REQUIRED = 'IPL'

Gives a list of all PTFs that are waiting for an IPL to be applied on the system.

2. QSYS2.USER_INFO – allows the user to gather information on user profiles on the system with all the expected parameters and a couple of extras like the object auditing value for the user, if the user profiles have a default password (password is the same as the profile), and invalid sign on attempts. This makes creating output for auditors regarding user profiles a snap.

Example: select * from qsys2.user_info where SPECIAL_AUTHORITIES like ‘%*ALL%’

Produces a list of all user profiles with the special authority *ALL.

3. QSYS2.USER_STORAGE – allows the user to gather information about who has been eating up all the storage on the system. (Note to self, on my test partition it’s me!) Querying against this view can provide critical data on systems that are constantly growing faster than planned, and no one seems to know where the increase is coming from.

Example: select * from QSYS2/USER_STORAGE where storage_used > 500000

Produces a list of all users with user storage over a half Gigabyte.

4. QSYS2.LICENSE_INFO – allows the user to gather license information about installed licensed programs. This is very useful for making sure your licenses aren’t expiring in the near term.

Example: select * from QSYS2/LICENSE_INFO where log_violation = 'YES'

Produces a list of all licensed products that have incurred a log violation.

Those were just a few examples, the full list of views, procedures and functions and the requisite PTF levels to use them is available on the IBM Community here.

I hope that you found this information helpful!