Getting data about jobs using SQL

This UDTF, User Defined Table Function, slipped under my radar when it was introduced last year as part of Technology Refreshes for IBM i 7.3, TR1, and 7.2, TR5. It is as if the commands WRKUSRJOB (work with user jobs), WRKSBMJOB (work with submitted jobs), and WRKSBSJOB (work with subsystem jobs) were all rolled into one to create the UDTF JOB_INFO.

Why would I choose to use JOB_INFO rather than those commands? I can tailor my SQL statement for the criteria I desire, and then fetch the data within a program to do with what I want. Alas, with those three work commands I can either display or print the output. I find it easier to use this UDTF than the list API QUSLJOB.

Like many other UDTF JOB_INFO five "parameters" you can use to narrow the scope of the result set returned.

"Parameter"	Description and allowed values	Default value
JOB_STATUS_FILTER	Job status *ALL, *ACTIVE, *JOBQ, *OUTQ	*ALL
JOB_TYPE_FILTER	Job type *ALL, *BATCH, *INTERACTIVE	*ALL
JOB_SUBSYSTEM_FILTER	Subsystem name *ALL, subsystem name	*ALL
JOB_USER	User *ALL, *USER, user name	*USER
JOB_SUBMITTER_FILTER	Type of submitted job *ALL, *JOB (Jobs submitted from this job), *USER (Jobs submitted by this user), *WRKSTN (jobs submitted from this workstation)	*ALL

Only one of these "parameters" can have a value other than *ALL at a time:  JOB_SUBSYSTEM_FILTER or JOB_SUBMITTER_FILTER.

If the JOB_SUBMITTER_FILTER is not *ALL then JOB_USER_FILTER must be *ALL.

There are 69 columns in this UDTF, therefore, I am not going to list them all here as IBM does a good job describing what they are in their KnowledgeCenter, there is a link to the appropriate page at the bottom of this post. I am only going to refer to a few in my examples. Alas, there are only long names for the columns.

Let me start with a very simple comparison between the WRKUSRJOB command and JOB_INFO.

WRKUSRJOB Work with User Jobs Opt Job User Type ----Status---- Function _ QPADEV0001 RPGPGM INTER ACTIVE CMD-STRSQL _ QPADEV0002 RPGPGM INTER OUTQ _ QPADEV0002 RPGPGM INTER OUTQ _ QPADEV0003 RPGPGM INTER ACTIVE CMD-WRKUSRJOB

01 SELECT * FROM TABLE(QSYS2.JOB_INFO()) A 02 WHERE JOB_TYPE NOT IN ('SBS','SYS','RDR','WTR') JOB_NAME JOB_I00001 JOB_STATUS 021493/RPGPGM/QPADEV0002 YES OUTQ 042768/RPGPGM/QPADEV0002 YES OUTQ 046212/RPGPGM/QPADEV0001 YES ACTIVE 046362/RPGPGM/QPADEV0003 YES ACTIVE JOB_TYPE JOB_T00001 INT INTERACTIVE INT INTERACTIVE INT INTERACTIVE INT INTERACTIVE

Line 1: My Select statement will list all of the columns, but I do not have room here to display them all here. The UDTF is defined in the TABLE part of the statement, I have not defined any of the "parameters", therefore, it will display all of the data for the current user, RPGPGM (me!). As with other UDTF I have to give a letter after the TABLE, this can be any letter, I chose A.

The Where is IBM's recommendation to ignore these job types when fetching the same data as WRKSBMJOB:  subsystem monitors, system, spool readers, and spool writers.

I have only given the first few columns from the result set:

1. JOB_NAME:  Job name
2. JOB_I00001 (JOB_INFORMATION):  Yes means that the jobs information is available
3. JOB_STATUS:  Status of the job
4. JOB_TYPE:  Job type
5. JOB_T00001 (JOB_TYPE_ENHANCED):  Job type description

In this next example I want to see all of the batch jobs Lara submitted on September 14.

01 SELECT JOB_NAME,JOB_STATUS,COMPLETION_STATUS,JOB_END_REASON, 02 JOB_ACTIVE_TIME,JOB_END_TIME 03 FROM TABLE(QSYS2.JOB_INFO(JOB_USER_FILTER => 'LARAD', 04 JOB_TYPE_FILTER => '*BATCH')) A 05 WHERE JOB_TYPE NOT IN ('SBS','SYS','RDR','WTR') 06 AND JOB_ACTIVE_TIME BETWEEN '2017-09-14-00.00.00' 07 AND '2017-09-14-23.59.59'

