Wednesday, September 2, 2020

Prestart jobs' information using SQL

sql to view prestart jobs

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:

 

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

No comments:

Post a Comment

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.