Wednesday, October 28, 2015

Using SQL to get information about Job Scheduled entries

wrkjobscde

Am I the only one who has found it difficult to get information about what is in an IBM i's Job Scheduler?

I can use the Work Job Schedule Entries command, WRKJOBSCDE, to create a print out of the details. But I just want a quick and simple way to quickly and easily find what I want.

Someone at IBM must have been listening as the View SCHEDULED_JOB_INFO was added in IBM i 7.2. I can now easily get to, pick and choose the information I want using a SQL SELECT statement. I will give some examples of what I find helpful, and show you what you can do using this View.

SCHEDULED_JOB_INFO is found in the library QSYS2. I am not going to list all of the columns in this View as IBM does a good job of it on their website here. I am going to use the Short Names of the columns to save space in the example code below.

If I want to get a list of all the entries that are not active, and when they last run, I would use the following columns:

  • SCDJOBNAME - Job schedule's name
  • STATUS - Status of the entry
    • HELD status indicates that the entry has been held, and will not run again until it is released
    • SAVED indicates the entry was defined to run once in the past
  • SBMTIMSTMP - The date the entry was last submitted

Which come together in a SELECT statement like:

  SELECT SCDJOBNAME,STATUS,SBMTIMSTMP
    FROM QSYS2.SCHEDULED_JOB_INFO
   WHERE STATUS <> 'SCHEDULED'
   ORDER BY SCDJOBNAME

Which gives me this list:

SCHEDULED_JOB_NAME  STATUS     SBMTIMSTMP
     C*********     HELD       -
     H*********     HELD       2014-02-20-07.03.00
     L*********     SAVED      2012-09-05-23.30.00

If I just want a list of all the active entries, with when they are next scheduled to run, I would use the following additional columns:

  • SCDDATEV - Which schedule the entry is on
  • NXTSUBDATE - The date the entry will be next submitted
  • SCTIME - The time the entry is scheduled to run
  • FREQUENCY - How often the entry is submitted
  • DESCRIPTION - The description text entered by the person who created the entry
  • COMMAND - The command that is submitted

Which I would put in a SELECT statement like this:

  SELECT SCDJOBNAME,SCDDATEV,NXTSUBDATE,SCDTIME,FREQUENCY,
         DESCRIPTION,COMMAND
    FROM QSYS2.SCHEDULED_JOB_INFO
   WHERE STATUS = 'SCHEDULED'
   ORDER BY SCDJOBNAME

Which gives me:

SCHEDULED_JOB_NAME  SCDDATEV        NXTSUBDATE  SCDTIME   FREQUENCY
    A*********      SCHEDULED_DAYS   10/09/15   05:31:00  *WEEKLY
    A*********      SCHEDULED_DAYS   10/12/15   12:00:00  *WEEKLY
    B*********      SCHEDULED_DAYS   10/09/15   07:00:00  *WEEKLY
    B*********      SCHEDULED_DAYS   10/13/15   04:00:00  *WEEKLY

And to the right I have:

DESCRIPTION                      COMMAND
-                                SBMJOB CMD(CALL PGM(**********/****
-                                DSPOBJD OBJ(*ALL/*ALL) OBJTYPE(*LIB
Report of daily BRMS backup      CALL PGM(**********/**********)
Weekly send file to FTP server   CALL PGM(**********/**********)

The value of "SCHEDULE_DAYS" in the column SCDDATEV means that the entry is scheduled for days of the week.

The most common task I need to perform is to find which job runs a specific program, and when did it last run:

  SELECT SCDJOBNAME,SBMTIMSTMP,COMMAND
    FROM QSYS2.SCHEDULED_JOB_INFO
   WHERE COMMAND LIKE '%BCTL%'

There is only one row (record) that matches my criteria:

SCHEDULED_JOB_NAME  SBMTIMSTMP           COMMAND
    B*********      2015-11-18-07.00.00  CALL PGM(**********/BCTL******)

How about which entries have run today:

  SELECT SCDJOBNAME,SBMTIMSTMP
    FROM QSYS2.SCHEDULED_JOB_INFO
   WHERE CAST(SBMTIMSTMP AS DATE) = CURRENT_DATE
   ORDER BY SBMTIMSTMP

Notice that I had to use CAST on the column SBMTIMSTMP to convert it, from a timestamp, to a date. When it is a date I can then compare it to the current date. The output is:

SCHEDULED_JOB_NAME  SBMTIMSTMP
    S*********      2015-11-18-00.05.00
    P*********      2015-11-18-00.30.00
    D*********      2015-11-18-02.00.00
    A*********      2015-11-18-05.31.00
    B*********      2015-11-18-07.00.00

I am sure you can think of more uses for this View to extract the information about the entries on your IBM i.

 

You can learn more about on the IBM website here.

 

This article was written for IBM i 7.2.

28 comments:

  1. The SCHEDULED_JOB_INFO view was added to IBM i 7.2 with TR2 and is also available in IBM i 7.1 with TR10. So it is not 100% correct, that this is a IBM i 7.2 feature, but anyway very nice post.

    ReplyDelete
    Replies
    1. Thank you for the correction. Alas, the IBM i's I have access to running 7.1 have only TR7.

      Delete
  2. IBM sure is enhancing DB2. I love it!

    We're on 7.1 and getting some wierd results:
    The columns for the two timestamps are not working. I see data for the other columns.
    We're also getting some error messages:
    Data mapping error on member SCHED_JOB.
    Syntax of date, time, or timestamp value not valid.

    By the way, what is the difference, if any, between WHERE CAST(SBMTIMSTMP AS DATE) = CURRENT_DATE and WHERE DATE(SBMTIMSTMP) = CURRENT_DATE?

    ReplyDelete
    Replies
    1. As I explained to Ralph Petter I do not have access to an IBM i with IBM i 7.1 TR10, so I cannot test of help with your first issue.

      As for your question using DATE() is nicer than using CAST().

      Delete
  3. How to schedule job that I wants to run once in year. .

    ReplyDelete
    Replies
    1. schedule it for your date. in the cl put a addjobscde command to put one in for next year. if an entry is for a date thenits deleted when it runs

      Delete
  4. Glenn, i can confirm the problem with the SBMTIMSTMP in 7.1 TR10. I think this is a problem with the api which provides the data for the view. I will open a PMR tomorrow.

    ReplyDelete
  5. I suspect the answer to this is no, but if you update schedules_job_info, does it add a new entry? using sql to make jobd's , submitting jobs etc would be awesome.

    ReplyDelete
  6. I have reported the problem PMR 22913,060,618 in 7.1 TR10 to IBM. We will see if the have a solution for this problem.

    ReplyDelete
  7. I was just looking into this the other day. SQL has a USER constant but not JOB_NAME or JOB_NUMBER

    ReplyDelete
  8. IBM has provided a Test PTF for the problem with the timestamp columns. So if you are on 7.1 and have problems with the timestamp columns in the schedules_job_info view. Contact IBM support and ask for Test PTF SI58427. This PTF solves the problem on our machine and i think it will be available for all users soon.

    ReplyDelete
  9. I am adding entries to scheduler via command line (call qcmd) by addjobscde. I have to copy paste commands as I have prepared all my commands in excel. Is there a way to quickly setup these records for scheduler?

    ReplyDelete
    Replies
    1. You could...
      1. Create a file
      2. Download your XL into this file
      3. Create a program to read the file and use the ADDJOBSCDE command to add entries to the job scheduler.

      Delete
  10. How to get the same details when you have advance scheduler (GO JS) ?

    ReplyDelete
    Replies
    1. That menu, JS, is a custom menu someone created on your IBM i.

      Now take the information displayed on that menu and send me the displayed details as an Excel spreadsheet. Not so easy reading the information from the menu. Easy using this SQL view.

      Delete
  11. Excellent. I too have been frustrated trying to find details of WRKJOBSCDE entries. A very handy tool. Thx!

    ReplyDelete
  12. This is great information, thank you. One question I have is how to see the specific days the jobs are scheduled for. For example, if a job runs on Tuesdays and Thursdays, the SQL results for SCDDATEV us "Schedule_Days". Is there a way to show the specific days that are on the job scheduler?

    ReplyDelete
    Replies
    1. Look in the column SCHEDULED_DAYS (SCDDAYS).

      SELECT SCDJOBNAME,SCDDAYS
      FROM QSYS2.SCHEDULED_JOB_INFO
      WHERE STATUS = 'SCHEDULED'
      ORDER BY SCDJOBNAME

      Delete
  13. VERY USEFUL! Thanks for sharing this!!

    ReplyDelete
  14. What about the jobs that we can see through WRKJOBJS? is there any way to see those jobs detail?

    ReplyDelete
    Replies
    1. I am sure there is as data is data.
      I cannot give an example as none of the partitions I use are using that tool.
      You will have to look in the library QUSRIJS and see which files contain the data you are looking for.

      Delete
  15. Been using this for a very long time. It's incredibly handy!

    ReplyDelete
  16. Is there also a possibility to do such an SQL query for the scheduler entries in OS version V6R1M0 where QSYS2/SCHEDULED_JOB_INFO doesn't exist?

    ReplyDelete
    Replies
    1. If you can find the file(s) that contain the same information you could use those directly.

      Or you would have to output the contents of WRKJOBSCDE to a spool file, read the spool file, & construct a file from that. This will be time consuming.

      Delete
  17. Hello Simon, I have been reading almost all your helpful tips and tricks, is there a table to query the "advanced job scheduler", aside from the SCHEDULED_JOB_INFO?

    ReplyDelete
    Replies
    1. Alas, I have never worked on a partition that has the Advanced Job Scheduler, therefore, I cannot answer your question.

      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.