SQL report examples

Examples of queries that can be run using the SQL custom reports.

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