The request... provide a way to alert when the number of jobs is growing to a concerning level. The solution follows...

alerting on high levels of jobs.sql

--
-- 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, '', '');