Prestart jobs' information using SQL

What are prestart jobs? They are batch jobs that run to allow programs on remote systems to communicate with the IBM i partition. They use prestart job entries that are found in the subsystem descriptions of the subsystems in which they run.

Previously the only way you could list the information about prestart jobs was to use the Display Subsystem Description command, DSPSBSD, one subsystem at a time, or the Display Active Prestart Job command, DSPACTPJ, one job at a time. The latest Technology Refreshes, IBM i 7.4 TR2 and 7.3 TR8, introduced the following to Db2:

• PRESTART_JOB_INFO View
• PRESTART_JOB_STATISTICS Table function

Both of these are found in the QSYS2 library.

These two make it so much easier to retrieve the information for prestart jobs, without being limited in the same way those two CL commands are. For example, I may want a list of all the active prestart jobs in all the subsystems. Using the DSPSBSD command I would have to visit every subsystem to list the jobs. Or I can get the information in one statement using the PRESTART_JOB_INFO View:

01 SELECT SUBSYSTEM_DESCRIPTION_LIBRARY AS "SBS LIB", 02 SUBSYSTEM_DESCRIPTION AS "SBS DESC", 03 PRESTART_JOB_PROGRAM_LIBRARY As "PRE LIB", 04 PRESTART_JOB_PROGRAM AS "PRE PGM" 05 FROM QSYS2.PRESTART_JOB_INFO 06 WHERE SUBSYSTEM_ACTIVE = 'YES' 07 ORDER BY SUBSYSTEM_DESCRIPTION

Lines 1 – 4: I think the long names for the columns describe the data they contain. The only reason I am renaming these column headings is to make the results fit on this page.

Line 5: As I said before this View is found in the library QSYS2.

Line 6: I only want a list of the prestart jobs that are active.

Line 7: And I want the results grouped by the subsystem they run in.

When I ran this on the IBM i I use for writing these examples the list was very long. So here are the first few returned results:

SBS LIB SBS DESC PRE LIB PRE PGM ------- -------- -------- ---------- QSYS QCMN QSYS QOQSESRV QSYS QCMN QSYS QNMAPINGD QSYS QCMN QSYS QNMAREXECD QSYS QCMN QSYS QACSOTP

If I wanted to see the prestart job's details I would have used the DSPACTPJ command:

DSPACTPJ SBS(QCMN) PGM(QSYS/QACSOTP)

Which displays the following screen:

Display Active Prestart Jobs 08/25/20 03:51:53 UTC Subsystem . . . . : QCMN Reset date . . . . : 06/14/20 Program . . . . . : QACSOTP Reset time . . . . : 13:39:15 Library . . . . : QSYS Elapsed time . . . : 1248:12:39 Prestart jobs: Current number . . . . . . . . . . . . . . . . : 1 Average number . . . . . . . . . . . . . . . . : 1.0 Peak number . . . . . . . . . . . . . . . . . : 1 Prestart jobs in use: Current number . . . . . . . . . . . . . . . . : 0 Average number . . . . . . . . . . . . . . . . : .0 Peak number . . . . . . . . . . . . . . . . . : 0 Program start requests: Current number waiting . . . . . . . . . . . . : 0 Average number waiting . . . . . . . . . . . . : .0 Peak number waiting . . . . . . . . . . . . . : 0 Average wait time . . . . . . . . . . . . . . : 00:00:00.0 Number accepted . . . . . . . . . . . . . . . : 0 Number rejected . . . . . . . . . . . . . . . : 0

While it is good to be able to display these results, sometimes I want to have them in a file so that I can, for example, send them via email.

PRESTART_JOB_STATISTICS Table function allow me to display the data, which I could easily output to a file that I could then email. Being a table function it should not come as a surprise that it will only return the results for one prestart job. This Table function has three required parameters:

1. Subsystem name
2. Prestart job program's library
3. Prestart job program's name

There are two other parameters, but they are optional.

