forstie

--
--  description:  Objects owned by QTMHHTP1
--  minvrm: V7R3M0
--
select 'QTMHHTP1' as user, 'IFS' as object_type, count(*) as objects_owned
  from qsys2.object_ownership
  where authorization_name = 'QTMHHTP1' and path_name is not null
union all
select 'QTMHHTP1' as user, 'QSYS' as object_type, count(*) as objects_owned
  from qsys2.object_ownership

forstie

-- One time setup
cl:ADDDIRE USRID(<user-profile> RST) USRD('Your name') USER(<user-profile>);

--
-- description: Count the stream file objects, by subdirectory
--              Include the total size of all stream files in each subdirectory
--              Order the result by largest object count, descending
--
-- Note: Please note that this isn't a fast query and is meant to be run once in a while
--       and not During a performance critical period.

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

--
-- description: Count the number of active user jobs in the system
-- minvrm: v7r2m0
--
select authorization_name as user_name, count(*) as active_job_count
  from table (
      qsys2.active_job_info()) j
  where job_type <> 'SYS' and job_status not in ('PSRW') and
        j.authorization_name not in (select user_name
            from qsys2.user_info

forstie

begin 
execute immediate  'create or replace variable qpfrdata.current_CS_library varchar(10) for sbcs data';
execute immediate  'create or replace variable qpfrdata.current_CS_member varchar(10) for sbcs data';

call qsys2.qcmdexc('QSYS/CHKPFRCOL');
set (qpfrdata.current_CS_library,qpfrdata.current_CS_member) =
    (select rtrim(substr(message_tokens,1,10)) as cs_lib, rtrim(substr(message_tokens,11,10)) as cs_mbr
 from table(qsys2.joblog_info('*')) where message_id = 'CPI0A16' order by ordinal_position desc limit 1);

forstie

--
-- Note: 
-- Documentation for ACS's cldownload and other fantastic hidden gems can be found here:
-- ftp://ftp.software.ibm.com/as400/products/clientaccess/solutions/GettingStarted_en.html
--

--
-- Do I have the ACS jar on this IBM i?
--
select create_timestamp, acs.*

forstie

--
-- Author: Scott Forstie
-- Date  : July 14, 2020
-- 
-- Question: How can I use SQL to validate that a string only contains an approved list of characters?
-- Answer  : The TRANSLATE() built-in function.
--
-- TRANSLATE replaces the allowed characters with blanks.
-- Then, RTRIM throws all the blanks away.
-- Then, if the resulting LENGTH is zero, you can conclude that the string only contained allowed characters.

forstie

--
-- Author: Scott Forstie
-- Date  : July 14, 2020
--
--
-- Question: How do I extract the Job User Name from a qualfied job name?
-- Answer  : Use Locate_In_String with Substring
-- Bonus   : Did you know that the QSYS2.JOB_NAME built-in global variable gives you the qualfied job-name of the current connection?

values qsys2.job_name,

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

--
-- The fastest way to find every user profile
--
select objname as user_profile
  from table (
      qsys2.object_statistics('QSYS', '*USRPRF', '*ALLSIMPLE')
    );