More SQL than you want.... or so much fine SQL that you keep coming back? Time will tell.

Kitchen Sink for the Admin.sql

-- 
-- ======================================================================
-- iSee - Using SQL to Manage IBM i
-- ======================================================================
--
-- ======================================================================
-- Scott Forstie
-- forstie@us.ibm.com
-- Date: November, 2020
-- Disclaimer: These examples are provided without warranty, yada yada
-- ======================================================================
--
--
stop;

-- ======================================================================
-- ======================================================================
-- ======================================================================

cl: crtlib coolstuff;            
 
set schema coolstuff;             
set path coolstuff, system path; 

stop;


-- ======================================================================
-- ======================================================================
--
-- description: Unused objects
--
-- Beware: last_used_timestamp is not updated for all object types.
-- https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_73/rbam6/detob.htm
-- 
--
-- definition: Find objects created > 6 months ago that have
--             not been used or changed in the last 6 months
-- ======================================================================
-- ======================================================================
-- ======================================================================

--
-- description: Find objects not used within a specified timeframe
--

-- ======================================================================
-- ======================================================================
stop;
select * from 
  table(qsys2.object_statistics('QUSRSYS', '*PGM *SRVPGM *FILE *USRSPC *DTAARA'))
  where (last_used_timestamp is NULL or 
         last_used_timestamp < current timestamp - 1 months) and
        (change_timestamp    < current timestamp - 1 months);

stop;

select * from 
  table(qsys2.object_statistics('QUSRSYS', '*PGM *SRVPGM *FILE *USRSPC *DTAARA'))
  where (last_used_timestamp is NULL or 
         last_used_timestamp < current timestamp - 1 months) and
        (change_timestamp    < current timestamp - 1 months)       
  order by objsize desc;
        
stop;

select varchar_format(objsize, '999G999G999G999G999G999G999G999') as obj_size, 
       objs.* from 
  table(qsys2.object_statistics('QUSRSYS', '*PGM *SRVPGM *FILE *USRSPC *DTAARA')) objs
  where (last_used_timestamp is NULL or 
         last_used_timestamp < current timestamp - 1 months) and
        (change_timestamp    < current timestamp - 1 months)       
  order by objsize desc;
        

stop;        
        
--       formatter time
create table Nov10objs as (select varchar_format(objsize, '999G999G999G999G999G999G999G999') as obj_size, objs.* from 
table(qsys2.object_statistics('QUSRSYS', '*PGM *SRVPGM *FILE *USRSPC *DTAARA')) objs
where (last_used_timestamp is NULL or last_used_timestamp < current timestamp - 1 months) and
(change_timestamp    < current timestamp - 1 months)  order by objsize desc) with data;
           
select * from Nov10objs;
stop;


--
-- description: Find save file objects within QGPL, and order by size descending
--
select a.iasp_name, a.iasp_number, objname, objsize, max(objcreated, change_timestamp) as change_timestamp, last_used_timestamp, objowner, objdefiner 
  from table (
      qsys2.object_statistics(object_schema => 'QGPL', objtypelist => '*FILE')
    ) a
    where objattribute = 'SAVF'
    order by objsize desc;
stop;

-- ======================================================================
-- ======================================================================

--
-- Generate the column list for a UDTF!
--

-- ======================================================================
-- ======================================================================

select * from qsys2.sysroutine where routine_name = 'OBJECT_STATISTICS';
select * from qsys2.sysparms c
  where specific_schema = 'QSYS2' and
        specific_name = (select specific_name from qsys2.sysroutine 
                       where routine_name = 'OBJECT_STATISTICS' and routine_schema = 'QSYS2');
stop;                      

-- debug what's wrong here                       
select
  listagg(cast(qsys2.delimit_name(parameter_name) as clob(1m)), ', ') within group (order by
  ordinal_position) as parameter_list
  from qsys2.sysparms c
  where specific_schema = 'QSYS2' and
        specific_name = (select specific_name from qsys2.sysroutine 
                       where routine_name = 'OBJECT_STATISTICS' and routine_schema = 'QSYS2')
         parameter_mode = 'OUT'; -- Don't include input parameters (doh)

stop;

select
  listagg(cast(qsys2.delimit_name(parameter_name) as clob(1m)), ', ') within group (order by
  ordinal_position) as parameter_list
  from qsys2.sysparms c
  where specific_schema = 'QSYS2' and
        specific_name = (select specific_name from qsys2.sysroutine 
                       where routine_name = 'OBJECT_STATISTICS' and routine_schema = 'QSYS2') and
         parameter_mode = 'OUT'; -- Don't include input parameters (doh)
         
         
stop;

-- how do we overcome this?

create table Nov10objs as
      (select varchar_format(objsize, '999G999G999G999G999G999G999G999') as obj_size,
              objname, objtype, objowner, objdefiner, objcreated, objsize, objtext,
              objlongname, last_used_timestamp, days_used_count, last_reset_timestamp,
              iasp_number, objattribute, objlongschema, text, sql_object_type, objlib,
              change_timestamp, user_changed, source_file, source_library, source_member,
              source_timestamp, created_system, created_system_version, licensed_program,
              licensed_program_version, compiler, compiler_version,
              user_defined_attribute, compressed, object_domain, object_audit,
              object_signed, system_trusted_source, multiple_signatures, save_timestamp,
              restore_timestamp, save_while_active_timestamp, save_command, save_device,
              save_file_name, save_file_library, save_volume, save_label,
              save_sequence_number, last_save_size, journaled, journal_name,
              journal_library, journal_images, omit_journal_entry, remote_journal_filter,
              journal_start_timestamp, apply_starting_receiver,
              apply_starting_receiver_library
          from table (
              qsys2.object_statistics('QUSRSYS', '*PGM *SRVPGM *FILE *USRSPC *DTAARA')
            ) objs
          where (last_used_timestamp is null or
                  last_used_timestamp < current timestamp - 1 months) and
                (change_timestamp < current timestamp - 1 months)
          order by objsize desc)
      with data;        
      
stop;


