Wednesday, November 29, 2017

Getting data about jobs using SQL

JOB_INFO table function in place of WRKSBMJOB, WRKUSRJOB, and WRKSBSJOB

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.

16 comments:

  1. I love this command, I am trying to use this in a program as a sort of load balancing using a control file to manage how many subsystem batch jobs are running and if they are in error (MSGW).

    ReplyDelete
  2. IBM missed one important feature about jobs, the current user. I need to use this to find server jobs running under QUSER for user XXXXX. So you end up needing to tailor a join of two job UDFTs from IBM.

    select * from table(QSYS2.JOB_INFO(JOB_USER_FILTER => '*USER')) A
    inner join table(QSYS2.ACTIVE_JOB_INFO(CURRENT_USER_LIST_FILTER => current_user)) AA on A.JOB_NAME = AA.JOB_NAME where 1=1 and A.job_name=qsys2.job_name;

    -Matt

    ReplyDelete
  3. Hi, can these be used on a V7R1 OS? If I copy/paste the examples above they don't work...is that the exact syntax or a some thing missing from the examples.
    Thanks, Frank

    ReplyDelete
    Replies
    1. From the first paragraph I would say no you cant use this UTDF: "Technology Refreshes for IBM i 7.3, TR1, and 7.2, TR5"

      Delete
  4. Is there a way here to retrieve the parameters passed to a job sitting in a jobq in status hold? Thanks,

    ReplyDelete
    Replies
    1. I cannot think of any way to get that information without doing the obvious:
      - DSPJOB
      - View the job log
      - Perhaps you can see it there

      Delete
  5. Best thing ever!!!! an SQL interface to iseries cmds... !!! it must exist since version 5!!!!

    ReplyDelete
  6. Can this be accomplished by few CL commands ?

    ReplyDelete
    Replies
    1. I am sure it can. But why bother when you can more than you need using this approach?

      Delete
  7. Corrections:
    *INTERACT, not *INTERACTIVE.
    JOB_USER_FILTER, not JOB_USER.

    ReplyDelete
  8. Hello Simon, excellent, very good, thank you. I have a query: Programs A, B and C call program X (independently and at any time) My question, how can program X know who has called it?

    ReplyDelete
    Replies
    1. You need to programmatically look in the call stack. There are various (and messy) ways to do that.

      IMHO the easiest way is to use SQL's STACK_INFO table function.

      Delete
  9. Hello Simon,

    Is there a way to retrieve all the call stack for the lifetime of the job not just the current active stack ?

    I have been following your blogs for few years now.. you are doing a great job sharing your knowledge. Thank you

    ReplyDelete
  10. Is there a way to get job info for a specific job (ie. JOBACCTG) for all users for a day period?

    ReplyDelete
    Replies
    1. Depending on the retention period on your partition I would look in HISTORY_LOG_INFO.

      Delete

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.