I was asked to show how SQL could be used to analyze QBATCH subsystem job history. Super grouping to the rescue.

QBATCH job study.sql

--
-- 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;