Querying Job Info

[jbeach]

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.

[Marc Clasby]

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...

SELECT

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

FROM

dbo.jobmst INNER

JOIN

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

Where

jobrun_proddt =

'12/17/2012'

[bluehouseinc]

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?

[Jay Kish]

I am also looking to do the same thing. I see this is a very old thread and wonder ing anyone has actually compelted a query?

[Marc Clasby]

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

file...export

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.

SELECT

dbo.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

FROM

dbo.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

WHERE

(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.

Example

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

Marc