Skip to content

Instantly share code, notes, and snippets.

Avatar

Scott Forstie forstie

View GitHub Profile
@forstie
forstie / Who owns the most objects.sql
Created Sep 24, 2020
Someone asked me how to use SQL to understand who owns the most objects. The following is a progression of queries to shine a light on this topic.
View Who owns the most objects.sql
--
-- 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
forstie / IFS breakdown objects by subdir.sql
Created Sep 23, 2020
Someone asked if you could count the stream file objects, by directory. This solution gets the job done and includes the total size count as well.
View IFS breakdown objects by subdir.sql
-- 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
forstie / Activation Groups.sql
Created Sep 22, 2020
In this Gist, we see how we can query the Db2 for i Health Center and inquire what Environmental Limits shows as the jobs that consumed the most activation groups within a single job, where the activation group executed SQL.
View Activation Groups.sql
--
-- 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
forstie / Count active user jobs.sql
Created Sep 18, 2020
We were asked... how do you count the number of active user jobs "right now" and see the answer ordered by highest to lowest count. The answer is found below and I've included a version of the SQL that will work for anyone still using IBM i 7.1.
View Count active user jobs.sql
--
-- 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
forstie / How to discern where to find todays Collection Services info.sql
Created Aug 5, 2020
If you want to automate analysis of Collection Services, you can use this approach to use SQL to discern the library in use by Collection Services (CS) and the member name in the CS files that corresponds to the current day.
View How to discern where to find todays Collection Services info.sql
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
forstie / Spreadsheets and Emails with SQL & ACS.sql
Last active Sep 24, 2020
In this Gist, I show how you can leverage ACS's jar that is shipped on your IBM i to do your bidding. In this case, we can automate the creation and downloading of a spreadsheet to an IFS file where the contents of the spreadsheet is controlled with an SQL query. After the spreadsheet is created in the IFS, we use SQL once more to email the spre…
View Spreadsheets and Emails with SQL & ACS.sql
--
-- 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
forstie / Data validation of a string.sql
Created Jul 14, 2020
In a classic two-for-Tuesday move, another string based and built-in function solved question has come in.
View Data validation of a string.sql
--
-- 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
forstie / Extracting data from a string.sql
Created Jul 14, 2020
The SQL language is so robust, there's often more than one way to accomplish a task. In this Gist, I demonstrate how to extract the job user name from a qualified job name.
View Extracting data from a string.sql
--
-- 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
forstie / Out of storage.sql
Created Jul 13, 2020
The IBM i has some built-in facilities to allow you to understand that an 'out of storage' condition exists. Here are some example SQL techniques for managing this alert mechanism.
View Out of storage.sql
--
-- 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
forstie / Finding user profile names.sql
Created Jun 11, 2020
Finding user profile names... the fastest way possible....
View Finding user profile names.sql
--
-- The fastest way to find every user profile
--
select objname as user_profile
from table (
qsys2.object_statistics('QSYS', '*USRPRF', '*ALLSIMPLE')
);
You can’t perform that action at this time.