Skip to content

Instantly share code, notes, and snippets.

@forstie
Last active June 14, 2024 20:38
Show Gist options
  • Save forstie/0ac5590bc5827bcd948b2ef0c246bf14 to your computer and use it in GitHub Desktop.
Save forstie/0ac5590bc5827bcd948b2ef0c246bf14 to your computer and use it in GitHub Desktop.
When a job is started, the CPF1124 message is sent to the history log. When the job ends, the CPF1164 message is sent to the history log. With SQL built-in functions, this example shows how SQL can extract and transform the CPF1164 job end message tokens into a useful form.
--
-- author: Scott Forstie
-- date : November 1, 2020
-- updated: November 3, 2020
-- updated: October 1, 2021
-- title : Job End and the CPF1164 message
-- When a job is started, the CPF1124 message is sent to the history log. When the job end, the CPF1164 message is sent to the history log.
-- With SQL built-in functions, this example shows how SQL can extract and transform the CPF1164 job end message tokens into a useful form.
-- minvrm: V7R3M0
--
-- Enabling PTF Group: SF99704 Level 7 & SF99703 Level 18 (or higher)
-- Interpret: https://www.ibm.com/support/pages/node/6172749
--
create or replace function systools.job_end_info (
start_time timestamp default (current_date - 1 day), end_time timestamp default '9999-12-30-00.00.00.000000'
)
returns table (
ordinal_position integer,
job_end timestamp,
user_name varchar(10) for sbcs data,
job_name varchar(28) for sbcs data,
job_ifc varchar(20) for sbcs data,
from_program varchar(10) for sbcs data,
cpu_time integer,
number_of_steps smallint,
job_end_code smallint,
job_end_detail varchar(100) for sbcs data,
secondary_ending_code smallint,
secondary_ending_code_detail varchar(100) for sbcs data,
cpu_time_precise decimal(15, 3),
job_entry timestamp(0),
job_start timestamp(0),
total_response_time integer,
transaction_count integer,
sync_aux_io_count integer,
job_type varchar(12) for sbcs data,
peak_temp_storage integer
)
specific systools.jobendinfo
not deterministic
not fenced
no external action
set option usrprf = *user, dynusrprf = *user
return
select ordinal_position, message_timestamp, from_user, from_job,
CASE interpret(substr(message_tokens, 1, 5) as char(5) ccsid 37)
WHEN 'QPADE' THEN 'TELNET/5250'
WHEN 'QZDAS' THEN 'ODBC or File TXFR'
WHEN 'QRWTS' THEN 'DDM / DRDA'
WHEN 'QTFTP' THEN 'FTP'
WHEN 'QTRXC' THEN 'REXEC'
WHEN 'QNFTP' THEN 'SNDNETxxx'
WHEN 'QNPSE' THEN 'Print Server'
WHEN 'QZSCS' THEN 'Central Server'
WHEN 'QZHQS' THEN 'Data Queue Server'
WHEN 'QPWFS' THEN 'File Server'
WHEN 'QP0ZS' THEN 'SSH Connection'
WHEN 'QZSOS' THEN 'SIGNON Server'
WHEN 'QZLSF' THEN 'NetServer File Share'
WHEN 'QSQSR' THEN 'CLI DB Connection'
WHEN 'QZRCS' THEN 'Remote Command'
ELSE 'Other'
END AS job_ifc,
from_program,
interpret(substr(message_tokens, 43, 4) as integer) as cpu_time,
interpret(substr(message_tokens, 47, 2) as smallint) as number_of_steps,
interpret(substr(message_tokens, 49, 2) as smallint) as job_end_code,
case interpret(substr(message_tokens, 49, 2) as smallint)
when 0 then 'The job completed normally'
when 10 then 'The job completed normally during controlled ending or controlled subsystem ending'
when 20 then 'The job exceeded end severity (ENDSEV job attribute)'
when 30 then 'The job ended abnormally'
when 40 then 'The job ended before becoming active'
when 50 then 'The job ended while the job was active'
when 60 then 'The subsystem ended abnormally while the job was active'
when 70 then 'The system ended abnormally while the job was active'
when 80 then 'The job ended (ENDJOBABN command)'
when 90 then 'The job was forced to end after the time limit ended (ENDJOBABN command)'
end as job_end_detail,
interpret(substr(message_tokens, 51, 2) as smallint) as secondary_ending_code,
case interpret(substr(message_tokens, 51, 2) as smallint)
when 0 then 'No secondary ending code'
when 100 then 'Disconnect time interval exceeded'
when 101 then 'Session device deleted'
when 102 then 'Error calling Disconnect Job (DSCJOB)'
when 300 then 'Device error and DEVRCYACN set to *ENDJOB'
when 301 then 'Job ended due to looping on device errors'
end as secondary_ending_code_detail,
interpret(substr(message_tokens, 53, 8) as decimal(15, 3)) as cpu_time_precise,
case
when hex(substr(message_tokens, 81, 1)) = '00' then null
else
timestamp_format(
interpret(substr(message_tokens, 81, 8) as char(8) ccsid 37) concat ' ' concat
interpret(substr(message_tokens, 73, 8) as char(8) ccsid 37), 'MM/DD/YY HH24:MI:SS')
end as job_entry,
case
when hex(substr(message_tokens, 97, 1)) = '00' then null
else
timestamp_format(
interpret(substr(message_tokens, 97, 8) as char(8) ccsid 37) concat ' ' concat
interpret(substr(message_tokens, 89, 8) as char(8) ccsid 37), 'MM/DD/YY HH24:MI:SS')
end as job_start,
interpret(substr(message_tokens, 105, 4) as integer) as total_response_time,
interpret(substr(message_tokens, 109, 4) as integer) as transaction_count,
interpret(substr(message_tokens, 113, 4) as integer) as sync_aux_io_count,
case interpret(substr(message_tokens, 117, 1) as char(1) ccsid 37)
when 'B' then 'Batch'
when 'I' then 'Interactive'
when 'M' then 'Subsystem'
when 'A' then 'Auto-start'
when 'R' then 'Spool Reader'
when 'W' then 'Spool Writer'
when 'X' then 'Start CPF'
when 'S' then 'System'
else interpret(substr(message_tokens, 117, 1) as char(1) ccsid 37)
end as job_type,
interpret(substr(message_tokens, 284, 4) as integer) as peak_temp_storage
from table (
qsys2.history_log_info(start_time => start_time, end_time => end_time)
)
where message_id = 'CPF1164';
stop;
--
-- View all CPF1164 messages, from today and yesterday
--
select *
from table (
systools.job_end_info()
);
stop;
--
-- View all CPF1164 messages, from today and yesterday
--
select *
from table (
systools.job_end_info(start_time => current date - 1 day, end_time => current timestamp )
);
stop;
--
-- View all CPF1164 messages, from October, 2020
--
select *
from table (
systools.job_end_info(start_time => '2020-10-01 00:00:00.000000', end_time => '2020-10-31 23:59:59.000000')
);
stop;
--
-- Count the ways jobs ended, from today and yesterday
--
select job_end_detail, count(*) as job_end_count
from table (
systools.job_end_info()
)
group by job_end_detail
order by 2 desc;
stop;
--
-- top 10 CPU consumers, from today and yesterday
--
select user_name, job_name, cpu_time, cpu_time_precise, job_start
from table (
systools.job_end_info()
)
order by cpu_time_precise desc
limit 10;
stop;
--
-- top 10 peak temp storage consumers, from today and yesterday
--
select user_name, job_name, peak_temp_storage, job_start
from table (
systools.job_end_info()
)
order by peak_temp_storage desc
limit 10;
stop;
--
-- top 10 transaction counts, from today and yesterday
--
select user_name, job_name, transaction_count, job_start
from table (
systools.job_end_info()
)
order by transaction_count desc
limit 10;
stop;
--
-- top 10 synchronous auxiliary I/O counts, from today and yesterday
--
select user_name, job_name, sync_aux_io_count, job_start
from table (
systools.job_end_info()
)
order by sync_aux_io_count desc
limit 10;
stop;
--
-- top 10 longest run jobs, from today and yesterday
--
select user_name, job_name, sjob_start, job_end
from table (
systools.job_end_info()
)
order by job_end - job_start desc
limit 10;
stop;
--
-- Breakdown by day, by job type
--
select date(job_end) as job_end_date, job_type, count(*) as job_type_count
from table (
systools.job_end_info(start_time => current date - 7 days, end_time => current timestamp )
)
group by date(job_end), job_type
order by 1, 3 desc;
stop;
@bdietz400
Copy link

I like this.
One addition would be calculation of wall clock time in seconds.
eg.
cast((job_end - job_start) as decimal(15, 3)) as clock_time_seconds

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment