Querying Tidal Schedule information from database

[JBAdamsJr]

I have made a copy of our production Tidal database for reporting and analysis purposes. One of the things that I would like to do is to get a list of our current production jobs that are enabled and the date/time of the last time it ran (successfully or not). How could I get that schedule information as to the last time the job ran from the Tidal database tables? Is this possible from a database query?

The goal is to get a list of enabled jobs that have not run in several weeks/months, most likely due to not having its file dependencies met, to see which of those can be disabled or removed completely. There may be cases where clients are no longer sending us files (maybe because they are no longer clients) that have Tidal jobs set up to process them, so we can get rid of them to clean up our list of Tidal jobs. Any help with this would be appreciated.

[Tracy Donmoyer]

Something like this may work:

select

jobmst.jobmst_name, max(jobrun.jobrun_time)

from jobrun

join jobmst

on jobrun.jobmst_id = jobmst.jobmst_id

group by jobmst.jobmst_name

The job name and active status are found in the jobmst table.  The jobrun table has the information you see in the "Job Activity" view.

Creating a copy of the DB for reporting is a good idea.  You don't want a query tying up your production DB.

Tracy

[JBAdamsJr]

One question I have is in regard to the JobRun table you reference. We have thousands of Tidal jobs that have been running for at least 2 years. When I do a query of just the JobRun table, it only returns around 5000 rows, and it does not include the JobMst_ID value of one particular job that I deal with. Why would the JobRun table have so few rows?

[Tracy Donmoyer]

Just to make sure we're on the same page, when you execute

select count(*)

  from jobrun

you get ~5,000?

Do you have "Automatic Daily History Cleanup" turned on?  From the menu select Activities, Configure Schedule.  About halfway down the left column of the Master tab is the setting.  Is it selected?  This will purge job history from the schedule based upon the "History Retention" specified on the Options tab of the Job Definition.

Still, 5,000 rows seems low.  How many jobs do you have defined (in the jobmst table)?  If you are getting 5,000 rows returned using my example it could be correct.  The query should only return one row per job, the most recent run.  Or there could be something wrong with the query.

[JBAdamsJr]

I am trying to query this database in SQL SERVER and I get the ~5000 rows with both Query Analyzer and the SQL Management Studio. When I use Access 2010, I get over 218000 rows in the JobRun table. I am not sure that this is all of the rows showing in Access, because it might be a limitation in its query. So the table does have many more rows than I get in Query Analyzer.

We do have the Automatic Daily History Cleanup turned on with the Trigger History Retention set to 30 days. So in Access when I query one particular job by JobMst_ID, I do get less than 30 rows; it currently goes back about 2-3 weeks or so. Does this mean that if a job has not run in the past 30 days, that it will have no records in the JobRun table, and there will be no way to determine that last date/time a job ran if it was before 30 days ago?

[Tracy Donmoyer]

I didn't think about the query tool restricting the number of rows returned.  I'm sure that is what is happening with Query Analyzer.

Executing "select count(*) from jobrun" will give you an accurate count of the number of rows in the table since it only returns one row.

"Trigger History Retention" does not determine how many days "Job History" will be retained.  Click on the Help Icon (?) for a description of the "Trigger History Retention" setting.

The Defaults tab has the default value for "Job History Retention".  This determines how long Job Activity is retained.  If this is 30 days, then, if a job has not run in the last 30 days, the job may not have entries in the jobrun table.

When a job is created, the "Job History Retention" value is used to set the "History Retention" of the job (found on the Job Definition, Options tab).  If you have never changed the value in the Defaults tab, then it is likely all your jobs have this value.  But if the Default value was changed at some point, then the jobs created before the value was changed will have one value, the jobs created after the change will have another value.  To determine the History Retention value for a single job, you can check the Options tab of the Job Definition.  To find out if all your jobs have the same value, you can run this query.

select

distinct jobdtl.jobdtl_retnsn

from jobdtl

If your default for "Job History Retention" was never changed, this will return a single value.

If you know a job that runs infrequently, you can look at the History tab of the job.  I'm not sure what to expect.  If the job only runs quarterly, for example, will there be no history shown if your Job History Retention is set to 30 days?

It may be possible to determine the last time a job ran by querying the msglog table.  This table can be quite large, so you have to be careful constructing your queries.  The Logging tab of the System Configuration specifies how long these records are retained.

Tracy

[JBAdamsJr]

I will look into those items further, but just so you know, even when I just try getting the count of the JobRun table, I get the same number of rows (~5000) as when I try to query it directly.