rep7 SQL query

rep7 is very slow and hard to use it, below is the SQL queries version for MAKEPLAN job (using ORACLE DB):

       
alter session set current_schema=maestro_DB_user;

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 (AJB_NAME like 'MAKEPLAN') order by AJB_NAME

Job average run

The below SQL query calculates the job average run time (in our case MAKEPLAN) using the historical data stored in the DB (using ORACLE DB):

       
alter session set current_schema=maestro_DB_user;

select avg (JHR_TOTAL_ELAPSED_TIME) as average
FROM JHR_JOB_HISTORY_RUNS
where AJB_NAME like 'MAKEPLAN'

If you want to calculate the average run time only for success jobs run:

       
alter session set current_schema=maestro_DB_user;

select avg (JHR_TOTAL_ELAPSED_TIME) as average
FROM JHR_JOB_HISTORY_RUNS
where AJB_NAME like 'MAKEPLAN' and JHR_STATUS = 'S'

Hint: The historical data age is controlled by the variable statsHistory / sh, the default value is 10 days.

Jobs without Job Stream

To find out which Jobs does not belong to Job Stream, the easiest way is to run a SQL query (using ORACLE DB):

       
alter session set current_schema=maestro_DB_user;

SELECT JOD_NAME FROM JOD_JOB_DEFINITIONS
WHERE JOD_NAME NOT IN
(SELECT AJB_NAME FROM AJB_ABSTRACT_JOBS)