Informing the IBM Community

DB2 for i – Services


This month we are going to have a break from node.js – I can hear the cheers from here!

This time we are going to look at my favourite DB2 for i services.

So, good place to start, what are DB2 services?

They are SQL views, procedures and functions that give us access to system information.  For example, are there any jobs that are running that are on a message wait status.

These services have been around, and added to for each new Operating System release, since 2016.

This type of information was previously gathered by:

  1. Running a command to a spool file, then copying the spool to a database table, then looping through all the records to find what you were after – Yes, we still do see sites doing it this way!
  2. Calling an IBM API – depending on the API, they could be complex and the majority of time, by coding a high-level language (RPG/Cobol)

Now with DB2 services we can just run a SQL string and the results will be returned to us – it’s that simple.

DB2 services are just SQL statements, which can be run on any IBM i SQL interface.  My favourite interface has to be the one shipped with Access for Client Solutions (ACS).

This option is shown below.

Top 10 Favourites

Here are my top ten favourite services.

Number 1 – PTF Groups

This has to be number one on my list – If I have to check a client’s box for issues, the first thing is to check the PTF groups they have installed and this SQL statement gives me everything I need to know.


With iLevel(iVersion, iRelease) AS
select OS_VERSION, OS_RELEASE from sysibmadm.env_sys_info
FROM iLevel, systools.group_ptf_currency P
WHERE ptf_group_release =
‘R’ CONCAT iVersion CONCAT iRelease concat ‘0’
ORDER BY ptf_group_level_available –
ptf_group_level_installed DESC;


Number 2 – Select Average_CPU_Utilisation  

A nice, quick and easy SQL statement to show what our current CPU usage is.


Select Average_Cpu_Utilization
From Qsys2.System_Status_Info;


Number 3 – Show Shell

This SQL statement shows all the users which are not using the default shell.


select authorization_name, pase_shell_path
from qsys2.user_info where pase_shell_path is not null;


Number 4 – History Log

This example displays the history log for the previous 24 hours.  Very useful, knowing how difficult it is to search the history log.


Select *
From Table (
Qsys2.History_Log_Info(Current Timestamp – 1 Day)
) X


Number 5 – Server Firmware

Another one along the lines of fix levels, this one is for our server firmware.  This service shows if the firmware has any available updates.


Select *
From Systools.Firmware_Currency;


Number 6 – NETSTAT Info

As my current workload seems to be continually into open source these days, checking if a TCP/IP port is in use is very handy. 

This SQL example shows all ports that are in use that are between 3000 and 5000.


Select *
From Qsys2.Netstat_Info
Where Local_Port Between 3000 and 5000;


Number 7 – Listing objects in a library, passing the library name and object type. 

This would have been useful many years ago, when checking objects in a library for DR testing.


Select *
From Table (
Qsys2.Object_Statistics(‘HRDATA’, ‘*ALL’)
Order By Objtype,
Objname ;


Number 8 – Show system information

Great one for reporting a PMR to IBM, gives all the system information you ever need.


Select Machine_Type,
From Qsys2.System_Status_Info;


Number 9 – Check an objects authority

Very handy to check a user has authority to an object, before you go ahead and assume.

Pass the library name and the object name to this function.

This function returns 1 for authority granted and 0 if the user is not authorised to the object.


Select Qsys2.Sql_Check_Authority(‘HRDATA’, ‘EMPLOYEE’)
From Sysibm.Sysdummy1;


Number 10 – Lock information

This service provides lock information for an object. 

This example shows lock information for our Employee table.


Select Job_Name,
From Qsys2.Object_Lock_Info
Where System_Object_Name = ‘EMPLOYEE’


Well, that’s my favourite ten DB2 services.  Keep an eye on the IBM site for these, they are continuously adding more as we speak.

If you need to check which services run under which OS level, check the site


As with all my previous articles which include coding, these changes have now been published as a GitHub gist,  which can be found at this link. 


IBM really has made it easy to gather system information, that was previously only available using APIs.

Let me know your favourites.

If you have any questions, either on this article, or any other open source, use the comments below, or send me a message on twitter @AndyYouens

Andy Youens is an IBM i consultant/instructor at Milton Keynes, UK-based FormaServe Systems with over 40 years IBM midrange experience.

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.


2 responses to “DB2 for i – Services”

  1. Mike Ryan avatar
    Mike Ryan

    Bloody great article! These SQL services are a great step forward by IBM. Cheers Andy

    1. Cheers Mike – Stay safe 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *