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.

12 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

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.