create or replace table Nov10objs as
      (select varchar_format(objsize, '999G999G999G999G999G999G999G999') as obj_size,
              objname, objtype, objowner, objdefiner, objcreated, objsize, objtext,
              objlongname, last_used_timestamp, days_used_count, last_reset_timestamp,
              iasp_number, objattribute, objlongschema, text, sql_object_type, objlib,
              change_timestamp, user_changed, source_file, source_library, source_member,
              source_timestamp, created_system, created_system_version, licensed_program,
              licensed_program_version, compiler, compiler_version,
              user_defined_attribute, compressed, object_domain, object_audit,
              object_signed, system_trusted_source, multiple_signatures, save_timestamp,
              restore_timestamp, save_while_active_timestamp, save_command, save_device,
              save_file_name, save_file_library, save_volume, save_label,
              save_sequence_number, last_save_size, journaled, journal_name,
              journal_library, journal_images, omit_journal_entry, remote_journal_filter,
              journal_start_timestamp, apply_starting_receiver,
              apply_starting_receiver_library
          from table (
              qsys2.object_statistics('QUSRSYS', '*PGM *SRVPGM *FILE *USRSPC *DTAARA')
            ) objs
          where (last_used_timestamp is null or
                  last_used_timestamp < current timestamp - 1 months) and
                (change_timestamp < current timestamp - 1 months)
          order by objsize desc)
      with data on replace delete rows;   
      
           
stop;

-- default ownership
select * from Nov10objs where objowner = 'QDFTOWN';

stop;

-- journal insight
select journaled, count(*) as journaled_count from Nov10objs 
  group by journaled
  order by 2 desc;
  
-- transformative grouping
select     journal_library concat '/' concat journal_name, 
           count(*) as journal_count 
  from Nov10objs 
  group by journal_library concat '/' concat journal_name
  order by 2 desc;
  

--
-- description: Which files in a library, are not being journaled?
--
SELECT *
   FROM TABLE (
         QSYS2.OBJECT_STATISTICS('TOYSTORE', '*ALL')
      ) X
   WHERE JOURNAL_LIBRARY IS NULL AND
         OBJTYPE = '*FILE'
   ORDER BY OBJNAME ASC;
   

stop;
--
-- Multiple libraries
--
-- Dawn's libraries  :-)
--
select *
  from table (
      qsys2.object_statistics('QSYS      ', '*LIB')
    ) libs
  where libs.objdefiner = 'DAWNM';
stop;

-- not quite there yet
create or replace table Nov10objs as
      (select varchar_format(objsize, '999G999G999G999G999G999G999G999') as obj_size,
              objname, objtype, objowner, objdefiner, objcreated, objsize, objtext,
              objlongname, last_used_timestamp, days_used_count, last_reset_timestamp,
              iasp_number, objattribute, objlongschema, text, sql_object_type, objlib,
              change_timestamp, user_changed, source_file, source_library, source_member,
              source_timestamp, created_system, created_system_version, licensed_program,
              licensed_program_version, compiler, compiler_version,
              user_defined_attribute, compressed, object_domain, object_audit,
              object_signed, system_trusted_source, multiple_signatures, save_timestamp,
              restore_timestamp, save_while_active_timestamp, save_command, save_device,
              save_file_name, save_file_library, save_volume, save_label,
              save_sequence_number, last_save_size, journaled, journal_name,
              journal_library, journal_images, omit_journal_entry, remote_journal_filter,
              journal_start_timestamp, apply_starting_receiver,
              apply_starting_receiver_library
          from table (
                 qsys2.object_statistics('QSYS      ', '*LIB')
               ) libs, lateral (
                 select *
                   from table (
                       qsys2.object_statistics(
                         libs.objname, '*PGM *SRVPGM *FILE *USRSPC *DTAARA')
                     ) x
               ) objs
          where libs.objdefiner = 'DAWNM' and
                (last_used_timestamp is null or
                  last_used_timestamp < current timestamp - 1 months) and
                (change_timestamp < current timestamp - 1 months)
          order by objsize desc)
      with data
  on replace delete rows;        
  
  
stop;

-- improve the select list
select
  listagg(cast('objs.' concat qsys2.delimit_name(parameter_name) as clob(1m)), ', ') within group (order by
  ordinal_position) as parameter_list
  from qsys2.sysparms c
  where specific_schema = 'QSYS2' and
        specific_name = (select specific_name from qsys2.sysroutine 
                       where routine_name = 'OBJECT_STATISTICS' and routine_schema = 'QSYS2') and
         parameter_mode = 'OUT'; -- Don't include input parameters (doh)
         
stop;


create or replace table Nov10objs as
      (select varchar_format(objs.objsize, '999G999G999G999G999G999G999G999') as obj_size,
              objs.objname, objs.objtype, objs.objowner, objs.objdefiner, objs.objcreated,
              objs.objsize, objs.objtext, objs.objlongname, objs.last_used_timestamp,
              objs.days_used_count, objs.last_reset_timestamp, objs.iasp_number,
              objs.objattribute, objs.objlongschema, objs.text, objs.sql_object_type,
              objs.objlib, objs.change_timestamp, objs.user_changed, objs.source_file,
              objs.source_library, objs.source_member, objs.source_timestamp,
              objs.created_system, objs.created_system_version, objs.licensed_program,
              objs.licensed_program_version, objs.compiler, objs.compiler_version,
              objs.user_defined_attribute, objs.compressed, objs.object_domain,
              objs.object_audit, objs.object_signed, objs.system_trusted_source,
              objs.multiple_signatures, objs.save_timestamp, objs.restore_timestamp,
              objs.save_while_active_timestamp, objs.save_command, objs.save_device,
              objs.save_file_name, objs.save_file_library, objs.save_volume,
              objs.save_label, objs.save_sequence_number, objs.last_save_size,
              objs.journaled, objs.journal_name, objs.journal_library,
              objs.journal_images, objs.omit_journal_entry, objs.remote_journal_filter,
              objs.journal_start_timestamp, objs.apply_starting_receiver,
              objs.apply_starting_receiver_library
          from table (
                 qsys2.object_statistics('QSYS      ', '*LIB')
               ) libs, lateral (
                 select *
                   from table (
                       qsys2.object_statistics(
                         libs.objname, '*PGM *SRVPGM *FILE *USRSPC *DTAARA')
                     ) x
               ) objs
          where libs.objdefiner = 'DAWNM' and
                (objs.last_used_timestamp is null or
                 objs.last_used_timestamp < current timestamp - 1 months) and
                (objs.change_timestamp < current timestamp - 1 months)
          order by objs.objsize desc)
      with data
  on replace delete rows;   
       
