View Scheduled Jobs with Excel

At the recent RPG & DB2 Summit in Dallas, I presented a session that dealt with the use of SQL in CL. One of my examples used the SCHEDULED_JOB_INFO view to retrieve scheduled jobs from the IBM i job scheduler. After the talk, one of the attendees gave me a great idea, and I’ve just got to pass it along to you.

SCHEDULED_JOB_INFO returns the same information that you can see with the Work with Job Schedule Entries (WRKJOBSCDE) command. It’s one of the IBM i Services. The gentleman told me that he and others in his shop had been working to get a handle on scheduled jobs. WRKJOBSCDE does not lend itself to analysis, and API’s are hard to work with. His idea was to use my query to build a database table, then use Copy to Import File (CPYTOIMPF) to create a CSV file, which Excel could open. The following CL code gives the general idea.

dcl   &SqlCmd    *char    512

chgvar  &SqlCmd +
   ('declare global temporary table JobInfo as +
     (select scheduled_job_name, scheduled_days, +
     next_submission_date, status, +
     description, sbmtimstmp, sbmjobsts +
     from scheduled_job_info +
     where scheduled_days like ''%*TUE%'' +
     or scheduled_days like ''%*ALL%'') +
     with data with replace')
runsql   sql(&SqlCmd) commit(*none)

cpytoimpf  fromfile(qtemp/JobInfo) tostmf(JobInfo.csv) +
             mbropt(*replace) stmfccsid(*pcascii) +
             rcddlm(*crlf) addcolnam(*sql)

Why do I keep editing this newsletter and speaking at conferences? Because I can’t afford not to. I learn more from others than what others learn from me.

Ted Holt welcomes your comments and questions. Email him through the IT Jungle Contacts page.