forstie

-- Resources:
-- https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/rzajq/rzajqudfactivejobinfo.htm
-- https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/rzajq/rzajqviewjobqueueinfo.htm

-- Review job queues, from a percent of capacity consumed perspective
select maximum_active_jobs,
       active_jobs,
       dec(dec(active_jobs, 19, 2) / dec(maximum_active_jobs, 19, 2), 19, 2) as jobq_percent_consumed, 
       job_queue_name, job_queue_library, job_queue_status, number_of_jobs,
       subsystem_name, subsystem_library_name, sequence_number,  held_jobs, released_jobs, scheduled_jobs, text_description,

forstie

--
-- Reference: https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/rzajr/rzajrservicesandports.htm
-- 
-- description: Which users with *ALLOBJ (either directly or indirectly via group profile membership) are accessing the IBM i via a non-SSL interface right now?
--
select authorization_name as user_name, j.*
  from qsys2.netstat_job_info j
  where local_port in (23, 446, 449, 2001, 4402, 5544, 5555, 8470, 8471, 8472, 8473, 8474, 8475, 8476)
        and j.authorization_name in (select authorization_name text_description

forstie

--
-- Am I current on the IBM i Group Hiper PTF GROUP level?
--   
With iLevel(iVersion, iRelease) AS
(
select OS_VERSION, OS_RELEASE from sysibmadm.env_sys_info
)
  SELECT P.*
     FROM iLevel, systools.group_ptf_currency P

forstie

with qsysopr_inquiries_today (msg, asker, text, key) as (
       select message_id, from_user, message_text, message_key
         from qsys2.message_queue_info
         where message_queue_library = 'QSYS'
               and message_queue_name = 'QSYSOPR'
               and message_type = 'INQUIRY'
               and date(message_timestamp) = current_date  
     ),
     qsysopr_answers_today (assoc_key) as (
       select associated_message_key

forstie

--
-- Check the configuration of: 
-- Maximum system disk pool usage (QSTGLOWACN and QSTGLOWLMT) system values
-- https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/rzakz/rzakzqstglowacn.htm
-- https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/rzakz/rzakzqstglowlmt.htm
--
select current_character_value from qsys2.system_value_info
  where system_value_name = 'QSTGLOWACN';
stop;

forstie

--
-- description: Since the last IPL, which jobs have used the most Activation Groups to execute SQL?
-- resource: https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_74/rzajq/rzajqhealthenvlimits.htm
-- minvrm: V6R1M0
--
select b.sizing_name,
       a.highwater_mark,
       a.when_recorded,
       a.job_name,
       a.current_user,

forstie

--
-- Reference material: https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/rzajq/rzajqudfifsobjstat.htm
--

--
-- How much space is used by stream files from /tmp (and subdirs) that haven't been used in 6 months
-- 
select varchar_format(sum(data_size),'999G999G999G999G999G999G999') tmp_size
  from table (
    qsys2.ifs_object_statistics(start_path_name => '/tmp', 

forstie

--
-- Show me the 4GB maximum size database radix indexes that have eclipsed 60% of the maximum allowed size
-- SQL indexes are *MAX1TB (where the maximum size is really closer to 2TB)
-- Non-SQL indexes created with CRTLF can be either *MAX1TB or *MAX4GB
--
-- https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/rzajq/rzajqserviceshealth.htm
--
WITH OVER_60_PERCENT(Object_Name, limit_id, Sizing_name,     
       Current_value, Maximum_value, Row_Number) AS                 
 (                                                                  

forstie

--
-- 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;

forstie

--
-- Complete review of special authorities granted to user profiles
--
select user_name, ordinal_position, ltrim(element) as special_authority
  from qsys2.user_info, table (
         systools.split(rtrim(special_authorities), '   ')
       ) b
  where user_name not in (select authorization_name
             from qsys2.authids
             where authorization_attr = 'GROUP');