Using SQL to get information about Job Scheduled entries

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.