Lines 1 and 2: These are the columns I want returned in my result set.

1. JOB_NAME:  Full job name
2. JOB_STAUTS:  Job status
3. COMPLETION_STATUS:  How the job completed, either normal or abnormal
4. JOB_END_REASON:  Most recent action that caused job to end
5. JOB_ACTIVE_TIME:  Time job started
6. JOB_END_TIME:  Time job ended

Lines 3 and 4: This time I am using two of the "parameters", user id and job type. Lara's user profile is LARAD, and only want jobs that ran in batch. Notice that I have to use => instead of an equal sign.

Line 5: IBM's recommended excluded job types.

Line 6 and 7: JOB_ACTIVE_TIME is the time the job started.

The returned results show me that she ran four batch jobs, two of which ended abnormally.

JOB_NAME JOB_STATUS COMPL00001 009420/LARAD/XXXXXXXXXX OUTQ NORMAL 009501/LARAD/XXXXXXXXXX OUTQ ABNORMAL 009502/LARAD/XXXXXXXXXX OUTQ ABNORMAL 009575/LARAD/XXXXXXXXXX OUTQ NORMAL JOB_E00004 JOB ENDING IN NORMAL MANNER JOB ENDED DUE TO THE MESSAGE SEVERITY LEVEL BEING EXCEEDED JOB ENDED DUE TO THE MESSAGE SEVERITY LEVEL BEING EXCEEDED JOB ENDING IN NORMAL MANNER JOB_A00002 JOB_E00002 2017-09-14-13.43.02 2017-09-14-13.44.29 2017-09-15-15.01.54 2017-09-15-15.01.59 2017-09-15-15.03.51 2017-09-15-15.03.56 2017-09-14-42.40.50 2017-09-15-43.42.49

I can emulate the command WRKSBSJOB too.

WRKSBSJOB SBS(QBATCH) Work with Subsystem Jobs Opt Job User Type -----Status--- __ BBBBBBBBBB SALLYP BATCH ACTIVE __ CCCCCCCCCC SALLYP BATCH ACTIVE __ DDDDDDDDDD SALLYP BATCH ACTIVE __ EEEEEEEEEE SALLYP BATCH MSGW

My equivalent using JOB_INFO:

01 SELECT JOB_NAME,JOB_ACTIVE_TIME,SUBMITTER_JOB_NAME, 02 JOB_LOG_OUTPUT,JOB_LOG_PENDING,OUTPUT_QUEUE_NAME 03 FROM TABLE(QSYS2.JOB_INFO(JOB_SUBSYSTEM_FILTER => 'QBATCH', 04 JOB_USER_FILTER => '*ALL')) A 05 WHERE JOB_TYPE NOT IN ('SBS','SYS','RDR','WTR')

Lines 1 and 2: I am selecting the following columns:

1. JOB_NAME:  Full job name
2. JOB_ACTIVE_TIME:  Time the job started
3. SUBMITTER_JOB_NAME:  Job that submitted this job
4. JOB_LOG_OUTPUT:  When the job log will be generated
5. JOB_LOG_PENDING:  Has the job log been written?
6. OUTPUT_QUEUE_NAME:  Default job queue

Lines 3 and 4: I want only those jobs in the subsystem QBATCH and for everyone.

Line 5: IBM's recommended excluded job types.

My results are:

JOB_NAME JOB_A00002 046393/SALLYP/BBBBBBBBBB 2017-11-22-04.06.30 046394/SALLYP/CCCCCCCCCC 2017-11-22-04.06.36 046395/SALLYP/DDDDDDDDDD 2017-11-22-04.06.41 046396/SALLYP/EEEEEEEEEE 2017-11-22-04.06.46 SUBMI00001 JOB_L00001 JOB_L00002 OUTPU00003 046019/SALLYP/AAAAAAAAAA *JOBEND NO QPRINT 046019/SALLYP/AAAAAAAAAA *JOBEND NO QPRINT 046019/SALLYP/AAAAAAAAAA *JOBEND NO QPRINT 046019/SALLYP/AAAAAAAAAA *JOBEND NO QPRINT

Alas, there is not a column in JOB_INFO that I can use to determine if a job is in message wait. I would need to use ACTIVE_JOB_INFO table function for that.

There are more examples of using this UDTF can be found on IBM's KnowledgeCenter page.

 

You can learn more about the JOB_INFO command from the IBM website here.

 

This article was written for IBM i 7.3 TR1 and 7.2 TR5.

---

Addendum

If you want to get a list of jobs in a job queue you should read this.