You may have been running Robot Schedule for a few years or even for decades. In that time, your business has undoubtedly changed, introduced new staff, and wound up with jobs that haven’t run for a while. It’s not a lot of overhead, but the mess can be confusing to new staff members.
When it comes to cleaning up these old Robot Schedule jobs, the best way is to use a SQL statement that you can run from STRSQL. This is a lot easier if you’re also using Sequel—and our support team can help you!—but if you aren’t, try the SQL statement below.
What Is STRSQL?
SQL is a feature of IBM i, accessible through STRSQL on a command line, with the ability to access data in files. You may need to add the library QSQL to your library list. To clean up old jobs, we use it to look at the Robot Schedule history stored in RBTMSG and we join this with the RBTROB file to build a list of jobs with no job history.
Once you’ve found your list, you’ll still want to review the list before blindly deleting jobs. You might have just created a job so it has no history or perhaps someone has interactively deleted the history—you will want to look for this, too. Remember to SAVLIB ROBOTLIB to a SAVF or tape before deleting any jobs so you have a backup.
Start Your Robot Job Cleanup
By including a HAVING clause, you can modify how many days you want to go back. Copy the SQL cleanup statement below for a list of Robot jobs that have not run in 365 days:
with rbt(jobnam, kytime , jobh, schovr, maxd) as(SELECT rbtrob.jobnam, rbtrob.kytime, case when cmrjob<>'0'
then ' ' else ' None' end AS jobh, rbtrob.schovr, cast (case when cmedat>0 then cmedat+19000000 else 0 end as Decimal(8,0)) AS maxd
FROM robotlib.rbtrob rbtrob Left Outer JOIN robotlib.rbtmsg rbtmsg ON rbtrob.KYTIME=rbtmsg.CMRJOB )
SELECT jobnam,kytime as Job_#,jobh as Job_Hist,schovr, char(max(maxd)) as LastRun_YYYMMDD
GROUP BY rbt.jobnam,rbt.kytime,jobh, rbt.schovr
HAVING jobh=' None' or MAX(case when maxd>0 then maxd else null end) < decimal((replace(char(current date-365 days, ISO),'-','')),8,0)
ORDER BY rbt.jobnam ASC,rbt.kytime ASC
Best of luck with this SQL statement! If you need help on this tip, consider taking questions to our Sequel support team. If you already own Sequel, they can help you put this logic into Sequel view.