09-10-2014 11:00 AM - edited 03-01-2019 09:10 AM
what are the table names for the job activity. i tried looking up all the tidal tables and on the tidal data model help file and i could not find them. please advise. thanks.
Solved! Go to Solution.
09-11-2014 07:33 AM
Hi Warren,
The data from job activity can be extracted from the "jobrun" table. And use JOIN statements with jobmst, jobdtl, nodmst etc to build a more comprehensive report.
SELECT
dbo.jobrun.jobrun_proddt as [PROD DATE],
dbo.jobmst.jobmst_prntname as [PARENT GROUP],
dbo.jobmst.jobmst_name as [JOB NAME],
dbo.jobrun.jobrun_time as [START TIME],
dbo.jobrun.jobrun_duration as [DURATION (s)],
CASE dbo.jobrun.jobrun_status
WHEN '1' THEN 'JOB_STATUS_WAIT'
WHEN '3' THEN 'JOB_STATUS_HOLD'
WHEN '51' THEN 'JOB_STATUS_ACTIVE'
WHEN '52' THEN 'JOB_STATUS_STOP'
WHEN '53' THEN 'JOB_STATUS_DEFERRED'
WHEN '66' THEN 'JOB_STATUS_ERROR'
WHEN '101' THEN 'JOB_STATUS_NORMAL'
WHEN '103' THEN 'JOB_STATUS_ABNORMAL'
WHEN '104' THEN 'JOB_STATUS_SKIPPED'
WHEN '105' THEN 'JOB_STATUS_ORPHAN'
WHEN '106' THEN 'JOB_STATUS_ABORTED'
WHEN '108' THEN 'JOB_STATUS_TIMEOUT'
WHEN '109' THEN 'JOB_STATUS_CANCELLED'
END AS [STATUS]
FROM
dbo.jobmst INNER JOIN
dbo.jobrun ON dbo.jobmst.jobmst_id = dbo.jobrun.jobmst_id
WHERE
dbo.jobrun.jobrun_proddt BETWEEN 'mm/dd/yyyy' AND 'mm/dd/yyyy'
ORDER BY [PROD DATE]
BR,
Derrick Au
09-11-2014 07:33 AM
Hi Warren,
The data from job activity can be extracted from the "jobrun" table. And use JOIN statements with jobmst, jobdtl, nodmst etc to build a more comprehensive report.
SELECT
dbo.jobrun.jobrun_proddt as [PROD DATE],
dbo.jobmst.jobmst_prntname as [PARENT GROUP],
dbo.jobmst.jobmst_name as [JOB NAME],
dbo.jobrun.jobrun_time as [START TIME],
dbo.jobrun.jobrun_duration as [DURATION (s)],
CASE dbo.jobrun.jobrun_status
WHEN '1' THEN 'JOB_STATUS_WAIT'
WHEN '3' THEN 'JOB_STATUS_HOLD'
WHEN '51' THEN 'JOB_STATUS_ACTIVE'
WHEN '52' THEN 'JOB_STATUS_STOP'
WHEN '53' THEN 'JOB_STATUS_DEFERRED'
WHEN '66' THEN 'JOB_STATUS_ERROR'
WHEN '101' THEN 'JOB_STATUS_NORMAL'
WHEN '103' THEN 'JOB_STATUS_ABNORMAL'
WHEN '104' THEN 'JOB_STATUS_SKIPPED'
WHEN '105' THEN 'JOB_STATUS_ORPHAN'
WHEN '106' THEN 'JOB_STATUS_ABORTED'
WHEN '108' THEN 'JOB_STATUS_TIMEOUT'
WHEN '109' THEN 'JOB_STATUS_CANCELLED'
END AS [STATUS]
FROM
dbo.jobmst INNER JOIN
dbo.jobrun ON dbo.jobmst.jobmst_id = dbo.jobrun.jobmst_id
WHERE
dbo.jobrun.jobrun_proddt BETWEEN 'mm/dd/yyyy' AND 'mm/dd/yyyy'
ORDER BY [PROD DATE]
BR,
Derrick Au
09-11-2014 08:02 AM
thanks so much. i figure that out that the table is the tidal.jobrun. the info that you provided is also very helpful.
Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community: