Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
I was asked to show how SQL could be used to analyze QBATCH subsystem job history. Super grouping to the rescue.
--
-- Subject: QBATCH Job Analysis
-- Author: Scott Forstie
-- Date : May 30, 2021
-- Note : This Gist leverages different built-in functions and grouping support to explore job history
--
-- ================================================================================================
--
-- description: 20 Jobs that ran (started and ended) the longest in QBATCH over the last 24 hours
--
-- ================================================================================================
WITH JOB_START(start_time, from_user, sbs, from_job, tokens) AS (
SELECT message_timestamp as time,
from_user,
substr(message_tokens, 59, 10) as subsystem,
from_job,
message_tokens
FROM TABLE(qsys2.history_log_info(START_TIME => CURRENT TIMESTAMP - 24 hours,
END_TIME => CURRENT TIMESTAMP)) x
WHERE message_id = 'CPF1124' and substr(message_tokens, 59, 10) = 'QBATCH'
ORDER BY ORDINAL_POSITION DESC
) SELECT TIMESTAMPDIFF(4, CAST(b.message_timestamp - a.start_time AS CHAR(22)))
AS execution_minutes, DAYNAME(b.message_timestamp) AS JOB_END_DAY,
a.from_user as user_name, a.from_job as job_name
FROM JOB_START A INNER JOIN
TABLE(qsys2.history_log_info(START_TIME => CURRENT TIMESTAMP - 24 hours,
END_TIME => CURRENT TIMESTAMP)) b
ON b.from_job = a.from_job
WHERE b.message_id = 'CPF1164'
ORDER BY TIMESTAMPDIFF(4, CAST(b.message_timestamp - a.start_time AS CHAR(22))) desc limit 20;
stop;
-- ===========================================================================================
--
-- description: How many batch jobs are processed each day? (today and previous 6 days)
--
-- ===========================================================================================
WITH JOB_START(start_time, from_user, sbs, from_job, tokens) AS (
SELECT message_timestamp as time,
from_user,
substr(message_tokens, 59, 10) as subsystem,
from_job,
message_tokens
FROM TABLE(qsys2.history_log_info(START_TIME => CURRENT date - 6 days,
END_TIME => CURRENT TIMESTAMP)) x
WHERE message_id = 'CPF1124' and substr(message_tokens, 59, 10) = 'QBATCH'
ORDER BY ORDINAL_POSITION DESC
) SELECT
DATE(b.message_timestamp) AS DATE,
count(*) as job_count -- <<<====
FROM JOB_START A INNER JOIN
TABLE(qsys2.history_log_info(START_TIME => CURRENT date - 6 days,
END_TIME => CURRENT TIMESTAMP)) b
ON b.from_job = a.from_job
WHERE b.message_id = 'CPF1164'
Group by DATE(b.message_timestamp) -- <<<====
order by date;
stop;
-- ===========================================================================================
--
-- description: Break down by day, user, and job duration (today and previous 6 days)
--
-- ===========================================================================================
with JOB_START (start_time, from_user, sbs, from_job, tokens) as (
select message_timestamp as time, from_user, substr(message_tokens, 59, 10) as subsystem,
from_job, message_tokens
from table (
qsys2.history_log_info(
START_TIME => current date - 6 days, END_TIME => current timestamp)
) x
where message_id = 'CPF1124' and
substr(message_tokens, 59, 10) = 'QBATCH'
order by ORDINAL_POSITION desc),
batch_jobs (date, user_name, job_minutes) as (
select date(b.message_timestamp) as DATE, a.from_user,
timestampdiff(4, cast(b.message_timestamp - a.start_time as char(22)))
from JOB_START A
inner join table (
qsys2.history_log_info(
START_TIME => current date - 6 days, END_TIME => current timestamp)
) b
on b.from_job = a.from_job
where b.message_id = 'CPF1164'
)
select date, user_name, sum(job_minutes) as total_job_minutes -- <<<====
from batch_jobs
group by date, user_name -- <<<====
order by 1, 3 desc;
stop;
-- ===========================================================================================
--
-- description: Break down by day, user, with counts (today and previous 6 days)
--
-- ===========================================================================================
with JOB_START (start_time, from_user, sbs, from_job, tokens) as (
select message_timestamp as time, from_user, substr(message_tokens, 59, 10) as subsystem,
from_job, message_tokens
from table (
qsys2.history_log_info(
START_TIME => current date - 6 days, END_TIME => current timestamp)
) x
where message_id = 'CPF1124' and
substr(message_tokens, 59, 10) = 'QBATCH'
order by ORDINAL_POSITION desc),
batch_jobs (date, user_name) as (
select date(b.message_timestamp) as DATE, a.from_user
from JOB_START A
inner join table (
qsys2.history_log_info(
START_TIME => current date - 6 days, END_TIME => current timestamp)
) b
on b.from_job = a.from_job
where b.message_id = 'CPF1164'
)
select date, user_name, count(*) as total_jobs
from batch_jobs
group by date, user_name -- <<<====
order by 1, 3 desc;
stop;
-- ===========================================================================================
--
-- description: Break down using the ROLLUP super group (today and previous 6 days)
--
-- ===========================================================================================
with JOB_START (start_time, from_user, sbs, from_job, tokens) as (
select message_timestamp as time, from_user, substr(message_tokens, 59, 10) as subsystem,
from_job, message_tokens
from table (
qsys2.history_log_info(
START_TIME => current date - 6 days, END_TIME => current timestamp)
) x
where message_id = 'CPF1124' and
substr(message_tokens, 59, 10) = 'QBATCH'
order by ORDINAL_POSITION desc),
batch_jobs (date, user_name) as (
select date(b.message_timestamp) as DATE, a.from_user
from JOB_START A
inner join table (
qsys2.history_log_info(
START_TIME => current date - 6 days, END_TIME => current timestamp)
) b
on b.from_job = a.from_job
where b.message_id = 'CPF1164'
)
select date, coalesce(user_name, dayname(date)) as Name, count(*) as total_jobs
from batch_jobs
group by rollup (date, user_name) -- <<<====
order by 1, 3 desc;
stop;
-- ===========================================================================================
--
-- description: Break down using the ROLLUP super group (today and previous 6 days)
--
-- ===========================================================================================
with JOB_START (start_time, from_user, sbs, from_job, tokens) as (
select message_timestamp as time, from_user, substr(message_tokens, 59, 10) as subsystem,
from_job, message_tokens
from table (
qsys2.history_log_info(
START_TIME => current date - 6 days, END_TIME => current timestamp)
) x
where message_id = 'CPF1124' and
substr(message_tokens, 59, 10) = 'QBATCH'
order by ORDINAL_POSITION desc),
batch_jobs (date, user_name, job_minutes) as (
select date(b.message_timestamp) as DATE, a.from_user, timestampdiff(
4, cast(b.message_timestamp - a.start_time as char(22)))
from JOB_START A
inner join table (
qsys2.history_log_info(
START_TIME => current date - 6 days, END_TIME => current timestamp)
) b
on b.from_job = a.from_job
where b.message_id = 'CPF1164'
),
rollup_jobs (date, name, total) as (
select date, coalesce(user_name, dayname(date)) as Name, count(*) as total_jobs
from batch_jobs
group by rollup (date, user_name)
order by 1, 3 desc)
select date, coalesce(name, 'Total...') as name, total -- <<<====
from rollup_jobs
order by 1, 3 desc;
stop;
-- ===========================================================================================
--
-- description: Break down using the CUBE super group (today and previous 6 days)
--
-- ===========================================================================================
with JOB_START (start_time, from_user, sbs, from_job, tokens) as (
select message_timestamp as time, from_user, substr(message_tokens, 59, 10) as subsystem,
from_job, message_tokens
from table (
qsys2.history_log_info(
START_TIME => current date - 6 days, END_TIME => current timestamp)
) x
where message_id = 'CPF1124' and
substr(message_tokens, 59, 10) = 'QBATCH'
order by ORDINAL_POSITION desc),
batch_jobs (date, user_name) as (
select date(b.message_timestamp) as DATE, a.from_user
from JOB_START A
inner join table (
qsys2.history_log_info(
START_TIME => current date - 6 days, END_TIME => current timestamp)
) b
on b.from_job = a.from_job
where b.message_id = 'CPF1164'
)
select date, coalesce(user_name, dayname(date)) as Name, count(*) as total_jobs
from batch_jobs
group by cube (date, user_name) -- <<<====
order by 1, 3 desc;
stop;
-- =======================================================================================================
--
-- description: Break down using the CUBE super group (today and previous 6 days) - examining temp stg
--
-- =======================================================================================================
with JOB_START (start_time, from_user, sbs, from_job, tokens) as (
select message_timestamp as time, from_user, substr(message_tokens, 59, 10) as subsystem,
from_job, message_tokens
from table (
qsys2.history_log_info(
START_TIME => current date - 6 days, END_TIME => current timestamp)
) x
where message_id = 'CPF1124' and
substr(message_tokens, 59, 10) = 'QBATCH'
order by ORDINAL_POSITION desc),
batch_jobs (job_end, user_name, max_temp_stg_mb) as (
select date(b.message_timestamp) as DATE,
a.from_user,
interpret(binary(right(message_tokens, 4),4) as integer) -- <<<====
from JOB_START A
inner join table (
qsys2.history_log_info(
START_TIME => current date - 6 days, END_TIME => current timestamp)
) b
on b.from_job = a.from_job
where b.message_id = 'CPF1164'
)
select job_end,
coalesce(user_name,
dayname(job_end)) as Name,
sum(max_temp_stg_mb) as total_max_temp_stg_mb
from batch_jobs
group by cube (job_end, user_name)
order by 1, 3 desc;
stop;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment