Job distinct / unique / total runs

If a job goes in ABEND and it is not fixed in the same TWS day it will be added to TWS historical database multiple times (as many times as UPDATESTATS jobs run) so the job runs number isn't accurate when we use rep7 to report them, below examples are ran using ORACLE DB.


The below SQL query will remove the duplicate items added by UPDATESTATS for unattended ABEND jobs:

       
alter session set current_schema=maestro_DB_user;

SELECT count(*) FROM (SELECT DISTINCT AJS_NAME, AJB_NAME, JHR_START_TIME, JHR_RUN_DATE, 
JHR_TOTAL_ELAPSED_TIME,JHR_STATUS, WKC_NAME FROM JHR_JOB_HISTORY_RUNS
WHERE jhr_run_date>=trunc(sysdate-12) AND jhr_run_date <trunc(sysdate+1)) 
WHERE (AJB_NAME like 'MAKEPLAN')

Also if you want to see only the unique jobs that ran on a specific workstation:

       
alter session set current_schema=maestro_DB_user;

SELECT count(*) FROM (SELECT UNIQUE AJB_NAME FROM (SELECT DISTINCT AJS_NAME, AJB_NAME, JHR_START_TIME,
JHR_RUN_DATE, JHR_TOTAL_ELAPSED_TIME , JHR_STATUS, WKC_NAME  FROM JHR_JOB_HISTORY_RUNS
WHERE jhr_run_date>=trunc(sysdate-12) AND jhr_run_date <trunc(sysdate+1)) 
WHERE (WKC_NAME like 'TWSMASTER'))

And a nice report containing all job runs, all success runs, all ABEND / CANCEL ones, unique jobs success and unique jobs failed:

       
alter session set current_schema=maestro_DB_user;

SELECT
Count (CASE WHEN JHR_STATUS = 'S' or JHR_STATUS = 'A' 
or JHR_STATUS = 'C' THEN '1' ELSE NULL END) All_Job_Runs,
Count (CASE WHEN JHR_STATUS = 'S' THEN '1' ELSE NULL END) All_Runs_Sucessful,
Count (CASE WHEN JHR_STATUS = 'A' or JHR_STATUS = 'C' THEN '1' ELSE NULL END) All_Runs_Failed,
Count (DISTINCT CASE WHEN JHR_STATUS = 'S' THEN AJB_NAME ELSE NULL END) Unique_Jobs_Successful,
Count (DISTINCT CASE WHEN JHR_STATUS = 'A' 
or JHR_STATUS = 'C' THEN AJB_NAME ELSE NULL END) Unique_Jobs_Failed
FROM (select distinct AJS_NAME, AJB_NAME, JHR_START_TIME, JHR_RUN_DATE, JHR_TOTAL_ELAPSED_TIME,
JHR_STATUS, WKC_NAME from JHR_JOB_HISTORY_RUNS
WHERE jhr_run_date>=trunc(sysdate-12) AND jhr_run_date <trunc(sysdate+1));

Hint: Modify sysdate-+XX to adjust the number of days on which the reports are ran, also you can use dates as: 

       
jhr_run_date>=TO_DATE('10/03/2012','dd/mm/yyyy') AND jhr_run_date <TO_DATE('12/03/2010','dd/mm/yyyy')