Work Active Jobs using SQL

The IBM Service (formerly "SQL Services") named ACTIVE_JOB_INFO returns information about active jobs. While I don't use it directly too often, I do use it via a surrogate interface I created named ACTJOB_LIST.

My ACTJOB_LIST table function wraps ACTIVE_JOB_INFO that links with JOBLOG_INFO and my own SPOOLED_LIST table function to create a more complete dataset. It leverages these functions to return the number of SPOOLED files a job has produced, and the Last Command or Function the job has run. Also if you're on V7R2 it parses out the 3-part qualified job name into its component parts (job name, number, and user). All this produces a listing that is more interesting to IBM i Admins.

While all of this is well and good, I took it one step further, I created a bunch of SQL Views over my primary Subsystem Descriptions so I can get a "Work active jobs"-like results for a given subsystem. For example if I query the QINTER view, I see the following:

Since it is a View, regardless of the IBM i release level, I can browse any of my managed partitions. All I need to do is prefix the view schema (library) with the partition identification or "3-part name", like this:

select * from chicago.sqltools.Qinter;        

Since, obviously, SQL Tools is installed on all the partitions I manage, I have no problem monitoring activity on these remote partitions; some of which are literally on the other side of the world.

I've included Views for QINTER, QBACH, QUSRNOMAX, and QHTTPSVR but users can simply extract the source code for any of these views and create one for their own custom subsystem name(s).

Try it out yourself. Download and install SQL Tools for IBM i today. Just visit: https://www.SQLiQuery.com/SQLTools

If you have any questions, just drop me a DM or send an email to the email link on the website.

That's all there is to it.