stop;
-- something seems to be missing...?
select * from Nov10objs;

stop;

-- defeated??? no way jose
create or replace table Nov10objs as
      (select libs.objname, objs.objname,
              varchar_format(objs.objsize, '999G999G999G999G999G999G999G999') as obj_size,
              objs.objtype, objs.objowner, objs.objdefiner, objs.objcreated, objs.objsize,
              objs.objtext, objs.objlongname, objs.last_used_timestamp,
              objs.days_used_count, objs.last_reset_timestamp, objs.iasp_number,
              objs.objattribute, objs.objlongschema, objs.text, objs.sql_object_type,
              objs.objlib, objs.change_timestamp, objs.user_changed, objs.source_file,
              objs.source_library, objs.source_member, objs.source_timestamp,
              objs.created_system, objs.created_system_version, objs.licensed_program,
              objs.licensed_program_version, objs.compiler, objs.compiler_version,
              objs.user_defined_attribute, objs.compressed, objs.object_domain,
              objs.object_audit, objs.object_signed, objs.system_trusted_source,
              objs.multiple_signatures, objs.save_timestamp, objs.restore_timestamp,
              objs.save_while_active_timestamp, objs.save_command, objs.save_device,
              objs.save_file_name, objs.save_file_library, objs.save_volume,
              objs.save_label, objs.save_sequence_number, objs.last_save_size,
              objs.journaled, objs.journal_name, objs.journal_library,
              objs.journal_images, objs.omit_journal_entry, objs.remote_journal_filter,
              objs.journal_start_timestamp, objs.apply_starting_receiver,
              objs.apply_starting_receiver_library
          from table (
                 qsys2.object_statistics('QSYS      ', '*LIB')
               ) libs, lateral (
                 select *
                   from table (
                       qsys2.object_statistics(
                         libs.objname, '*PGM *SRVPGM *FILE *USRSPC *DTAARA')
                     ) x
               ) objs
          where libs.objdefiner = 'DAWNM' and
                (objs.last_used_timestamp is null or
                  objs.last_used_timestamp < current timestamp - 1 months) and
                (objs.change_timestamp < current timestamp - 1 months)
          order by objs.objsize desc)
      with data
  on replace delete rows;   
  
stop;


-- improve the select list
select '(lib,' concat
  listagg(cast(qsys2.delimit_name(parameter_name) as clob(1m)), ', ') within group (order by
  ordinal_position) concat ')' as column_list
  from qsys2.sysparms c
  where specific_schema = 'QSYS2' and
        specific_name = (select specific_name from qsys2.sysroutine 
                       where routine_name = 'OBJECT_STATISTICS' and routine_schema = 'QSYS2') and
         parameter_mode = 'OUT'; 

stop;


create or replace table Nov10objs (
      lib, objname, obj_size, objtype, objowner, objdefiner, objcreated, objsize, objtext,
      objlongname, last_used_timestamp, days_used_count, last_reset_timestamp,
      iasp_number, objattribute, objlongschema, text, sql_object_type, objlib,
      change_timestamp, user_changed, source_file, source_library, source_member,
      source_timestamp, created_system, created_system_version, licensed_program,
      licensed_program_version, compiler, compiler_version, user_defined_attribute,
      compressed, object_domain, object_audit, object_signed, system_trusted_source,
      multiple_signatures, save_timestamp, restore_timestamp, save_while_active_timestamp,
      save_command, save_device, save_file_name, save_file_library, save_volume,
      save_label, save_sequence_number, last_save_size, journaled, journal_name,
      journal_library, journal_images, omit_journal_entry, remote_journal_filter,
      journal_start_timestamp, apply_starting_receiver, apply_starting_receiver_library)
    as
      (select libs.objname, objs.objname,
              varchar_format(objs.objsize, '999G999G999G999G999G999G999G999') as obj_size,
              objs.objtype, objs.objowner, objs.objdefiner, objs.objcreated, objs.objsize,
              objs.objtext, objs.objlongname, objs.last_used_timestamp,
              objs.days_used_count, objs.last_reset_timestamp, objs.iasp_number,
              objs.objattribute, objs.objlongschema, objs.text, objs.sql_object_type,
              objs.objlib, objs.change_timestamp, objs.user_changed, objs.source_file,
              objs.source_library, objs.source_member, objs.source_timestamp,
              objs.created_system, objs.created_system_version, objs.licensed_program,
              objs.licensed_program_version, objs.compiler, objs.compiler_version,
              objs.user_defined_attribute, objs.compressed, objs.object_domain,
              objs.object_audit, objs.object_signed, objs.system_trusted_source,
              objs.multiple_signatures, objs.save_timestamp, objs.restore_timestamp,
              objs.save_while_active_timestamp, objs.save_command, objs.save_device,
              objs.save_file_name, objs.save_file_library, objs.save_volume,
              objs.save_label, objs.save_sequence_number, objs.last_save_size,
              objs.journaled, objs.journal_name, objs.journal_library,
              objs.journal_images, objs.omit_journal_entry, objs.remote_journal_filter,
              objs.journal_start_timestamp, objs.apply_starting_receiver,
              objs.apply_starting_receiver_library
          from table (
                 qsys2.object_statistics('QSYS      ', '*LIB')
               ) libs, lateral (
                 select *
                   from table (
                       qsys2.object_statistics(
                         libs.objname, '*PGM *SRVPGM *FILE *USRSPC *DTAARA')
                     ) x
               ) objs
          where libs.objdefiner = 'DAWNM' and
                (objs.last_used_timestamp is null or
                  objs.last_used_timestamp < current timestamp - 1 months) and
                (objs.change_timestamp < current timestamp - 1 months)
          order by objs.objsize desc)
      with data
  on replace delete rows;   
  
stop;
-- show how to save in IFS 

drop table Nov10objs;
select * from Nov10objs;

-- Use prompting ...  /home/SCOTTF/capture dawns object info.sql
cl:RUNSQLSTM;
stop;
cl:RUNSQLSTM SRCSTMF('/home/SCOTTF/capture dawns object info.sql') COMMIT(*NONE) NAMING(*SQL);
select * from Nov10objs;
stop;
drop table Nov10objs;


cl: SBMJOB CMD(RUNSQLSTM SRCSTMF('/home/SCOTTF/capture dawns object info.sql') COMMIT(*NONE) NAMING(*SQL)) JOB(DAWNSPACE) INLASPGRP(*CURRENT) JOBMSGQFL(*PRTWRAP) LOG(4 0 *SECLVL);
stop;

cl: ADDJOBSCDE JOB(DAWNSPACE) CMD(RUNSQLSTM SRCSTMF('/home/SCOTTF/capture dawns object info.sql') COMMIT(*NONE) NAMING(*SQL)) FRQ(*WEEKLY) SCDDATE(*NONE) SCDDAY(*ALL) SCDTIME(23550)   ;
stop;
select *
  from qsys2.scheduled_job_info
  where scheduled_job_name = 'DAWNSPACE'
  order by scheduled_by;
                                      
select * from Nov10objs ;
stop;
select * from Nov10objs order by obj_size desc limit 10;

-- ======================================================================
-- ======================================================================
--
-- The following approach can be used for any multiple member file.
-- 
-- ======================================================================
-- ======================================================================


-- description: Find all members within the Collection Services JOB WAIT BUCKET DESCRIPTION file
--
select table_partition as membername, a.*
  from qsys2.syspartitionstat a
  where tabpart is not null and table_schema = 'QPFRDATA' and table_name = 'QAPMJOBWTD'
  order by create_timestamp desc;
stop;

--
-- description: Find the second most recently added member
--
WITH ct1(member_number, member_name) AS 
(
select ROW_NUMBER() OVER (ORDER BY CREATE_TIMESTAMP DESC) AS MBR_NUMBER, 
 TABLE_PARTITION AS MBRNAME
 from qsys2.syspartitionstat A
where tabpart is not null and table_schema = 'QPFRDATA' and TABLE_NAME = 'QAPMJOBWTD'
AND TABLE_PARTITION <> TABLE_NAME
)
SELECT member_name FROM CT1 WHERE member_number = 2;
stop;

--
-- description: Find the second most recently added member (improved)
--
select table_partition as membername, a.*
  from qsys2.syspartitionstat a
  where tabpart is not null and table_schema = 'QPFRDATA' and table_name = 'QAPMJOBWTD'
  order by create_timestamp desc
  limit 1 offset 1;
stop;

create or replace variable select1 clob(1M);

select MIN(count(*), 10)  from qsys2.syspartitionstat 
  where table_schema = 'QPFRDATA' and table_name = 'QAPMJOBWTD';
 

select table_partition from qsys2.syspartitionstat 
  where table_schema = 'QPFRDATA' and table_name = 'QAPMJOBWTD';
stop;  
select * from QPFRDATA.QAPMJOBWTD(Q301000002);
create or replace alias qtemp.tryit for QPFRDATA.QAPMJOBWTD(Q301000002);
select * from qtemp.tryit;

stop;
begin
declare query_sqlstmt clob(1G) default '';
declare sqlstmt clob(1G);
declare lc integer default 0;
declare alias_count integer;
declare v_partition_name varchar(10);
declare disk_members cursor for   
select table_partition from qsys2.syspartitionstat 
  where table_schema = 'QPFRDATA' and table_name = 'QAPMJOBWTD'
  order by create_timestamp desc
  limit 20;

select MIN(count(*), 10) into alias_count from qsys2.syspartitionstat 
  where table_schema = 'QPFRDATA' and table_name = 'QAPMJOBWTD';

open disk_members;

while (lc<alias_count) do
  set lc = lc + 1;
  fetch from disk_members into v_partition_name;
  set sqlstmt = 'create or replace alias DISK' concat lc concat 
                                   ' FOR QPFRDATA.QAPMJOBWTD(' concat v_partition_name concat ')';  
  execute immediate sqlstmt;
  set query_sqlstmt = query_sqlstmt concat ' SELECT * FROM DISK' concat lc;
  if (lc < alias_count) then 
    set query_sqlstmt = query_sqlstmt concat ' UNION ALL ';
  end if;
end while;

set select1 = query_sqlstmt;
close disk_members;

end;
stop;
--
-- Place the results of this query into the query below
--
values select1;

select *
  from disk1
union all
  select *
    from disk2
union all
  select *
    from disk3
union all
  select *
    from disk4
union all
  select *
    from disk5
union all
  select *
    from disk6
union all
  select *
    from disk7
union all
  select *
    from disk8
union all
  select *
    from disk9
union all
  select *
    from disk10;
    
    
stop;   


--
-- description: Storage breakdown for a specific user
--
cl: crtlib stgstudy;

create or replace variable stgstudy.sqltext varchar(100) default 
('SBMJOB CMD(RUNSQL SQL(''create table stgstudy.stg' concat lpad(month(current date), 2, 0)
                              concat lpad(day(current date), 2, 0)
                              concat lpad(right(year(current date), 2), 2, 0)
  concat ' as (select b.objlongschema, b.objname, b.objtype, 
       b.objattribute,  b.objcreated, b.objsize,
       b.objtext, b.days_used_count, b.last_used_timestamp 
  from table (
         qsys2.object_statistics(''''*ALLUSRAVL '''', ''''*LIB'''')
       ) as a, table (
         qsys2.object_statistics(a.objname, ''''ALL'''')
       ) as b
  where b.objowner = ''''TIMMR''''
  order by b.objsize desc
  fetch first 10 rows only) with data '') COMMIT(*NONE)) JOB(STGSTUDY)');

values;
call qsys2.qcmdexc(stgstudy.sqltext);

select * from STGSTUDY.STG051719;

stop;

-- insert from examples

--  category:  IBM i Services
--  description:  PTF - Group PTF Currency
--
-- Derive the IBM i operating system level and then
-- determine the level of currency of PTF Groups
--
with ilevel (iversion, irelease) as (
    select os_version,
           os_release
      from sysibmadm.env_sys_info
  )
  select p.*
    from ilevel,
         systools.group_ptf_currency p
    where ptf_group_release = 'R' concat iversion concat irelease concat '0'
    order by ptf_group_level_available - ptf_group_level_installed desc;

