SQL report examples

This section provides some examples of queries that can be run using the SQL custom reports.

Jobs grouped by return codes

For each return code, this query returns the number of jobs that ended with the corresponding return code:

SELECT DISTINCT return_code AS RC count(job_name) AS ,#JOB

FROM mdl.job_history_v

GROUP BY return_code

Table 1. Example of query outcome
RC	#JOB
0	1670
5	11
6	1
50	2
127	352

Job statistics grouped on job status

For each job status, this query returns the number of jobs that ended with the corresponding job status and also the planned duration time, the total elapsed time, and total CPU time:

SELECT job_status, count(job_name) AS job count, floor(sum(planned_duration/1000)) AS

planned duration, floor(sum(total_elapsed_time/1000)) AS total elapsed,

floor(sum(total_cpu_time/1000)) AS total cpu

FROM mdl.job_history_v GROUP BY job_status

FROM mdl.job_history_v

GROUP BY return_code

Table 2. Example of query outcome
JOB_STATUS	JOB COUNT	PLANNED DURATION	TOTAL ELAPSED	TOTAL CPU
A	366	0	21960	0
S	1670	1413360	1423500	183

Jobs in a range of return code

This query returns the number of job in a range of return codes

SELECT *

FROM (select DISTINCT return_code, count(job_name) AS#JOB

FROM mdl.job_history_v

GROUP BY return_code) AS temp

WHERE return_code > 0 AND return_code < 6

Table 3. Example of query outcome
RETURN_CODE	#JOB
5	11

Jobs that ran within a time range and finished with a specific job status

SELECT WORKSTATION_NAME, JOB_NAME, JOB_RUN_DATE_TIME

FROM MDL.JOB_HISTORY_V

WHERE JOB_RUN_DATE_TIME BETWEEN '2008-05-19 10:00:00.0' AND '2008-05-19

21:00:00.0' AND JOB_STATUS <> 'S'

ORDER BY JOB_RUN_DATE_TIME

Table 4. Example of query outcome
WORKSTATION_NAME	JOB_NAME	JOB_RUN_DATE_TIME
NC122072	PEAK_A_06	2008–08–03 23:23:00.0
NC122072	JOB_RER_A	2008–08–03 23:23:00.0
NC122072	PEAK_A_13	2008–08–03 23:23:00.0
NC122072	PEAK_A_20	2008–08–03 23:23:00.0
NC122072	PEAK_A_27	2008–08–03 23:23:00.0
NC122072	PEAK_A_43	2008–08–03 23:23:00.0
NC122072	PEAK_B_19	2008–08–03 23:24:00.0