Skip to content

Instantly share code, notes, and snippets.

@forstie

forstie/coolstuff.read_joblog.sql

Last active April 15, 2024 16:58
Show Gist options
  • Save forstie/3196584921e0b3634cce201fdad86f13 to your computer and use it in GitHub Desktop.
Save forstie/3196584921e0b3634cce201fdad86f13 to your computer and use it in GitHub Desktop.
Download ZIP
Joblogs... important, but needlessly difficult to automate or consume. This gist takes on this topic....
Raw
coolstuff.read_joblog.sql
--
-- Subject: Reading a joblog
-- Author: Scott Forstie
-- Date : January, 2024
-- Features Used : This Gist uses SQL PL, qsys2.joblog_info, sys2.history_log_info, QSYS2.SPOOLED_FILE_INFO,
-- systools.SYSTOOLS.SPOOLED_FILE_DATA, systools.ended_job_info, listagg
--
-- If you're like me, you've found it frustrating that a joblog is only a joblog while the job is active.
-- What happens when the joblog wraps or the job completes? That's up to whomever constructed the job,
-- but in many cases the answer is one or more spooled files exist with the joblog data.
-- The spooled files are conveniently and appropriately named... QPJOBLOG.
--
-- But, the savvy SQL user doesn't want to be looking here and there and everywhere for the joblog deetz.... do they?
--
-- This gist ends with a UDTF that encompasses both scenarios, leaving the end user with a single (tour de force?)
-- function to use to capture and/or interrogate the joblog for a specific job.
-- #SQLcandoit
--
-- Resources:
-- https://www.ibm.com/docs/en/i/7.5?topic=services-joblog-info-table-function
-- https://www.ibm.com/docs/en/i/7.5?topic=services-spooled-file-info-table-function
-- https://www.ibm.com/docs/en/i/7.5?topic=services-spooled-file-data-table-function
-- https://www.ibm.com/docs/en/i/7.5?topic=services-ended-job-info-table-function
--
stop;
--
-- First... lets execute a job in QBATCH that will produce a joblog (and then a spooled file).
--
cl:SBMJOB CMD(QSYS/RUNSQL SQL('Begin declare lv integer default 100; declare lc integer default 0; while (lc<lv) do set lc=lc+1; call systools.lprintf('''' concat lc concat ''''); end while; end')
commit(*NONE)) JOB(COOLIO) LOG(4 00 *SECLVL) LOGOUTPUT(*JOBEND) JOBMSGQFL(*PRTWRAP);
stop;
--
-- As noted already, once the job completes, qsys2.joblog_info() will fail
--
create or replace variable coolstuff.thejob varchar(28);
set coolstuff.thejob = '441402/SCOTTF/COOLIO'; ---- Copy the batch jobname from the SBMJOB
select ordinal_position, message_text from table(qsys2.joblog_info(coolstuff.thejob));
--
-- The query will fail if the job is not active
--
-- SQL State: 42704 Vendor Code: -443 Message: [SQL0443] JOB 441402/SCOTTF/COOLIO NOT FOUND
--
stop;
--
-- Search the history to find the start and end timestamps for a specific job
--
with job_deetz (startandend) as (
select cast(listagg(MESSAGE_TIMESTAMP) WITHIN GROUP(ORDER BY MESSAGE_TIMESTAMP) as char(52))
from table (
qsys2.history_log_info(
START_TIME => current date - 2 days, END_TIME => current timestamp)
)
where FROM_JOB = coolstuff.thejob and
(message_id = 'CPF1124' or
message_id = 'CPF1164')
)
select timestamp(substr(startandend, 1, 26)) as job_start,
timestamp(substr(startandend, 27, 26)) as job_end
from job_deetz;
stop;
--
-- Search the history to find the start and end timestamps, and the user, for a specific job
--
with job_deet (startandend) as (
select cast(listagg(MESSAGE_TIMESTAMP) within group (order by MESSAGE_TIMESTAMP) as char(52))
from table (
qsys2.history_log_info(
START_TIME => current date - 2 days, END_TIME => current timestamp)
)
where FROM_JOB = coolstuff.thejob and
(message_id = 'CPF1124' or
message_id = 'CPF1164')
),
job_deetz (starting, ending) as (
select timestamp(substr(startandend, 1, 26)) as job_start,
timestamp(substr(startandend, 27, 26)) as job_end
from job_deet
)
select starting, ending, from_user as job_user
from job_deetz, table (
systools.ended_job_info(START_TIME => starting, END_TIME => ending)
)
where from_job = coolstuff.thejob;
stop;
--
-- Find the spooled file(s) from the job
--
with job_deet (startandend) as (
select cast(listagg(MESSAGE_TIMESTAMP) within group (order by MESSAGE_TIMESTAMP) as char(52))
from table (
qsys2.history_log_info(
START_TIME => current date - 2 days, END_TIME => current timestamp)
)
where FROM_JOB = coolstuff.thejob and
(message_id = 'CPF1124' or
message_id = 'CPF1164')
),
job_deetz (starting, ending) as (
select timestamp(substr(startandend, 1, 26)) as job_start,
timestamp(substr(startandend, 27, 26)) as job_end
from job_deet
),
job_and_user (starting, ending, job_user) as (
select starting, ending, from_user
from job_deetz, table (
systools.ended_job_info(START_TIME => starting, END_TIME => ending)
)
where from_job = coolstuff.thejob
)
select *
from job_and_user, table (
QSYS2.SPOOLED_FILE_INFO(
user_name => job_user,
JOB_NAME => coolstuff.thejob,
STARTING_TIMESTAMP => starting,
ENDING_TIMESTAMP => ending)
);
stop;
--
-- Find the spooled joblogs for the job
--
with job_deet (startandend) as (
select cast(listagg(MESSAGE_TIMESTAMP) within group (order by MESSAGE_TIMESTAMP) as char(52))
from table (
qsys2.history_log_info(
START_TIME => current date - 2 days, END_TIME => current timestamp)
)
where FROM_JOB = coolstuff.thejob and
(message_id = 'CPF1124' or
message_id = 'CPF1164')
),
job_deetz (starting, ending) as (
select timestamp(substr(startandend, 1, 26)) as job_start,
timestamp(substr(startandend, 27, 26)) as job_end
from job_deet
),
job_and_user (starting, ending, user_name) as (
select starting, ending, from_user
from job_deetz, table (
systools.ended_job_info(START_TIME => starting, END_TIME => ending)
)
where from_job = coolstuff.thejob
),
spool_detail (job_name, spooled_file_name, spooled_file_number) as (
select user_name, spooled_file_name, spooled_file_number
from job_and_user, table (
QSYS2.SPOOLED_FILE_INFO(
user_name => user_name,
JOB_NAME => coolstuff.thejob,
STARTING_TIMESTAMP => starting,
ENDING_TIMESTAMP => ending)
) where spooled_file_name = 'QPJOBLOG'
)
select *
from spool_detail;
stop;
--
-- Read the spooled joblogs for the job
--
with job_deet (startandend) as (
select cast(listagg(MESSAGE_TIMESTAMP) within group (order by MESSAGE_TIMESTAMP) as char(52))
from table (
qsys2.history_log_info(
START_TIME => current date - 2 days, END_TIME => current timestamp)
)
where FROM_JOB = coolstuff.thejob and
(message_id = 'CPF1124' or
message_id = 'CPF1164')
),
job_deetz (starting, ending) as (
select timestamp(substr(startandend, 1, 26)) as job_start,
timestamp(substr(startandend, 27, 26)) as job_end
from job_deet
),
job_and_user (starting, ending, user_name) as (
select starting, ending, from_user
from job_deetz, table (
systools.ended_job_info(START_TIME => starting, END_TIME => ending)
)
where from_job = coolstuff.thejob
),
spool_detail (job_name, spooled_file_name, spooled_file_number) as (
select coolstuff.thejob, spooled_file_name, spooled_file_number
from job_and_user, table (
QSYS2.SPOOLED_FILE_INFO(
user_name => user_name, JOB_NAME => coolstuff.thejob,
STARTING_TIMESTAMP => starting, ENDING_TIMESTAMP => ending)
)
where spooled_file_name = 'QPJOBLOG'
)
select *
from spool_detail, table (
SYSTOOLS.SPOOLED_FILE_DATA(
JOB_NAME => job_name, spooled_file_name => spooled_file_name,
SPOOLED_FILE_NUMBER => spooled_file_number)
)
order by spooled_file_number, ordinal_position;
stop;
--
-- Wrapper the query logic within a UDTF
-- (by default, the search for the job start and end will be from 2 days prior up to the current time... adjust as needed)
--
create or replace function coolstuff.read_joblog (
p_qualfied_job_name varchar(28),
search_start timestamp default (select current timestamp - 2 days from sysibm.sysdummy1),
search_end timestamp default current timestamp
)
returns table (
JOBLOG_DATA varchar(1024) for sbcs data,
ORDINAL_POSITION integer,
SPOOLED_FILE_NAME varchar(10) for sbcs data,
SPOOLED_FILE_NUMBER integer
)
specific coolstuff.read_joblog
modifies sql data
not deterministic
no external action
not fenced
disallow parallel
set option commit = *NONE
begin
declare error_hit integer default 0;
declare sql_stmt_text varchar(10000) for sbcs data;
begin
declare continue handler for sqlexception set error_hit = 1;
set sql_stmt_text = 'declare global temporary table joblog_deetz (
SPOOLED_FILE_NAME, SPOOLED_FILE_NUMBER, ORDINAL_POSITION, SPOOLED_DATA) as
(select cast(null as varchar(10)), cast(null as integer), ordinal_position, message_text
from table (
qsys2.joblog_info(''' concat p_qualfied_job_name concat ''')
))
with data
with replace';
execute immediate sql_stmt_text;
end;
--
-- If error_hit is equal to 1, that means that the attempt to consume the joblog as
-- an active job failed, probably because the job is no longer active.
--
if (error_hit = 1) then
set sql_stmt_text = 'declare global temporary table joblog_deetz (
SPOOLED_FILE_NAME, SPOOLED_FILE_NUMBER, ORDINAL_POSITION, SPOOLED_DATA) as
(with job_deet (startandend) as (
select cast(
listagg(MESSAGE_TIMESTAMP) within group (order by MESSAGE_TIMESTAMP) as
char(52))
from table (
qsys2.history_log_info(
START_TIME => ''' concat search_start concat ''',
END_TIME => ''' concat search_end concat ''')
)
where FROM_JOB = ''' concat p_qualfied_job_name concat ''' and
(message_id = ''CPF1124'' or
message_id = ''CPF1164'')
),
job_deetz (starting, ending) as (
select timestamp(substr(startandend, 1, 26)) as job_start,
timestamp(substr(startandend, 27, 26)) as job_end
from job_deet
),
job_and_user (starting, ending, user_name) as (
select starting, ending, from_user
from job_deetz, table (
systools.ended_job_info(START_TIME => starting, END_TIME => ending)
)
where from_job = ''' concat p_qualfied_job_name concat '''
),
spool_detail (job_name, spooled_file_name, spooled_file_number) as (
select QUALIFIED_JOB_NAME, spooled_file_name, spooled_file_number
from job_and_user, table (
QSYS2.SPOOLED_FILE_INFO(
user_name => user_name, JOB_NAME => ''' concat p_qualfied_job_name concat ''',
STARTING_TIMESTAMP => starting, ENDING_TIMESTAMP => ending)
)
where spooled_file_name = ''QPJOBLOG''
)
select spooled_file_name, spooled_file_number, ordinal_position, spooled_data
from spool_detail, table (
SYSTOOLS.SPOOLED_FILE_DATA(
JOB_NAME => job_name, spooled_file_name => spooled_file_name,
SPOOLED_FILE_NUMBER => spooled_file_number)
)
order by spooled_file_number, ordinal_position)
with data
with replace';
execute immediate sql_stmt_text;
end if;
return select SPOOLED_DATA, ORDINAL_POSITION, SPOOLED_FILE_NAME, SPOOLED_FILE_NUMBER
from session.joblog_deetz;
end;
stop;
--
-- Test reading the joblog of an active job (the job for this connection)
--
select * from table(coolstuff.read_joblog(qsys2.job_name));
stop;
--
-- Test reading the joblog of a completed job (the batch job we executed at the top of this script)
--
select * from table(coolstuff.read_joblog(coolstuff.thejob));
stop;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment