Skip to content

Instantly share code, notes, and snippets.

@forstie
Created May 29, 2022 12:58
Show Gist options
  • Save forstie/6aad7d22a0869239f8385c8ebb90b228 to your computer and use it in GitHub Desktop.
Save forstie/6aad7d22a0869239f8385c8ebb90b228 to your computer and use it in GitHub Desktop.
The request... provide a way to alert when the number of jobs is growing to a concerning level. The solution follows...
--
-- Subject: The request... provide a way to alert when the number of jobs is growing to a concerning level. The solution follows...
-- Author: Scott Forstie
-- Date : May, 2022
-- Features Used : This Gist uses system_value_info, CTEs, syslimits_basic, override_qaqqini, MESSAGE_QUEUE_INFO, SEND_MESSAGE, and more
--
--
-- System limit tracking provides IBM i health insight:
-- ================================================================
-- 1) Recognize when the IBM i is trending towards an outage or serious condition
-- 2) Identify a run-away job that is endlessly consuming an operating system resource
--
-- See these resources for a detailed explanation of System Limits:
-- ================================================================
-- https://www.ibm.com/support/pages/node/1136884
-- https://www.ibm.com/docs/en/i/7.4?topic=services-system-health
--
-- For System Limits, some database limits include automatic alert-style messages to QSYSOPR using message identifier SQL7064
-- Db2 for i Global Variables can be adjusted to cause the alert messages to appear earlier or closer to hitting the architectural limit
--
-- See this resource for a detailed explanation of System Limits alerting:
-- =======================================================================
-- https://www.ibm.com/support/pages/node/1116795
--
-- For those topics not covered by Db2 for i alerts, end users can establish their own alerts, using a trigger over QSYS2/SYSLIMTBL
-- Clients can call QSYS2.SEND_MESSAGE() to send an alert message, using either SQL7064 or their own message identifier
--
-- For Maximum number of jobs (limit ID: 19000), the default maximum is 970,000 (IBM i 7.2 and up)
-- The value of the QMAXJOB system value takes precedence over the IBM i architectural maximum
-- The QMAXJOB default is 163520, but can be set to 32000 through 970000
--
-- Limit ID 19000 will insert rows into SYSLIMTBL when the number of active jobs reaches 1,000
-- Subsequent rows are inserted with intervals of every 400 additional jobs
--
stop;
--
-- Note: The maximum number of jobs can be lowered via the QMAXJOB system value
--
select current_numeric_value as max_jobs
from qsys2.system_value_info
where system_value_name = 'QMAXJOB';
stop;
--
-- Show me the historical percentage used for Maximum # of Jobs
--
-- https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/rzajq/rzajqserviceshealth.htm
--
with tt (job_maximum) as (
select current_numeric_value
from qsys2.system_value_info
where system_value_name = 'QMAXJOB'
)
select last_change_timestamp as increment_time, current_value as job_count,
tt.job_maximum,
dec(dec(current_value, 19, 2) / dec(tt.job_maximum, 19, 2) * 100, 19, 2)
as percent_consumed
from qsys2.syslimits_basic, tt
where limit_id = 19000
order by Increment_time desc;
stop;
--
-- Deploy a trigger program without trying to acquire an exclusive lock
--
call qsys2.override_qaqqini(1, '', '');
call qsys2.override_qaqqini(2, 'ALLOW_DDL_CHANGES_WHILE_OPEN', '*YES');
--
-- Send an alert if the number of jobs exceeds 50% of the maximum
--
create or replace trigger qsys2.system_limits_large_file
after insert on qsys2.syslimtbl
referencing new as n for each row mode db2row
set option usrprf = *owner, dynusrprf = *owner
begin atomic
declare v_msg varchar(2000) for sbcs data;
declare v_error integer;
declare percent_consumed decimal(4, 2);
declare exit handler for sqlexception set v_error = 1;
if (n.limit_id = 19000) then
set percent_consumed = (with tt (job_maximum) as (
select current_numeric_value
from qsys2.system_value_info
where system_value_name = 'QMAXJOB'
) select dec(dec(n.current_value, 19, 2) / dec(tt.job_maximum, 19, 2) * 100, 19, 2)
from tt);
-- If the value is larger than 50 percent, send an alert message
if (percent_consumed > 50.00) then
set v_msg = 'System Limit for QMAXJOB exceeded 50%: ' concat percent_consumed concat '% consumed';
execute immediate 'call QSYS2.SEND_MESSAGE(''SQL7064'', length(''' concat v_msg concat '''), ''' concat v_msg concat ''')';
end if;
end if;
end;
call qsys2.override_qaqqini(3, '', '');
stop;
--
-- Review any instances where SQL7064 has been sent to the QSYS/QSYSOPR message queue
--
select *
from table (
QSYS2.MESSAGE_QUEUE_INFO(MESSAGE_FILTER => 'ALL', SEVERITY_FILTER => 80)
)
where message_id = 'SQL7064'
order by MESSAGE_TIMESTAMP desc;
stop;
--
-- What if you don't want to see an alert message every time 400 jobs are added or removed, once your alerting level it eclipsed?
-- You can establish your own pace of alert messages
--
--
-- Send an alert if the number of jobs exceeds 50% of the maximum (once per day)
--
call qsys2.override_qaqqini(1, '', '');
call qsys2.override_qaqqini(2, 'ALLOW_DDL_CHANGES_WHILE_OPEN', '*YES');
create or replace trigger qsys2.system_limits_large_file
after insert on qsys2.syslimtbl
referencing new as n for each row mode db2row
set option usrprf = *owner, dynusrprf = *owner
begin atomic
declare v_msg varchar(2000) for sbcs data;
declare v_error integer;
declare msg_count bigint;
declare percent_consumed decimal(4, 2);
declare exit handler for sqlexception set v_error = 1;
if (n.limit_id = 19000) then
set msg_count = (select count(*)
from table (
QSYS2.MESSAGE_QUEUE_INFO(MESSAGE_FILTER => 'ALL')
)
where message_id = 'SQL7064' and
message_text like '%QMAXJOB%' and
date(message_timestamp) = current date);
if (msg_count = 0) then
set percent_consumed = (with tt (job_maximum) as (
select current_numeric_value
from qsys2.system_value_info
where system_value_name = 'QMAXJOB'
) select dec(dec(n.current_value, 19, 2) / dec(tt.job_maximum, 19, 2) * 100, 19, 2)
from tt);
-- If the value is larger than 50 percent, send an alert message
if (percent_consumed > 50.00) then
set v_msg = 'System Limit for QMAXJOB exceeded 50%: ' concat percent_consumed concat
'% consumed';
execute immediate 'call QSYS2.SEND_MESSAGE(''SQL7064'', length(''' concat v_msg concat
'''), ''' concat v_msg concat ''')';
end if;
end if;
end if;
end;
call qsys2.override_qaqqini(3, '', '');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment