The request... show how Query Supervisor could be used to HOLD a job. The criteria for which situations merit a job being held are left to the reader. The example shows how QS could react to a long running query issued by an interactive user.

Query Supervisor - Holding a job.sql

--
-- Subject: Query Supervisor - Holding a job
-- Author: Scott Forstie
-- Date  : April, 2023
-- Features Used : This Gist uses qsys2.query_supervisor, qsys2.EXIT_PROGRAM_INFO, aliases, qsys2.qcmdexc, qsys2.ADD_QUERY_THRESHOLD,  qsys2.REMOVE_QUERY_THRESHOLD, qsys2.joblog_info, and QSYS2.ACTIVE_JOB_INFO
--
-- Notes:
-- ===============================================
-- 1) Query Supervisor (QS) exists in IBM i 7.3 and higher
--    (SF99703 Level 24, SF99704 Level 13, SF99950 <any>) 	
-- 2) Query Supervisor resources are found here:
--    https://www.ibm.com/support/pages/node/6442049
-- 3) IBM has example QS exit programs written in ILE RPG, ILE C, or CL
--    https://www.ibm.com/docs/en/i/7.4?topic=supervisor-query-example-exit-programs
--
stop;

--
-- Review configured Query Supervisor thresholds
--
select *
  from qsys2.query_supervisor;
stop;  

--
-- Try to hold the job that is my current connection
--
call qsys2.qcmdexc('HLDJOB JOB(' concat qsys2.job_name concat ')');

-- SQL State: 38501 
-- Vendor Code: -443 Message: 
-- [CPF1342] Current job not allowed as job name on this command.
stop;

--
-- Find the Query Supervisor header file (ILE C)
--
create or replace alias qtemp.qs_header for qsysinc.h(eqqqrysv);
stop;

--
-- Review the Query Supervisor header file 
--
select * from qtemp.qs_header;
stop;

--
-- This example shows how to establish a Query Supervisor threshold
-- that is holding jobs.
--
stop;

call qsys2.qcmdexc('CRTSRCPF FILE(QTEMP/QS_HOLD) RCDLEN(140)');
call qsys2.qcmdexc('addpfm file(qtemp/QS_HOLD) mbr(QS_HOLD)');
insert into qtemp.QS_HOLD
  values
 (1, 010101, '#include <stdlib.h>'),
 (2, 010101, '#include <string.h>'),
 (3, 010101, '#include <stddef.h> '),
 (4, 010101, '#include <iconv.h>'),
 (5, 010101, '#include <stdio.h>'),
 (6, 010101, '#include <except.h>'), 
 (7, 010101, '#include <eqqqrysv.h>'),
 (8, 010101, 'static void convertThresholdNameToJobCCSID(const char* input, char* output)'),
 (9, 010101, '{'),
 (10,010101, '  iconv_t converter;'),
 (11,010101, '  char from_code[32], to_code[32];'),
 (12,010101, '  size_t input_bytes, output_bytes;'),
 (13,010101, '  int iconv_rc;'),
 (14,010101, '  memset(from_code, 0, sizeof(from_code));'),
 (15,010101, '  memset(to_code, 0, sizeof(to_code));'),
 (16,010101, '  memcpy(from_code, "IBMCCSID012000000000", 20);'),
 (17,010101, '  memcpy(to_code, "IBMCCSID00000", 13);'),
 (18,010101, '  converter = iconv_open(to_code, from_code);'),
 (19,010101, '  if (converter.return_value == 0) {'),
 (20,010101, '    input_bytes = 60;'),
 (21,010101, '   output_bytes = 30;'),
 (22,010101, '    iconv_rc = iconv(converter,'),
 (23,010101, '                     &input, &input_bytes,'),
 (24,010101, '                     &output, &output_bytes);'),
 (25,010101, '    iconv_close(converter);'),
 (26,010101, '    if (iconv_rc >= 0)'),
 (27,010101, '      return; /* Conversion was successful. */'),
 (28,010101, '  }'),
 (29,010101, '  sprintf(output, "iconv_open() failed with: %d", converter.return_value);'),
 (30,010101, '}'),
 (31,010101, 'int trimmed_length(const char* str, int len)'),
 (32,010101, '{'),
 (33,010101, '  const char* first_blank = memchr(str, '' '', len);'),
 (34,010101, '  if (first_blank)'),
 (35,010101, '    return first_blank - str;'),
 (36,010101, '  return len;'),
 (37,010101, '}'),
 (38,010101, 'int main(int argc, char* argv[])'),
 (39,010101, '{'),
 (40,010101, '  char length_string[10];'),
 (41,010101, '  char cmd[600];'),
 (42,010101, '  char thresholdNameInJobCCSID[31];'),
 (43,010101, '  char msg[512];'),
 (44,010101, '  const QQQ_QRYSV_QRYS0100_t* input = (QQQ_QRYSV_QRYS0100_t*)argv[1];'),
 (45,010101, '  int* rc = (int*)argv[2];'),
 (46,010101, '  memset(thresholdNameInJobCCSID, 0, sizeof(thresholdNameInJobCCSID));'),
 (47,010101, '  convertThresholdNameToJobCCSID(input->Threshold_Name,thresholdNameInJobCCSID);'),
 (48,010101, '  if (memcmp("TIM USING STRSQL", thresholdNameInJobCCSID, 16) == 0) '),
 (49,010101, '    { return; } '),
 (50,010101, '  memset(cmd, 0, sizeof(cmd));'),
 (51,010101, '  strcat(cmd, "SBMJOB CMD(HLDJOB JOB(");'),
 (52,010101, '  strncat(cmd, input->Job_Number, trimmed_length(input->Job_Number,6));'),
 (53,010101, '  strcat(cmd, "/");'),
 (54,010101, '  strncat(cmd, input->Job_User, trimmed_length(input->Job_User,10));'),
 (55,010101, '  strcat(cmd, "/");'),
 (56,010101, '  strncat(cmd, input->Job_Name, trimmed_length(input->Job_Name,10));'),
 (57,010101, '  strcat(cmd, "))");'),
 (58,010101, '  system(cmd);'),
 (59,010101, '}');