stop;

--  category:  IBM i Services
--  description:  Spool - Output queue basic detail
--
-- 10% better than perfect? Wow
--
select user_name, sum(size) as total_spool_space
  from qsys2.output_queue_entries_basic
  where user_name in (select user_name from qsys2.user_info where user_creator <> '*IBM')
  group by user_name
  order by total_spool_space desc
  limit 10;
  
stop;


-- Show highest levels for two system limits in the last week:
-- 1620:Maximum number of rows locked in a unit of work
-- 16201:Maximum number of row change operations in a unit of work
-- https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/rzajq/rzajqserviceshealth.htm
select user_name, job_name, last_change_timestamp as when, a.*
  from qsys2.syslimtbl a
  where limit_id in (1620, 16201) and last_change_timestamp > current timestamp - 1 year
  order by current_value desc;
stop;

-- What about IFS storage?
select a.*
  from qsys2.syslimits a
  where limit_id = 18409
  order by current_value desc;
stop;

-- Who did it? Hmmmmm
select text_description, u.* from qsys2.user_info u where authorization_name = 'LDB';


-- ======================================================================
-- ======================================================================

-- License management

-- ======================================================================
-- ======================================================================



--        
--  description:  License Management - Expiration processing
--         
CALL systools.license_expiration_check(60);         
--
-- Review licenses that will expire within 60 days
--
SELECT MESSAGE_TEXT, m.*
   FROM qsys2.message_queue_info m
   WHERE message_queue_name = 'QSYSOPR' AND
         MESSAGE_TEXT LIKE '%EXPIRE%';


--
-- Review license usage violations
--
SELECT a.*
   FROM qsys2.license_info a
   WHERE installed = 'YES' AND
   PEAK_USAGE > USAGE_COUNT   
   ORDER BY peak_usage DESC;
stop;


-- ======================================================================
-- ======================================================================

-- Anything related to "since the last IPL"

-- ======================================================================
-- ======================================================================



--  category:  DB2 for i Services
--  description:  Index Advice - Analyzing advice since last IPL 
--
-- Examine the index advice where MTIs have been used 
-- since the last IPL
--      
WITH last_ipl(ipl_time)
   AS (SELECT job_entered_system_time
          FROM TABLE(qsys2.job_info(job_status_filter => '*ACTIVE', 
                                    job_user_filter   => 'QSYS')) x
          WHERE job_name = '000/QSYS/SCPF')
   SELECT mti_used, mti_created, i.*
      FROM last_ipl, qsys2.sysixadv i
      WHERE last_mti_used > ipl_time OR
            last_mti_used_for_stats > ipl_time
      order by mti_used desc;
stop;



-- ======================================================================
-- ======================================================================

-- History Log

-- ======================================================================
-- ======================================================================


--
-- description: Study job longevity (in minutes)
--
   WITH JOB_START(start_time, from_user, sbs, from_job) AS (
     SELECT message_timestamp as time, 
          from_user, 
          substr(message_tokens, 59, 10) as subsystem,
          from_job
     FROM TABLE(qsys2.history_log_info(START_TIME => CURRENT DATE - 7 days,
                                       END_TIME   => CURRENT TIMESTAMP)) x           
     WHERE message_id = 'CPF1124' 
     ORDER BY ORDINAL_POSITION DESC
   ) SELECT TIMESTAMPDIFF(4, CAST(b.message_timestamp - a.start_time AS CHAR(22)))
              AS execution_minutes, DAYNAME(b.message_timestamp) AS JOB_END_DAY, 
            a.from_user, a.from_job, a.sbs
     FROM JOB_START A  INNER JOIN
          TABLE(qsys2.history_log_info(START_TIME => CURRENT DATE - 7 days,
                                       END_TIME   => CURRENT TIMESTAMP)) b
          ON b.from_job = a.from_job 
     WHERE b.message_id = 'CPF1164'
     ORDER BY execution_minutes desc limit 20;
 


-- ======================================================================
-- ======================================================================

-- Security

-- ======================================================================
-- ======================================================================

     

--
-- description: Wait, I thought nobody was using QSECOFR
--
-- All of this is enabled by:
-- CHGUSRAUD USRPRF(QSECOFR) AUDLVL(*JOBDTA) 
--
SELECT Entry_timestamp, job_number        concat '/' concat
                        rtrim(job_user)   concat '/' concat
                        rtrim(job_name) as job_name, 
                        REMOTE_PORT, REMOTE_ADDRESS,  
 case substr(cast(entry_data as varchar(1) for bit data), 1, 1) 
 when 'A' then 'ENDJOBABN command'
 when 'B' then 'Submit'
 when 'C' then 'Change'
 when 'E' then 'End'
 when 'H' then 'Hold'
 when 'I' then 'Disconnect'
 when 'J' then 'The current job is attempting to interrupt another job'
 when 'K' then 'The current job is about to be interrupted'
 when 'L' then 'The interruption of the current job has completed'
 when 'M' then 'Change profile or group profile'
 when 'N' then 'ENDJOB command'
 when 'P' then 'Attach prestart or batch immediate job'
 when 'Q' then 'Change query attributes'
 when 'R' then 'Release'
 when 'S' then 'Start'
 when 'T' then 'Change profile or group profile using a profile token'
 when 'U' then 'CHGUSRTRC'
 when 'V' then 'Virtual device changed by QWSACCDS API'
 else 'unknown'
 end as ENTRY_TYPE,
 case substr(cast(entry_data as varchar(2) for bit data), 2, 1) 
 when 'B' then 'Batch'
 when 'I' then 'Interactive'
 else 'other' 
 end as Job_Type, 
 case substr(cast(entry_data as varchar(3) for bit data), 3, 1) 
 when 'J' then 'Prestart'
 else 'other' 
 end as Job_SubType, 
 cast(entry_data as varchar(3112) for bit data) as ESD,
       x.*
   FROM TABLE(qsys2.display_journal(
               JOURNAL_LIBRARY        => 'QSYS', 
               JOURNAL_NAME           => 'QAUDJRN',
               STARTING_RECEIVER_NAME => '*CURAVLCHN', 
               JOURNAL_CODES          => 'T',  
               journal_entry_types    => 'JS'
)) AS x
where "CURRENT_USER" = 'QSECOFR' 
ORDER BY 1;

