Extract Active Job Data with IBM i SQL Services and CoolSpools

Are you making the most of SQL services on your IBM i server?

The CoolSpools database conversion commands allow you to specify FROMFILE(*SQL) as the source of your data, executing an SQL statement and converting the generated record set to a variety of output formats including Excel, CSV, HTML and XML. Most commonly, the FROMFILE(*SQL) option is used to select and convert data from one or more database tables, but it is also possible to use a wide range of IBM i SQL services to convert system information.

The SQL language on the IBM i platform includes a number of services that allow you to extract information about the system’s configuration, objects and jobs. The range of SQL services available has been growing with each recent release of the operating system. If your IBM i server is on operating system version 7.1 or later, then you can run the CoolSpools command below to generate an Excel spreadsheet that lists the SQL services available on your server.

CVTDBFXLSX FROMFILE(*SQL) TOSTMF(Available_SQL_Services.xlsx) STMFOPT(*REPLACE) SQL(‘SELECT RTRIM(SERVICE_SCHEMA_NAME)||”.”||TRIM(SERVICE_NAME) as SERVICE, SQL_OBJECT_TYPE, EXAMPLE FROM QSYS2.SERVICES_INFO’ *SQL)

More information regarding the SQL services available on IBM i can be found in IBM documentation, or in Scott Forstie’s Gists on GitHub. Any of the listed SQL services that outputs a record set can be used as the source of a CoolSpools database conversion.

Listing Active Jobs in MSGW status

Lets look at a practical example of how IBM i SQL services can be used with CoolSpools.

If you are on a recent IBM i release (IBM i 7.2 and above) then you can call the SQL function active_job_info() to retrieve information about active jobs on the system. The example below will generate an HTML table containing details of any jobs currently in MSGW (message wait) status, and embed that table within an email message to support@your_org.com

CVTDBFHTML FROMFILE(*SQL) TOSTMF(Jobs_in_MSGW.htm) STMFOPT(*REPLACE) SQL(‘SELECT SUBSYSTEM, JOB_NAME, JOB_TYPE, FUNCTION, JOB_STATUS FROM TABLE (QSYS2.ACTIVE_JOB_INFO()) as JOB WHERE JOB_STATUS = ”MSGW”’ *SQL) EMAIL(*YES) EMAILOPT(*NO ‘Jobs in MSGW Status’ *EMBED) EMAILTO(support@your_org.com)

The email message below was delivered by running this simple command.

If you find that the SQL statement needed to extract the required data becomes too lengthy or complex to be easily maintainable within the SQL() parameter of the command, then CoolSpools allows you to specify FROMFILE(*SQLSRC) instead and to place the SQL statement within a source member.

Our previous example becomes:

CVTDBFTXT FROMFILE(*SQLSRC) TOSTMF(Jobs_in_MSGW.htm) STMFOPT(*REPLACE) SQLSRC(LIBRARY/QSQLSRC JOBMSGW *SQL) EMAIL(*YES) EMAILOPT(*NO ‘Jobs in MSGW Status’ *EMBED) EMAILTO(support@your_org.com)

With source member JOBMSGW in file LIBRARY/QSQLSRC containing the following:

0001.00 /* Select active jobs with status MSGW using active_job_info() */
0002.00 SELECT
0003.00    SUBSYSTEM,
0004.00    JOB_NAME,
0005.00    JOB_TYPE,
0006.00    FUNCTION,
0007.00    JOB_STATUS
0008.00 FROM
0009.00    TABLE (QSYS2.ACTIVE_JOB_INFO()) as JOB
0010.00 WHERE
0011.00    JOB_STATUS = ‘MSGW’