call qsys2.qcmdexc('CRTCMOD MODULE(QTEMP/QS_HOLD) SRCFILE(QTEMP/QS_HOLD)  OUTPUT(*print)  ');
call qsys2.qcmdexc('CRTPGM PGM(SUPERVISOR/QS_HOLD) MODULE(QTEMP/QS_HOLD) ACTGRP(*CALLER) USRPRF(*OWNER) DETAIL(*NONE)');
 
call qsys2.qcmdexc('ADDEXITPGM EXITPNT(QIBM_QQQ_QRY_SUPER) FORMAT(QRYS0100) PGMNBR(*LOW) PGM(SUPERVISOR/QS_HOLD) THDSAFE(*YES) TEXT(''TIM USING STRSQL'')') ;
stop;

--
-- Remove a Query Supervisor threshold 
--
CALL QSYS2.REMOVE_QUERY_THRESHOLD(THRESHOLD_NAME  => 'TIM USING STRSQL');
stop;

--
-- Add a threshold for elapsed time of queries coming in over QZDA jobs
--
CALL QSYS2.ADD_QUERY_THRESHOLD(THRESHOLD_NAME  => 'TIM USING STRSQL',
                               THRESHOLD_TYPE  => 'ELAPSED TIME',
                               THRESHOLD_VALUE => 3,
                               SUBSYSTEMS      => 'QINTER', 
                               INCLUDE_USERS   => 'TIMMR',
                               LONG_COMMENT    => 'STRSQL Queries from Tim running longer than 3 seconds');
stop;
--
-- Review configured Query Supervisor thresholds
--
select *
  from qsys2.query_supervisor;
stop; 

--
-- Review the Query Supervisor exit programs
--  
select *
  from QSYS2.EXIT_PROGRAM_INFO where EXIT_POINT_NAME = 'QIBM_QQQ_QRY_SUPER';
stop;

--
-- Ok then, we're all set... ask Tim to use STRSQL!
--

stop;
--
-- Which jobs are being held right now?
--
SELECT  J.ELAPSED_TIME, J.JOB_ACTIVE_TIME, j.*
FROM TABLE(QSYS2.ACTIVE_JOB_INFO(detailed_info => 'NONE')) J
where job_status = 'HLD';
stop;

--
-- Lets see the joblogs of the held jobs
--
with hjs (jn) as (
    select job_name
      from table (
          QSYS2.ACTIVE_JOB_INFO(detailed_info => 'NONE')
        ) J
      where job_status = 'HLD'
  )
  select jn, jl.message_text, jl.*
    from hjs, lateral (
           select *
             from table (
                 qsys2.joblog_info(hjs.jn)
               )
         ) jl order by message_timestamp asc;
stop;

 

Thank you so much Scott. This really helped. Appreciate it.

Santosh Yadav

You're very welcome. Thanks a lot for the feedback.