stop;


create table user_profile_audit  for system name upaudit as (
SELECT entry_timestamp, "CURRENT_USER" as who_made_the_change,
       job_number concat '/' concat rtrim(job_user) concat '/' concat rtrim(job_name) as job_name,
       rtrim(left(object, 10)) as target_profile, 
       cast(cast(substring(entry_data,639-610+1,3) as VARCHAR(3) for bit data) as CHAR(3)) AS OPERATION,
       cast(cast(substring(entry_data,645-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS ALLOBJ,
       cast(cast(substring(entry_data,653-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS PREV_ALLOBJ,
       cast(cast(substring(entry_data,646-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS JOBCTL,
       cast(cast(substring(entry_data,654-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS PREV_JOBCTL,
       cast(cast(substring(entry_data,647-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS SAVSYS,
       cast(cast(substring(entry_data,655-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS PREV_SAVSYS,
       cast(cast(substring(entry_data,648-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS SECADM,
       cast(cast(substring(entry_data,656-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS PREV_SECADM,
       cast(cast(substring(entry_data,649-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS SPLCTL,
       cast(cast(substring(entry_data,657-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS PREV_SPLCTL,
       cast(cast(substring(entry_data,650-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS SERVICE,
       cast(cast(substring(entry_data,658-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS PREV_SERVICE,
       cast(cast(substring(entry_data,651-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS AUDIT,
       cast(cast(substring(entry_data,659-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS PREV_AUDIT,
       cast(cast(substring(entry_data,652-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS IOSYSCFG,
       cast(cast(substring(entry_data,660-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS PREV_IOSYSCFG
   FROM TABLE(qsys2.display_journal(JOURNAL_LIBRARY => 'QSYS', 
                               JOURNAL_NAME    => 'QAUDJRN', 
                               STARTING_RECEIVER_NAME => '*CURAVLCHN',
                               JOURNAL_ENTRY_TYPES => 'CP',  
                               STARTING_TIMESTAMP => CURRENT TIMESTAMP - 1 days
)) AS x order by entry_timestamp desc
) with data;



select * from user_profile_audit;
stop;


--
-- schduled to run once per day
--
insert into secureit.user_profile_audit
  -- coding guide: https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/rzarl/rzarlf12.htm
  with CP_ACTIVITY (when, who_made_the_change, job_name, target_profile, operation,  
    allobj,   prev_allobj, jobctl,   prev_jobctl,  savsys,   prev_savsys, secadm,   prev_secadm,
    splctl,   prev_splctl, service,  prev_service, audit,    prev_audit,  iosyscfg, prev_iosyscfg) as (
  SELECT entry_timestamp, "CURRENT_USER" as who_made_the_change,
       job_number concat '/' concat rtrim(job_user) concat '/' concat rtrim(job_name) as job_name,
       rtrim(left(object, 10)) as target_profile, 
       cast(cast(substring(entry_data,639-610+1,3) as VARCHAR(3) for bit data) as CHAR(3)) AS OPERATION,
       cast(cast(substring(entry_data,645-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS ALLOBJ,
       cast(cast(substring(entry_data,653-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS PREV_ALLOBJ,
       cast(cast(substring(entry_data,646-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS JOBCTL,
       cast(cast(substring(entry_data,654-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS PREV_JOBCTL,
       cast(cast(substring(entry_data,647-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS SAVSYS,
       cast(cast(substring(entry_data,655-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS PREV_SAVSYS,
       cast(cast(substring(entry_data,648-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS SECADM,
       cast(cast(substring(entry_data,656-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS PREV_SECADM,
       cast(cast(substring(entry_data,649-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS SPLCTL,
       cast(cast(substring(entry_data,657-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS PREV_SPLCTL,
       cast(cast(substring(entry_data,650-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS SERVICE,
       cast(cast(substring(entry_data,658-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS PREV_SERVICE,
       cast(cast(substring(entry_data,651-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS AUDIT,
       cast(cast(substring(entry_data,659-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS PREV_AUDIT,
       cast(cast(substring(entry_data,652-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS IOSYSCFG,
       cast(cast(substring(entry_data,660-610+1,1) as VARCHAR(1) for bit data) as CHAR(1)) AS PREV_IOSYSCFG
   FROM TABLE(qsys2.display_journal('QSYS', 'QAUDJRN', journal_entry_types => 'CP',
                                    starting_timestamp  => CURRENT TIMESTAMP - 24 HOURS
)) AS x order by entry_timestamp desc )
select when, who_made_the_change, job_name, target_profile, operation,  
             secureit.check_special('*ALLOBJ',   prev_allobj,   allobj) AS ALLOBJ,
             secureit.check_special('*JOBCTL',   prev_jobctl,   jobctl) AS JOBCTL,
             secureit.check_special('*SAVSYS',   prev_savsys,   savsys) AS SAVSYS,
             secureit.check_special('*SECADM',   prev_secadm,   secadm) AS SECADM,
             secureit.check_special('*SPLCTL',   prev_splctl,   splctl) AS SPLCTL,
             secureit.check_special('*SERVICE',  prev_service,  service) AS SERVICE,
             secureit.check_special('*AUDIT',    prev_audit,    audit) AS AUDIT,
             secureit.check_special('*IOSYSCFG', prev_iosyscfg, iosyscfg) AS IOSYSCFG,
                  allobj,   prev_allobj, jobctl,   prev_jobctl,
                  savsys,   prev_savsys, secadm,   prev_secadm,
                  splctl,   prev_splctl, service,  prev_service,
                  audit,    prev_audit,  iosyscfg, prev_iosyscfg from CP_ACTIVITY order by when desc;
                  
stop;
                  
                  


--- =====================================================================================
--- =====================================================================================
---                                   Message Queues
--- =====================================================================================
--- =====================================================================================

--
-- Examine all system operator inquiry messages that have a reply
--
SELECT a.message_text AS "INQUIRY", b.message_text AS "REPLY", B.FROM_USER, B.*, A.*
 FROM qsys2.message_queue_info a INNER JOIN   
      qsys2.message_queue_info b
ON a.message_key = b.associated_message_key
WHERE A.MESSAGE_QUEUE_NAME = 'QSYSOPR' AND
      A.MESSAGE_QUEUE_LIBRARY = 'QSYS' AND
      B.MESSAGE_QUEUE_NAME = 'QSYSOPR' AND
      B.MESSAGE_QUEUE_LIBRARY = 'QSYS'
ORDER BY b.message_timestamp DESC; 
stop;

--
-- Examine all system operator inquiry messages that have no reply
--
WITH REPLIED_MSGS(KEY) AS (
SELECT a.message_key
 FROM qsys2.message_queue_info a INNER JOIN   
      qsys2.message_queue_info b
ON a.message_key = b.associated_message_key
WHERE A.MESSAGE_QUEUE_NAME = 'QSYSOPR' AND
      A.MESSAGE_QUEUE_LIBRARY = 'QSYS' AND
      B.MESSAGE_QUEUE_NAME = 'QSYSOPR' AND
      B.MESSAGE_QUEUE_LIBRARY = 'QSYS'
ORDER BY b.message_timestamp DESC
)
SELECT a.message_text AS "INQUIRY", A.*
 FROM qsys2.message_queue_info a 
      LEFT EXCEPTION JOIN REPLIED_MSGS b
ON a.message_key = b.key
WHERE MESSAGE_QUEUE_NAME = 'QSYSOPR' AND
      MESSAGE_QUEUE_LIBRARY = 'QSYS' AND
      message_type = 'INQUIRY'  
ORDER BY message_timestamp DESC; 
stop;


--- =====================================================================================
--- =====================================================================================
---                                   Work Management
--- =====================================================================================
--- =====================================================================================

--
-- Construct a subsystem that will constrain the amount of system resources
-- available to users who are known to execute ad hoc queries.
--
CL: CRTSBSD SBSD(QGPL/KIDDIESBS) POOLS((1 *BASE)) TEXT('Dangerous users SBS');
CL: CRTJOBQ QGPL/KIDDIEJOBQ TEXT('Dangerous users job queue');
CL: ADDJOBQE SBSD(QGPL/KIDDIESBS) JOBQ(QGPL/KIDDIEJOBQ) MAXACT(10) SEQNBR(40);
CL: CRTCLS CLS(QGPL/KIDDIECLS) RUNPTY(55) TIMESLICE(10) TEXT('Dangerous class');
CL: ADDPJE SBSD(QGPL/KIDDIESBS) PGM(QSYS/QRWTSRVR) JOBD(QGPL/QDFTSVR) 	CLS(QGPL/KIDDIECLS);
CL: ADDPJE SBSD(QGPL/KIDDIESBS) PGM(QSYS/QZDASOINIT) JOBD(QGPL/QDFTSVR) 	CLS(QGPL/KIDDIECLS);
CL: STRSBS SBSD(QGPL/KIDDIESBS);
--
-- Relocate TIM's server jobs to the KIDDIESBS
--
CALL QSYS2.SET_SERVER_SBS_ROUTING('TIMMR','*ALL','KIDDIESBS');

--
-- Review existing configurations for users and groups
--
SELECT * FROM QSYS2.SERVER_SBS_ROUTING;
stop;



--- =====================================================================================
--- =====================================================================================
---                                   Temporary Storage
--- =====================================================================================
--- =====================================================================================

--
-- description: Find active jobs using the most temporary storage. 
--
SELECT JOB_NAME, AUTHORIZATION_NAME, TEMPORARY_STORAGE, SQL_STATEMENT_TEXT, J.*
  FROM TABLE (QSYS2.ACTIVE_JOB_INFO(DETAILED_INFO => 'ALL')) J
   WHERE JOB_TYPE <> 'SYS' ORDER BY TEMPORARY_STORAGE DESC ;
stop;


--  category:  IBM i Services
--  description:  Work Management - Active Job info - Temp storage consumers, by memory pool
--
-- Find the top 4 consumers of temporary storage, by memory pool
--
with top_consumers (job_name, memory_pool, authorization_name, function_type, function,
      temporary_storage, rank) as (
    select job_name, memory_pool, authorization_name, function_type, function,
           temporary_storage, rank() over (
             partition by memory_pool
             order by temporary_storage desc
           )
      from table (
          qsys2.active_job_info()
        ) x
      where job_type <> 'SYS'
  )
  select job_name, memory_pool, authorization_name,
         function_type concat '-' concat function as function, temporary_storage
    from top_consumers
    where rank in (1, 2, 3, 4)
    order by memory_pool desc;
stop;

--
-- description: Which active jobs are the top consumers of temporary storage?
--
select bucket_current_size, bucket_peak_size, a.*
  from qsys2.systmpstg a where job_name is null
  order by bucket_current_size desc;
stop;

--
-- WRKSYSSTS for SQL
--
select * from qsys2.system_status_info;
stop;

--
-- Go beyond what the commands can do
-- 
-- Review the ASP consumption vs limit
--
with sysval(low_limit) as (
select current_numeric_value/100.0 as QSTGLOWLMT
  from qsys2.system_value_info
  where system_value_name = 'QSTGLOWLMT'
)
select SYSTEM_ASP_USED, 
       DEC((10.0 - low_limit),4,2) as SYSTEM_ASP_LIMIT 
from sysval, qsys2.SYSTEM_STATUS_INFO ;
stop;


--- =====================================================================================
--- =====================================================================================
---                                   Netstat
--- =====================================================================================
--- =====================================================================================

--
-- description: Find active 5250 sessions
--
select remote_address, remote_port, authorization_name as user_name, job_name
  from qsys2.netstat_job_info n
  where local_port = 23 and job_type = 'INTERACTIVE';
stop;
--
-- description: Return one row for all 5250 sessions
--
SELECT * FROM QSYS2.NETSTAT_INFO
  WHERE LOCAL_PORT = 23
  AND CONNECTION_TYPE = 'IPV4';
stop;




--- =====================================================================================
--- =====================================================================================
---                                  Disallowing Operations
--- =====================================================================================
--- =====================================================================================

--
--  Plug this logic into any exit point to recognize drops, deletes, updates, etc
--
create variable sql_statement clob(2m);
set sql_statement = 'DROP TABLE TOYSTORE.EMPLOYEE';

select 'Please don''t delete : ' concat schema concat '/' concat name
       as drop_table_detector
  from table (
      qsys2.parse_statement(sql_statement)
    ) where sql_statement_type = 'DROP TABLE';
    


--- =====================================================================================
--- =====================================================================================
---                                  Before you delete a library
--- =====================================================================================
--- =====================================================================================

-- Examine the library lists for every job description.
-- Since the library list column returns a character string containing a list of libraries, to see the individual library names it needs to be broken apart. To do this, you can create a table function that takes the library list string and returns a list of library names.
create or replace function systools.get_lib_names (
      jobd_libl varchar(2750),
      jobd_libl_cnt int
    )
  returns table (
    libl_position int, library_name varchar(10)
  )
  begin
    declare in_pos int;
    declare lib_cnt int;
    set in_pos = 1;
    set lib_cnt = 1;
    while lib_cnt <= jobd_libl_cnt do
      pipe (
        lib_cnt,
        rtrim((substr(
            jobd_libl,
            in_pos,
            10
          )))
      );
      set in_pos = in_pos + 11;
      set lib_cnt = lib_cnt + 1;
    end while;
    return;
  end;
stop;

-- Use the function to interrogate the use of libraries in jobd's libl
select job_description,
       job_description_library,
       libl_position,
       library_name
  from qsys2.job_description_info,
       table (
         systools.get_lib_names(
           library_list,
           library_list_count
         )
       ) x
where library_name = 'QGPL';
stop;

-- Or a simpler approach using LIKE
-- Note that this approach has a weakness, QGPL2 for example would be found as a match
select job_description_library,
       job_description,
       library_list
  from qsys2.job_description_info
  where library_list like '%QGPL%';

stop;
select job_name
      from qsys2.output_queue_entries_basic
      where output_queue_library_name = 'QUSRSYS' and output_queue_name =
            'PRT01' and user_name = session_user and spooled_file_name =
            'QPDSPCLS'
      order by create_timestamp desc
      limit 1;



--
-- UDTF... find class information
--
create or replace function systools.class_info( )
   returns table (lib varchar(10) ccsid 1208, cls varchar(10) ccsid 1208,
   clstext varchar(10) ccsid 1208, lastuse timestamp, usecount integer,
   rpriority integer)
   not deterministic
   external action
   modifies sql data
   not fenced
   set option commit = *none
begin
   declare v_print_line char(133);
   declare local_sqlcode integer;
   declare local_sqlstate char(5);
   declare v_message_text varchar(70);
   declare v_dspcls varchar(30);
   --
   -- DSPCLS detail
   --
   declare v_class char(10);
   declare v_class_library char(10);
   declare v_class_run_priority integer;
   declare v_library_name varchar(10);

   --
   -- OBJECT_STATISTICS detail
   --
   declare find_classes_query_text varchar(50);
   declare v_cls_text char(10);
   declare v_job_name varchar(28);
   declare v_cls_last_use timestamp;
   declare v_cls_use_count integer;
   declare c_find_classes cursor for find_classes_query;
   declare c_find_dspcls_output cursor for select job_name
      from qsys2.output_queue_entries_basic
      where user_name = session_user and 
            spooled_file_name = 'QPDSPCLS'
      order by create_timestamp desc
      limit 1;
   declare c_dspcls_output cursor for select c1
      from session.splf x
      where rrn(x) > 4
      order by rrn(x);
   declare continue handler for sqlexception
   begin
      get diagnostics condition 1
            local_sqlcode = db2_returned_sqlcode, local_sqlstate =
            returned_sqlstate;
      set v_message_text = 'systools.class_info() failed with: ' concat
               local_sqlcode concat '  AND ' concat local_sqlstate;
      signal sqlstate 'QPC01' set message_text = v_message_text;
   end;
   declare global temporary table splf (c1 char(133)) with replace;
   set find_classes_query_text =
                'select libs.objname, objs.OBJNAME  , rtrim(objs.OBJTEXT)  , objs.LAST_USED_TIMESTAMP  , objs.DAYS_USED_COUNT from table (
                 qsys2.object_statistics(''QSYS      '', ''*LIB'')
               ) libs, lateral ( select * FROM TABLE (qsys2.OBJECT_STATISTICS(libs.objname,''CLS    '')) AS a ) objs';
   prepare find_classes_query from find_classes_query_text;
   open c_find_classes;
   l1: loop fetch from c_find_classes into v_library_name,
                                           v_class,
                                           v_cls_text,
                                           v_cls_last_use,
                                           v_cls_use_count;
   get diagnostics condition 1
         local_sqlcode = db2_returned_sqlcode, local_sqlstate =
         returned_sqlstate;
   if (local_sqlstate = '0200') then
      close c_find_classes;
      return;
   end if;
   set v_dspcls = 'DSPCLS CLS(' concat rtrim(v_library_name) concat '/' concat
            rtrim(v_class) concat ') OUTPUT(*PRINT)';
   call qsys2.qcmdexc(v_dspcls);
   open c_find_dspcls_output;
   fetch from c_find_dspcls_output into v_job_name;
   close c_find_dspcls_output;
   call qsys2.qcmdexc(
      'CPYSPLF FILE(QPDSPCLS) TOFILE(QTEMP/SPLF) SPLNBR(*LAST) JOB(' concat
         v_job_name concat ') '
   );
   open c_dspcls_output;
   fetch from c_dspcls_output into v_print_line;
   set v_class_run_priority = int(substr(v_print_line, 56, 10));
   close c_dspcls_output;
   call qsys2.qcmdexc(
      'DLTSPLF FILE(QPDSPCLS)  SPLNBR(*LAST) JOB(' concat v_job_name concat
         ') '
   );
   pipe (v_library_name, v_class, v_cls_text, v_cls_last_use, v_cls_use_count,
   v_class_run_priority);end loop; /* L1 */
   close c_find_classes;
end;
stop;

select *
   from table (
         systools.class_info()
      );