I can get the results from the Table function for the same prestart job program I used with the DSPACTPJ.

01 SELECT * 02 FROM TABLE(QSYS2.PRESTART_JOB_STATISTICS( 03 'QCMN', 04 'QSYS', 05 'QACSOTP'))

Lines 3 – 5: The parameters do not need to be on separate lines. I just did this to make it easier for you to see.

The results for this program are:

SUBSYSTEM_NAME PRESTART_JOB_PROGRAM_LIBRARY PRESTART_JOB_PROGRAM -------------- ---------------------------- -------------------- QCMN QSYS QACSOTP STATUS_TIMESTAMP ELAPSED_TIME -------------------------- ------------ 2020-08-25 03:56:12.599539 4493817 CURRENT_JOBS AVERAGE_JOBS PEAK_JOBS CURRENT_INUSE_JOBS ------------ ------------ --------- ------------------ 1 1.0 1 0 AVERAGE_INUSE_JOBS PEAK_INUSE_JOBS CURRENT_WAIT_REQUESTS ------------------ --------------- --------------------- 0.0 0 0 AVERAGE_WAIT_REQUESTS PEAK_WAIT_REQUESTS AVERAGE_WAIT_TIME --------------------- ------------------ ----------------- 0.0 0 0.0 ACCEPTED_REQUESTS REJECTED_REQUESTS ----------------- ----------------- 0 0

This data matches what was shown on the DSPACTPJ display.

This would get really interesting if I could join the two together. I will admit I have copied this from an IBM example. The example used a Lateral join. The best definition I could find for what a Lateral join is:

A lateral join is essentially a foreach loop in SQL. A lateral join is represented by the keyword LATERAL with an inner subquery in the FROM clause, as shown in the following simple representation:

SELECT <columns>
  FROM <tableReference>
  LATERAL <innerSubquery>

My version of IBM's example looks like:

01 SELECT A.PRESTART_JOB_NAME,B.* 02 FROM QSYS2.PRESTART_JOB_INFO A, 03 LATERAL 04 (SELECT * 05 FROM TABLE(QSYS2. PRESTART_JOB_INFO ( 06 A.SUBSYSTEM_DESCRIPTION, 07 A.PRESTART_JOB_PROGRAM_LIBRARY, 08 A.PRESTART_JOB_PROGRAM))) AS B 09 WHERE A.SUBSYSTEM_ACTIVE = 'YES' 10 AND A.SUBSYSTEM_DESCRIPTION = 'QUSRWRK'

Line 1: The "A" prefix is for the column from the PRESTART_JOB_INFO View. B.* includes all the columns from the PRESTART_JOB_INFO Table function.

Line 3: The LATERAL statement that joins the View and the Table function.

Lines 4 – 8: The subselect query, which is the Table function.

Lines 6 – 8: The columns from the View are used as the parameters passed to the Table function.

Lines 9 and 10: I only want the active jobs in the subsystem QUSRWRK.

The first few columns of the results would look something like:

PRESTART_ SUBSYSTEM PRESTART_JOB_ PRESTART_JOB JOB_NAME _NAME PROGRAM_LIBRARY _PROGRAM ---------- --------- -------------- ------------ QZSOSIGN QUSRWRK QSYS QZSOSIGN QZSCSRVS QUSRWRK QSYS QZSCSRVS QNPSERVS QUSRWRK QSYS QNPSERVS QZRCSRVS QUSRWRK QSYS QZRCSRVS QZHQSSRV QUSRWRK QSYS QZHQSSRV QZDASOINIT QUSRWRK QSYS QZDASOINIT

These are more great additions to SQL on IBM i. A big thank you to IBM's Db2 for i team for these.

 

You can learn more about this from the IBM website:

• PRESTART_JOB_INFO View
• PRESTART_JOB_STATISTICS Table function

 

This article was written for IBM i 7.4 TR2 and 7.3 TR8.