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:
- 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!
- 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 –
Number 2 – Select Average_CPU_Utilisation
A nice, quick and easy SQL statement to show what our current CPU usage is.
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.
From Table (
Qsys2.History_Log_Info(Current Timestamp – 1 Day)
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.
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.
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.
From Table (
Order By Objtype,
Number 8 – Show system information
Great one for reporting a PMR to IBM, gives all the system information you ever need.
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’)
Number 10 – Lock information
This service provides lock information for an object.
This example shows lock information for our Employee table.
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 https://www.ibm.com/support/pages/node/1119123
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.
Mike Ryan says
Bloody great article! These SQL services are a great step forward by IBM. Cheers Andy
Andy Youens says
Cheers Mike – Stay safe 🙂