IBM i Services are perhaps the best kept secret of managing and monitoring IBM i. Relatively few users of our favourite platform know what they are and even fewer use them regularly. This is a crying shame as they are truly remarkable. IBM i Services can tell you in seconds what it takes a skilled system operator minutes or even hours to discover.
I was lucky enough to meet up with IBM Rochester’s Scott Forstie at our recent i-UG event at Wyboston Lakes in the UK. Scott is truly one of the world’s leading authorities on IBM i Services. His enthusiasm for the platform in general, and IBM i Services in particular, was infectious. He gave an excellent talk on IBM i Services at the conference and it was the audience’s reaction to it that has inspired this article.
So what are they? Put simply, IBM i Services are the latest generation of management and monitoring APIs (application program interfaces) that IBM has built into the operating system. More specifically, they are a series of tables (files for you old-school readers) that are maintained in real time by IBM i itself.
These tables can be queried to find all sorts of system health, usage and security information. Using a simple query, you can find out things like:
• Current temporary storage by user.
• Which users deleted a record from a specific file during a specific timeframe.
• Which users are having trouble signing in.
• Which users have *ALLOBJ both directly or by virtue of a group profile.
• Which users are logging into your system using DRDA/DDM.
• How much storage is being using on your system by a specific user or users.
• Installed PTF group levels and whether IBM has any newer levels for you to install.
• Which tables are approaching their maximum size.
• Which tables have the most deleted records.
Plus, literally hundreds of other statistics.
How do you use them?
To use IBM i Services, you simply run an SQL statement. You can do this from whichever SQL client you prefer, be it green on black, System i Navigator for Windows, or your own favoured SQL client.
Issuing a statement like the one shown below will list all users that have failed at least one sign-on attempt.
SELECT * FROM QSYS2.USER_INFO WHERE SIGN_ON_ATTEMPTS_NOT_VALID > 0
SELECT AUTHORIZATION_NAME, STATUS, NO_PASSWORD_INDICATOR, PREVIOUS_SIGNON, TEXT_DESCRIPTION FROM QSYS2.USER_INFO WHERE SPECIAL_AUTHORITIES LIKE '%*ALLOBJ%' OR AUTHORIZATION_NAME IN (SELECT USER_PROFILE_NAME FROM QSYS2.GROUP_PROFILE_ENTRIES WHERE GROUP_PROFILE_NAME IN (SELECT AUTHORIZATION_NAME FROM QSYS2.USER_INFO WHERE SPECIAL_AUTHORITIES like '%*ALLOBJ%')) ORDER BY AUTHORIZATION_NAME;
Pro tip: I switched from STRSQL on green screen to the RunSQL built into IBM i Navigator for Windows for the results shown below. You can use either but when there is more data to display or you want to export the results, this GUI view wins hands down.
Why are they better?
Now, if you are new to SQL you might think: “That’s not better; it is a long and complex SQL statement “, and that you can get this information from the command line much quicker with a simple IBM i command like:
PRTUSRPRF TYPE(*AUTINFO) SPCAUT(*ALLOBJ).
Well, think again. There is a good reason why this SQL statement is longer; it is because it gives you a more complete answer. You will notice that user SURPRISE is in the list above from IBM i Services but not in the list below from PRTUSRPRF.
This user inherits its *ALLOBJ super powers from its Group Profile ROWTON. It does not have *ALLOBJ specified in its own parameter and so does not show up on the PRTUSRPRF list below. However, that does not stop this user from having full access to every object on the system.
Saving time and effort
As a sysadmin who spends a lot of his time monitoring and updating servers, my favourite IBM i Service is called PTF Currency. This allows me to run a single query against a system to get a list of all the PTF Groups installed along with their level and health (just like a WRKPTFGRP). But, at the same time, it talks to IBM HQ and finds out what the latest available version is for these groups and tells you that too.
SELECT * FROM SYSTOOLS.GROUP_PTF_CURRENCY ORDER BY ptf_group_level_available - ptf_group_level_installed DESC
• run a WRKPTFGRP and capture the output,
• login to IBM Fix Central,
• select IBM Operating systems and IBM i and that you want PTFs for a specific release,
• specify the client’s sever model and serial number.
IBM then generates a list of every possible group you could choose. You would then match this list again the one captured from WRKPTFGRP.
Don’t worry if that process did not make a whole lot of sense. I just wanted to show that the old method could take 30 minutes or more, whereas, using IBM i Services, I got the answer shown above in six seconds.
My company looks after dozens of LPARs for our clients. We probably do this process a dozen times a month for them and so this literally saves us hours of mind-numbing work every single month.
So what’s the catch? Well, it’s not so much a catch as a statement of fact: if you want to know what is going on with your system, you are going to need to learn SQL.
This is not a bad thing. We are always talking about how IBM i supports modern and open standards and what could be more modern or open than SQL? You can use it on just about every system out there.
Scratching the surface
There are literally dozens of these IBM i Services starting in v7.1, with more being added by PTF with every TR level. Below is a table provided by Scott Forstie at the i-UG meeting which summarises these services and when they were added.
Nice to see you
It was great to see so many old friends at Wyboston Lakes but it was also a surprise to see so many new faces too. Welcome aboard, you guys. On behalf of PowerWire, the i-UG and myself it was great to see such an energised crowd.
Our next meeting will take place at The Mount Hotel in my home town of Wolverhampton on November 26, 2015. We’ve already confirmed a number of excellent guest speakers. Hope to see you there. More details and registration available at the i-UG website.
Steve Bradshaw is the founder and managing director of Wolverhampton, UK-based Power Systems specialist Rowton IT Solutions and technical director of British IBM i user group i-UG. He has been a key contributor to PowerWire since 2012 and he also sits on the Common Europe Advisory Council (CEAC) which helps IBM shape the future of IBM i.