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.

SQL

With iLevel(iVersion, iRelease) AS
(
select OS_VERSION, OS_RELEASE from sysibmadm.env_sys_info
)
SELECT P.*
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;

Results

Number 2 – Select Average_CPU_Utilisation  

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

SQL

Select Average_Cpu_Utilization
From Qsys2.System_Status_Info;

Results

Number 3 – Show Shell

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

SQL

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

Results

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.

SQL

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

Results

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.

SQL

Select *
From Systools.Firmware_Currency;

Results

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.

SQL

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

Results

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.

SQL

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

Results

Number 8 – Show system information

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

SQL

Select Machine_Type,
Machine_Model,
Serial_Number,
System_Asp_Used
From Qsys2.System_Status_Info;

Results

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.

SQL

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

Results

Number 10 – Lock information

This service provides lock information for an object. 

This example shows lock information for our Employee table.

SQL

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

Results

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

GitHub

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. 

Conclusion

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.