Hello, I am struggling with finding the right query or queries to pull data from the Tidal database that is just like the information displayed in the Job Activity window in the client. This is mainly for reporting and information purposes. I'm not running the Reporting Module since it was sunsetted before I knew about it. Has anyone had success doing something like this? Are there any examples or a data dictionary that is availble? Any info is appreciated.
There is a data dictionary but I believe you would need to ask support for it. It may be too much work for what you are trying to accomplish. Your requirements (replication of job activity screen) would be difficult for multiple reasons (group levels, occurance, status code vs status in GUI).Depending on your needs, you may be better off providing all users a read only view.
Depending on what you are looking for something like below might work. You would need to join jobmst and jobrun and possibly agent tables (nodmst,nodlstmst) if you want that detail. There is an cross reference of status in CMD line interface guide (example status 101= Completed Normally and status 103 is Completed Abnormally...
dbo.jobrun.jobrun_proddt, dbo.jobmst.jobmst_prntname, dbo.jobmst.jobmst_name, dbo.nodlstms.nodlstmst_name, dbo.nodmst.nodmst_name,
dbo.jobrun.jobrun_status, dbo.jobrun.jobrun_duration, dbo.jobrun.jobrun_fromtm, dbo.jobrun.jobrun_untiltm
dbo.jobrun ON dbo.jobmst.jobmst_id = dbo.jobrun.jobmst_id LEFT OUTER JOIN
dbo.nodlstms ON dbo.jobrun.nodlstmst_id = dbo.nodlstms.nodlstmst_id LEFT OUTER JOIN
dbo.nodmst ON dbo.jobrun.nodmst_id = dbo.nodmst.nodmst_id
There is a Scheduler Data Model.chm file on the 5.3 CD2 doc folder that contains all the information about the tables you will need but please know that the jobrun table is the heart of the scheduler and you can cause problems if you query that table the wrong way or to much.
Also - What version of TES are you running?
I do many queries mostly ad-hoc but I generally mine for specific things
A hidded gem that probably goes mostly unused is the export function which might server your reporing needs
save as text (it exports comma delimited)
You can do this from any grid it seems and is a quick way to get a nice report. The export honors the view preferenes and filters for job activity and job definitions AND it gives you the translations for things like status.
(I recommend removing job params and having fully expanded)
Otherwise are you looking for a query for the activity let me know what you want in the header (mine no longer has the default)
Here's an example on how to create a SQL query for you own needs.
Copy this query into SQL MGMT Studio then use CTL+SHIFT+Q to open Query designer thye you cna add any details you need
from jobrun, jobmst, jobdtl tables then you can pick and choose the fields and headers you want (column header = alias) I limited to a single day.
SELECTdbo.jobrun.jobrun_id AS JobNumber, dbo.jobmst.jobmst_name AS Name, dbo.jobrun.jobrun_status AS Status_ID, dbo.jobrun.jobrun_esttime AS EstStart,
dbo.jobrun.jobrun_estduration AS EstDuration, dbo.jobrun.jobrun_proddt
FROMdbo.jobrun INNER JOIN
dbo.jobmst ON dbo.jobrun.jobmst_id = dbo.jobmst.jobmst_id INNER JOIN
dbo.jobdtl ON dbo.jobmst.jobdtl_id = dbo.jobdtl.jobdtl_id
(dbo.jobrun.jobrun_proddt = CONVERT(DATETIME, '2013-04-04 00:00:00', 102))
the database query can get a little complicated as different values are translated and there are differnt types of joins etc.
Status is stored as a number in job run table. A jobrun_status of 101 = completed normally, 103 = abnormally, 1 = waiting on depdendecies, etc.
I think people found little value in SQL query as a reporting mechanism but its fantastic for researching things