View Scheduled Jobs with Excel
April 19, 2016 Ted Holt
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.